I recently was involved in a discussion about whether or not to enable the ‘Optimize for Ad Hoc Workloads’ server configuration setting.  My initial thought was to leave it disabled, since this is the default after all, and we didn’t have a specific situation where it was believed enabling this would have helped.  I admittedly did not know a lot about this setting, so I decided to do some research.  After researching, I’m changing my tune.

What’s the Benefit?

Enabling this setting tells SQL Server that when a query is ran for the first time, only cache a stub of the execution plan.  This stub is essentially a 300 byte flag that lets SQL Server know this query has been ran once.  When the query is ran a second time, the entire plan is cached as it would be if this setting was left at the default.  The minimum size for a full cached execution plan is 16k, so this is quite a savings on single use plans.

Any Drawbacks?

In this post by Kalen Delaney, she detailed a few settings that you should always change.  ‘Optimize for ad hoc workloads’ was one of these settings, with the “exception of a few edge case scenarios.”  Since “always” isn’t a word that is thrown around lightly in the SQL Server world, I was curious as to what these edge case scenarios were.  I asked Kalen, and she responded with this:

“The one case I can think of, which was actually mentioned to me by a Microsoft engineer when I asked why this isn’t ON by default is this:

Suppose you have an app that runs lots and lots of very complex queries, needing lots of compile time, and each one is run exactly TWICE. So you need to spend the time to compile each time you run, because the plan won’t be saved the first time, but then you never run again to take advantage of the saved plan.

Yeah, I said it was a real edge case!”

Uh yeah, that’s an edge case.

Take a Look for Yourself

Want to know how much of your cache is bloated by single use plans?  The below query will give you the total number of single use plans, along with their total size in MB.

SELECT
count(1) as PlanCount, 
((SUM(CONVERT(BIGINT, size_in_bytes)))/1024)/1024 as size_in_MB
FROM sys.dm_exec_cached_plans p
OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
WHERE usecounts = 1
GO

Follow @AdamKreul