September 17, 2012 at 3:29 pm
I recently turned on OPTIMIZE FOR ADHOC WORKLOADS on one of my servers. My understanding is that after this flag is turned on any time code gets compiled for the first time a stub will be stored instead of a full plan. This stub will the be replaced with the full plan the next time the code is compiled.
Thus far in sys.dm_exec_cached_plans I'm seeing a little over 16k "Stubs", 1500 "Compiled Plan"s with more than 1 use and 900 "Compiled Plan"s with only one use.
My confusion comes in on the "Compiled Plan"s with only one use. I would have expected all of them to be stubs. The only possible reason I can think of is that when code is recompiled and a stub is found a new entry is added under the compiled plans, however the statistics from the first run are not included. Does anyone know if this is the case? Also if this is the case does the original stub get deleted?
My expectation was that a plan would be compiled the first time and a "Stub" entry would be added. Then the second time it was compiled the entry would be modified to be a "Compiled Plan" with the full plan included and the uses updated to 2 and of course all of the other statistics updated as well. Now I'm not so sure.
Thanks for any help anyone can give me.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
September 17, 2012 at 4:06 pm
Are they object plans (procedure, trigger, function), parametrised or ad-hoc queries?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 18, 2012 at 1:36 pm
GilaMonster (9/17/2012)
Are they object plans (procedure, trigger, function), parametrised or ad-hoc queries?
It appears to be a fairly even mix of all of the above.
I did do some playing around and think I answered my own question though. I documented what I came up with here: SqlStudies.com[/url]
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
September 18, 2012 at 1:46 pm
Worth noting that optimise for ad-hoc only affects ad-hoc batches, not procedures.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 26, 2012 at 10:22 am
Hi I'm interested to know more about how optimize for ad-hoc workloads only affects ad-hoc batches, not procedures. I recently noticed that a lot of cache was being used by single use ad hoc queries. I enabled optimize for ad hoc workloads, but it didn't help.
I'm I correct in reasoning that this means the vendors stored procedures are written without parameters and my only recourse is to test out forced parameterization or tell them to re-write their sp's using parameters and/or batches within the SP?
October 26, 2012 at 10:51 am
Looks like the compatibility level is 90, so that's my problem...
October 31, 2012 at 10:14 am
Sorry for spamming your post. I've upgraded all db's to compatibility level 100, enabled optimize for ad hoc workloads, restarted SQL Services and I still have a ton of single use ad hoc queries.
What's going on?
CacheTypeTotal PlansTotal MBsAvg Use CountTotal MBs - USE Count 1Total Plans - USE Count 1
Adhoc496294644.64780414636.56164549559
Proc184152.92187528847936.72656262
Prepared385.49218715642.2812518
Trigger3314.68757320.1484371
UsrTab100.3046872039800
View10614.87520600
Check683.04687580500
October 31, 2012 at 10:32 am
Actually I found that if you have OPTIMIZE FOR ADHOC on and see a full adhoc plan rather than a stub then it is actually the second time the query was used. The first time it creates the stub, the second time it deletes the first entry, then creates a new entry but with only one use count. The number of single use adhoc plans should go down over time.
I ran a few examples in my blog here: http://sqlstudies.com/2012/09/18/funny-results-from-optimize-for-ad-hoc-workloads/[/url]
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply