Configuring "Optimize for Ad hoc Workloads"
Execution of any query or stored procedure for the first time creates an execution plan, which is stored in SQL Server 's procedure cache memory. It happens many times that we execute a simple query once, which is not even going to be used again anytime soon and it may never run again in future too even execution plan generated for that query will consume space in procedure cache. You may run out of cache sometimes, due to lack of memory, which affects performance. This was really a big issue till SQL Server 2005. In order to remedy this, Microsoft introduced "Optimize for Ad hoc Workloads" in SQL Server 2008, and it is still available in SQL Server 2012. This setting is instance-wide in SQL Server. ...
Get Microsoft SQL Server 2012 Performance Tuning Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
