Should I Optimize My SQL Server Instances for Ad Hoc Workloads?

Q: I heard you recommend the new SQL Server 2008 configuration optimize for ad hoc workloads. Should I turn this option on, and why?

A: Yes! In general, I recommend that this option be enabled on all my customers’ servers. The original idea behind the option was to reduce plan cache bloat caused by ad hoc workloads, but even if you don’t have a lot of intentionally ad hoc workloads, you might still save cache and reduce bloat of dynamically executed strings and other internal code.

What this option does is prevent SQL Server from placing a plan in cache on the first execution of a statement. Instead, SQL Server will create only a query hash (and place it in cache). Once a statement executes a second time, then SQL Server will place the plan in cache. The reason this is a problem is that query plans can be quite large, especially when compared to the query hash. A simple statement executed against a table with only a few indexes might still be 24KB in size, whereas more complicated statements against more complicated schemas can actually be megabytes in size. A query hash is usually measured in bytes or maybe 1KB if it’s really complicated. If a statement ends up executing only once, then you save space by not placing a large plan in cache that won’t ever be reused. The downside is that the second execution also has to generate the plan to place it in cache, but if the statement is executed often, then there’s only one execution that suffers. If the statements aren’t executed often, then your plan cache won’t be wasted and can instead be used by procedures and plans that do get reused.

I’ve written a few detailed blog posts on this subject, and it’s something that you might want to investigate further. However, I haven’t yet found an environment that didn’t benefit from having this option turned on. The bad news is that it was a new option in SQL Server 2008 and therefore exists only in SQL Server 2008 and later.
Note that this is an advanced configuration option, so you need to configure your server to show advanced options first. The following are the commands necessary to turn this configuration on at the server level:

 

 sp_configure 'show advanced options', 1   go   reconfigure   go   sp_configure 'optimize for ad hoc    workloads', 1   go   reconfigure   go

If you want to learn more about how much of your cache is being used by single-use plans, then check out my blog posts “ Plan cache and optimizing for adhoc workloads” and “ Plan cache, adhoc workloads and clearing the single-use plan cache bloat."

If you want to see what’s in cache and get more insight into plan size and query hash, then check out my blog post “ Clearing the cache - are there other options?”