January 31, 2012 at 6:23 am
Hi All,
On our OLTP server I set the value for "optimize for ad hoc workloads" as true. If I query sys.dm_exec_cached_plans I see that the single use plan cache is 433.15 MB. If this value is set, why would the plan cache be bloated with single user plans?
Our SQL Server version is SQL 2008 SP1 64 bit.
Any advice is appreciated.
-Roy
January 31, 2012 at 6:28 am
What is the value for Compiled Plan Stub?
You can still get Cache bloat from ad-hoc plans. It should be less than before. This setting is mainly to help offset ad-hoc plans that are only run once. If the ad-hoc query is run again, then a full plan is created instead of just the stub. If you have a lot of single use ad-hoc queries you will reduce your cache bloat. If they are multi-use ad-hoc queries, your bloat reduction will be less significant, since a full plan is created for those ad-hoc queries upon the second execution of the query.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 31, 2012 at 6:31 am
Interestingly I do not see much difference before and after. Maybe 100 MB difference.
-Roy
January 31, 2012 at 6:32 am
You replied while I was updating my reply. Check the rest of my last post. 🙂
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 31, 2012 at 6:36 am
The compile plan stub is just 26.851
-Roy
January 31, 2012 at 6:38 am
It sounds like you have a ton of multi-use ad hoc queries, hence minimal benefit.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 31, 2012 at 6:42 am
Those were used just twice? First time it creates the stub, the second time it is used, it stores the plan in the cache and afterwards it is not used? That is a bit strange.
-Roy
January 31, 2012 at 6:46 am
They were used at least twice. Anything not used more than once will retain the stub. Upon being used a second time, the stub is replaced with an execution plan and subsequent uses may use that plan instead of the stub.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 31, 2012 at 6:53 am
What I am seeing is strange. When I stated my plan cache was 413 MB, I was talking about only single used plans. This could mean only one thing. The first time the query was executed, it created the Stub. The next time the same query was used, it cached the plan. After that it was never used.
This could mean that I am facing a pretty high compilation and the plans are not being used properly. Crap.
-Roy
January 31, 2012 at 8:19 am
Agreed. That is one of the biggest problems with ad-hoc queries. I have a client with a much higher ad-hoc cache than what you are seeing.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 31, 2012 at 8:33 am
Roy Ernest (1/31/2012)
On our OLTP server I set the value for "optimize for ad hoc workloads" as true.
Out of curiosity... do you really have a large number of ad-hoc queries against your OLTP system? this is usually a feature of some DSS systems like large Data Warehouses. On most OLTP sytems there is a limited number of battle-tested queries that are executed once and again.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 31, 2012 at 8:41 am
We have nHybernate in one application unfortunately. I do not think it has been optimized yet and they do not know how to optimize it yet.
But I am pushing for removal of nHybernate.
-Roy
January 31, 2012 at 9:32 am
The performance problem is at the .net development.
Tell you development team to take advantage of cache, lazy load, etc.
Most of the performance problems with nHybernate lies in loading entire sets of complete objects (and members).
I remember a case where for counting the number of hospitals the app loaded all hospitals (and all doctors, pacients, schedules etc for each hospital).
A lot of data to only get the answer: 42.
I dont like nHybernate and some IT directors claims theres no more need for SPs and querys at all (and realy its faster for development).
I say it's a tool like any other and can be the best choose for some sets of applications.
But for a large database centered I dont recomend nHybernate.
Discuss the problem with your .net development team.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply