Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Do You Enable “Optimize for Ad Hoc Workloads”?

image

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.

Comments

Posted by Glenn Berry on 26 April 2011

I always enable this setting, and I have been advising people to do the same for quite a while. I am always surprised that so many DBAs seem to have not heard of this setting. Thanks for helping to spread the word!

Posted by Steve Jones on 26 April 2011

I'm not surprised many people don't know about it. It's not talked about very much and it wasn't until late last year that I remembered that it was in there when someone mentioned it.

I wouldn't think this should always be set. It's workload dependent, but there could be better guidelines, and this is an easy one to set if you fit inside some wide parameters.

Posted by Glenn Berry on 26 April 2011

I actually talked to Sangeetha Shekar (who implemented the feature in SQL Server 2008) about this a couple of years ago. The only case she could think of where this setting might hurt you a small amount is if you had lots of ad-hoc plans that were going to be executed only two or three times.  That is definitely an extreme edge case scenario.  Here is some of her writing (from SQL Server 2005 days)

blogs.msdn.com/.../plan-cache-concepts-explained.aspx

Posted by ALZDBA on 27 April 2011

In our instance install sequence this setting is being enabled.

Reason: most of our devs still like assembling their query strings themselves and just don't want to write a sproc because of the implementation overhead they see in it.

(they might forget it with their rollouts or may even need a #&$@ dba to implement it in QA or Production -- which only reveals how many times stuff needs to be fixed anyway)

All BP and good advise is being neglected and a performance price is to be payed.

At least, this setting helps a bit.

Posted by george sibbald on 27 April 2011

This might be a silly question but how does this fit in with the database level option 'alter database dbname set parameterization forced' which I beleieve has an effect on ad-hoc queries?

Posted by Cliff Jones on 27 April 2011

I was one of the No respondents.  We tested it last September on a representative work load and determined that it did not help, in fact some of our performance counters were worse.  I was quite surprised by that finding.  So I would suggest that people test first.

Posted by Brad M. McGehee on 27 April 2011

George, “optimize for ad hoc workloads" and "forced parameterization" are different solutions to different problems, although they are both related plan caching. Check out Books Online for each option, as it does a good job of explaining each one.

Cliff, it is good to hear feedback about this option not working for you. Before running the test, did you check to see if you had any plan cache bloat in the first place? If you did not have any plan cache bloat, then this option won't provide much, if any benefit.

Posted by george sibbald on 27 April 2011

thanks brad

Posted by Caruncles on 27 April 2011

Brad, I don't call myself a DBA, but I took this job last August and I'm THE whole department, so I've been learning as fast as I can.  I know you didn't ask for numbers, but I saw that Kimberly's posts were closed.  Can you interpret these?  I don't know if they are bad, good or otherwise.

We run SQL 08 standard, 8gb total memory- as a virutal machine on a Hyper-V box.

CacheType TotPlans TotalMBs AvgUC   TotMBUC1       TPUC1

Prepared310 55.125000 49 45.023437 215

Proc 396 110.968750 336 36.585937 142

Adhoc 2010 17.921173 38 9.569610 1841

Check 36 1.304687 31 0.554687 17

Trigger 7 1.156250 518 0.000000 0

View 1022 96.648437 16 0.000000 0

UsrTab 1 0.218750 5 0.000000 0

Posted by Brad M. McGehee on 27 April 2011

Caruncles, it would appear that there are 2,010 ad hoc plans in memory, taking up about 17.9MB (which is relatively small as compared to the amount of available RAM), with an average use count of 38. What you are looking for are ad hoc queries that run only once. Try running this query: SELECT * FROM sys.dm_exec_cached_plans WHERE objtype = 'adhoc'. This will display all of the ad hoc plans. If the majority of them have a use count of 1, then turning on “optimize for ad hoc workloads" may be beneficial, although how beneficial is difficult to estimate, unless you do some formal before and after trend analysis. If you decide to experiment with this option, try this in a controlled test environment before trying it in production. If you are unable to do any testing, I would recommend you leave the setting as is, until you have enough time to properly test before and after performance to verify if changing the setting is helpful or not. 17 MB is not really a large amount of RAM, unless you are already under memory pressure and you need to eek out every bit of RAM you can for the data cache.

Leave a Comment

Please register or log in to leave a comment.