OPTIMIZE FOR ADHOC WORKLOADS question

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » OPTIMIZE FOR ADHOC WORKLOADS question

OPTIMIZE FOR ADHOC WORKLOADS question

Rate Topic

Display Mode

Topic Options

Author

Message

Kenneth.Fisher

Posted Monday, September 17, 2012 3:29 PM

Hall of Fame

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:31 AM
Points: 3,367, Visits: 1,563

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 Fisher
I strive to live in a world where a chicken can cross the road without being questioned about its motives.
--------------------------------------------------------------------------------
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Link to my Blog Post --> www.SQLStudies.com

Post #1360476

GilaMonster

Posted Monday, September 17, 2012 4:06 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 37,687, Visits: 29,946

Are they object plans (procedure, trigger, function), parametrised or ad-hoc queries?

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1360488

Kenneth.Fisher

Posted Tuesday, September 18, 2012 1:36 PM

Hall of Fame

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:31 AM
Points: 3,367, Visits: 1,563

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

Kenneth Fisher
I strive to live in a world where a chicken can cross the road without being questioned about its motives.
--------------------------------------------------------------------------------
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Link to my Blog Post --> www.SQLStudies.com

Post #1361000

GilaMonster

Posted Tuesday, September 18, 2012 1:46 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 37,687, Visits: 29,946

Worth noting that optimise for ad-hoc only affects ad-hoc batches, not procedures.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1361005

Jon.Morisi

Posted Friday, October 26, 2012 10:22 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 8:12 AM
Points: 275, Visits: 679

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?

Post #1377718

Jon.Morisi

Posted Friday, October 26, 2012 10:51 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 8:12 AM
Points: 275, Visits: 679

Looks like the compatibility level is 90, so that's my problem...

Post #1377743

Jon.Morisi

Posted Wednesday, October 31, 2012 10:14 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 8:12 AM
Points: 275, Visits: 679

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?

CacheType Total Plans Total MBs Avg Use Count Total MBs - USE Count 1 Total Plans - USE Count 1
Adhoc 49629 4644.647804 1 4636.561645 49559
Proc 184 152.921875 288479 36.726562 62
Prepared 38 5.492187 1564 2.28125 18
Trigger 33 14.6875 732 0.148437 1
UsrTab 10 0.304687 20398 0 0
View 106 14.875 206 0 0
Check 68 3.046875 805 0 0

Post #1379417

Kenneth.Fisher

Posted Wednesday, October 31, 2012 10:32 AM

Hall of Fame

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:31 AM
Points: 3,367, Visits: 1,563

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/

Kenneth Fisher
I strive to live in a world where a chicken can cross the road without being questioned about its motives.
--------------------------------------------------------------------------------
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Link to my Blog Post --> www.SQLStudies.com

Post #1379427

« Prev Topic | Next Topic »

Permissions