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.
