Do You Enable “Optimize for Ad Hoc Workloads”?


Most of the time when I run a poll on my blog, I run it for at least a month so that I can get enough results in order for the data to be more or less representative of the SQL Server community. What has been odd about this poll, is that even though I have run it longer than any other poll on the website, it has had one of the fewest response rates.

When I look at the results, my best guess is that people who have looked at the poll weren’t familiar with the question, and because of that, ignored responding to it, instead of admitting that they didn’t know what it was. Or, perhaps it is because this feature is only available in SQL Server 2008 and higher, and not everyone is running this version yet.

When you look at the results, just over 36% of the respondents said that they use the “optimize for ad hoc workloads” SQL Server configuration option. Of the 15 or percent that said no, it is hard to know if they are not using it because they don’t know what it is, because they have made a deliberate choice not to use it, or because they don’t have any SQL Server 2008 instances. (That’s one of the limitations of such a simple poll.) About another 10% said “it depends” if they use it or not, and about 38% of the responders admitted not knowing what the feature does. I am guessing that the 38% is understated, assuming my belief that people didn’t respond to this question because they didn’t understand the question. It’s too bad that so many DBAs aren’t familiar with this option, as it is a very simple option that can return big benefits for many SQL Server instances.

For those of you who don’t know, when “optimize for ad hoc workloads” is turned on (it is off by default), SQL Server will only store a small compiled plan stub, not the entire execution plan, the very first time an ad hoc query is compiled for the first time. Should the same batch be executed again, then this time around, the full execution plan will be cached in the plan cache, which is what happens to ad hoc queries when this option is not turned on.

So how is this option useful? If your SQL Server instance executes many ad hoc queries, it is very possible that once the query is executed, it may never be executed again. This can produce what is sometimes called “plan cache bloat”, which means that much of your plan cache is wasted by execution plans that will never be used again, reducing the amount of buffer pool memory available to SQL Server. The “optimize for ad hoc workloads” option helps to prevent “plan cache bloat” by only caching the full execution plan of queries that are known to be used more than once. If “plan cache bloat” is a problem that you have, then turning on this feature can boost the performance of your SQL Server instance.

How do you know if you have the “plan cache bloat” and should turn on “optimize for ad hoc workloads”? The best way is to query sys.dm_exec_cached_plans, using one of the many queries available on the web, such as the one written by Kimberly Tripp. By using the querying this DMV, you will be able to quickly tell if your instance has a “plan cache bloat” problem. If it does, then turn this option on, as there is virtually no downside to doing to, and a great potential upside.

If you have any experience using this option, and would like to share your experiences, please do so below.

6 thoughts on “Do You Enable “Optimize for Ad Hoc Workloads”?

  1. Interesting–and your polls are usually this technical, as I recall. If you stack up your poll questions side by side, is there a pattern in those that get more responses and those that do not?

    Also, yes, we do 🙂

  2. There does seem to be a general trend of the more technical the poll question, the fewer the responses.

  3. I’m not clear about something: are you recommending we clear the plan cache periodically to reduce this bloat? Wln’r that purge oft-used plans as well? I see no way to purge only single-use plans.

    • No, I am not suggestion you clear the plan cache to reduce bloat, although I have heard of people doing this on a periodic basis. If you turn on this option, then the plan bloat problem, assuming you have one, should go away.

  4. My question was not clear: for those of us on SQL 2005, is there a way to clear the bloat, given that we have to live with ad hoc queries due to reporting tools being used. Writing stored procedures for all reports is not an option we have here.

Comments are closed.