How To Expose Aging Metrics For Cached Query Plans?
-
I am trying to track down the aging metrics for a particular stored procedure compiled execution plan. I've looked at the syscacheobjects table but this does not have what I need.
I've got a procedure that takes 15-25 minutes to compile depending on the overal CPU utilization. It get's dropped from cache a few times a day...even though I would expect this proc plan to stay put since it takes 90000+ CPU cycles to compile.
I would like to to be able to expose the aging metric - the one that once it gets down to zero the plan is marked for deallocation and the lazy writer will then dispose of when free pages are needed in the buffer (well, I'm assuming its the lazy writer that is also responsible for this).
Is there a way to do this? Rather than blindly re-running the proc on a scheduler to keep its plan in cache, I would rather query something much more light weight and execute the proc then and only then (to keep its plan in cache).
Thanks. RH
-
If the plan is getting aged out then the proc will not be getting called very often - the whole point of the process is to only keep oft used plans in cache. You might consider splitting your proc into smaller procs - I've never been a lover of large plans, they often don't optimise well anyway. More memory will possibly increase the size of the proc cache and increase the stay in cache.
Have you considered optimising the query to reduce the compile time?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Yes, I have considered it. The proc is a vendor supplied proc that sorts through about 25 versions of a query and executes one using an IF condition against the input params. It really should be 25 different procs. However, making it 25 different procs means getting development time out of my report writing team to go back and modify many reports. This is tough to get for several reasons...lets just leave it at that.
This server is typically very busy betwen 7 AM and 7PM and completely idle otherwise. Cache objects seem to get marked for deallocation during the nighttime hours. What gets called first thing determines what gets dropped and what doesn't.
My cached object pages (NOT cached database pages) go from about 20MB in the morning to about 350MB by the evening. Many items are getting recompiled and cached in the morning that I would like to never drop.
I understand SQL Server's dynamic management strengths - and I typically don't sway from them. However, I would like to experiment in this case.
RH
-
You could modify the first sp to call any one of 25 other sp's based on the input. The result set would still be returning to the same calling sp. The reason your procedure is recompiling is as data changes the optimizer sees that the plan it has is no good for that dataset. I've never tried it but there is an option clause for queries and one of the options is to keep plan and keepfixed plan. You might be able to add that to the query to prevent recompiles.
Tom
-
Tom, good suggestion on the one proc calling 25 - I agree. Also, thanks for the info on the optimizer hint. I hadn't heard of it before. RH
-
I have used, but don't suggest using optimizer hints. They can bite you.
Good luck. Let us know how it goes.
-
The other issue you might have is that updated stats/index rebuilds will mark the proc for recompilation, if you have auto stats turned on this could happen at almost any time.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply