The Case for Optimize for Ad Hoc Workloads

, 2015-05-19

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.

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






Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.


1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...


1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.


360 reads