Optimize for Ad Hoc Workloads is a configuration option that can free significant amounts of memory for some SQL Servers.
In this post I briefly describe the process used by SQL Server when dealing with execution plans for ad hoc queries, the impact this can have on the plan cache for single use plans, and how the "optimize for ad hoc workloads" configuration option frees plan cache memory for more deserving causes, such as database pages.
How are ad hoc queries handled by the engine?
Ad hoc queries are very common amongst vendor-supplied applications, especially those designed to work with multiple database engines. Essentially, instead of encapsulating the code in stored procedures, the full TSQL query is submitted every time.
SQL Server looks at the query and checks the plan cache to see if a plan has already been compiled for it. If so, the plan is reused. If not, an execution plan is compiled and added to the plan cache.
What does this mean for single-use plans?
So when an identical query is run many times over, its plan is reused, saving CPU resources from having to compile it repeatedly, and very little of the plan cache is used - just one copy of the plan is stored.
But consider an application that generates hundreds or thousands of different queries that are only called once. In this case there is no execution plan to reuse, so the plan cache bloats in size to stored thousands of execution plans that will never be reused. This steals memory from the buffer pool that would be better used by database pages.
I've found a particularly extreme example of this - here is the output from part of the Memory -> Plan Cache page in SQL CoPilot.
As you can see, there are over 9,000 ad hoc plans that have only been used once, using over 20GB of plan cache!
How does "optimize for ad hoc workloads" solve the problem?
"optimize for ad hoc workloads" is a configuration option introduced in SQL Server 2008, and it solves the problem very neatly. Whenever a new ad hoc query is run, the execution plan is compiled and executed, but only a stub is stored in the plan cache. This is a significant memory saving, as the plans for some complex queries can be very large indeed. When a query is called a second time, SQL Server spots the stub from the last execution, compiles and executions the plan again, but this time it stores the whole plan in the cache. Future executions of the same query will now reuse the plan.
For applications generating thousands of single-use ad hoc plans, this is a significant saving on memory.
To enable it, run the following:
EXEC sp_configure 'optimize for ad hoc workloads', 1
For 99.99% (I made that number up, but it's effectively 100%) of systems you can turn on this configuration option without worry. If there are no single-use plans there will be no benefit, but no harm is done either. If there are single-use plans, it will free memory in the buffer pool.
In theory, if you have an application that uses ad-hoc SQL and calls everything just 2 or 3 times, you may see a penalty in terms of CPU usage. But I've never seen this happen.
"optimize for ad hoc workloads" is a useful tool in your fight for performance, and
I'd like to see it enabled by default in future versions of SQL Server.
OK - I've put a line through that last sentence now. There is a good reason why this option is not enabled by default. With "optimize for ad hoc workloads" enabled, single-use ad hoc SQL is not recorded in sys.dm_exec_query_stats. This could affect your ability as a DBA to perform performance tuning. If you are in the habit of querying sys.dm_exec_query_stats (and you're missing out on a great source of information if you're not) there will be no execution statistics for any single-use ad hoc SQL.
My revised conclusion is that this configuration option is still very useful, but you need to make the decision on a server by server basis whether to free up some memory or to retain the ability to see cached single-use ad hoc plans and their query statistics.