Want to Control the Procedure Cache?

re: Want to Control the Procedure Cache?

Denis Gobo — Tue, 14 Aug 2007 13:28:25 GMT

You got my vote

re: Want to Control the Procedure Cache?

Linchi Shea — Tue, 14 Aug 2007 15:35:31 GMT

It's the app guys' fault. Design a better app instead! GDR.

re: Want to Control the Procedure Cache?

Glenn Berry — Tue, 14 Aug 2007 17:08:37 GMT

I agree this would be a very nice configuration option. Until then, DBCC FREEPROCCACHE is your friend...

Seriously, I am forwarding this to a few people I know on the Product team to see if we can get this feature.

re: Want to Control the Procedure Cache?

Adam Machanic — Wed, 15 Aug 2007 17:41:47 GMT

Thanks for the votes, everyone. Hopefully this puts some pressure on them to act!

Glenn, agreed about DBCC FREEPROCCACHE--I have also set up scheduled dumps in the past. But people seem to get extremely paranoid about this stuff and in a recent consulting engagement where I suggested this as a possible fix--they had major proc cache bloat issues--the manager became extremely annoyed at the very idea that I would consider implementing such a "hack." Better for the engine to handle this, I think...

re: Want to Control the Procedure Cache?

Whitney — Wed, 15 Aug 2007 18:47:54 GMT

Adam,

After your suggested "hack" caused annoyance did the client move forward with the change or just let the cache continue to run wild? I get that it feels hackish but what other option do you really have (beside going to vote on connect ;) )?

re: Want to Control the Procedure Cache?

Adam Machanic — Wed, 15 Aug 2007 23:40:34 GMT

No--they refused to even try it, saying that it would "obviously" cause their CPUs to shoot to 100% and stay there... Of course, the compile/recompile counters already showed that virtually every query being submitted was already causing compiles to happen, so it wouldn't have made any difference... But sometimes paranoia wins over logic :)

re: Want to Control the Procedure Cache?

Lara Rubbelke — Thu, 16 Aug 2007 01:53:32 GMT

Thanks for submitting this one! I had posted a blog on my old blog detailing issues I have experienced with this one (http://blogs.digineer.com/blogs/larar/archive/2007/07/15/memory-pressure-on-sql-server-2005-64-bit.aspx).

re: Want to Control the Procedure Cache?

Theo Ekelmans — Thu, 16 Aug 2007 11:16:47 GMT

I've made a similar suggestion on MS connect (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289691)

MS replied "we plan to release a new parameter of FREEPROCCACHE where you can identify a plan using its sql_handle or plan_handle and evict the plan from the proc cache"

This way you can pick and choose what plans are unwanted and remove them from the procedure cache, i.e. the unparameterised danamic queries with a useage count of 1

re: Want to Control the Procedure Cache?

WesleyB — Thu, 16 Aug 2007 14:03:48 GMT

Yep... know the feeling.

http://sqlug.be/blogs/wesleyb/archive/2007/01/24/486.aspx

Lucky for us they already resized it in SP2 :-s

re: Want to Control the Procedure Cache?

WesleyB — Thu, 16 Aug 2007 14:04:13 GMT

Oh yeah, but I do agree with Linchi :-D

re: Want to Control the Procedure Cache?

Dan — Thu, 22 Nov 2007 12:34:05 GMT

Hi Adam

I attended Kalen Delaney's talk to the UK user group in Reading last week. She went to great pains to point out how, in 2005 SP2, the plan cache clears trival ad-hoc plans once a threshold is reached. Reading between the lines it looks like cache bloat can still occur but by definition these plans would be non trivial, prepared plans - not the sort of stuff a poorly designed app is likely to churn out. This covers some of it: http://sqlblog.com/blogs/kalen_delaney/archive/2007/11/04/did-you-know-sp2-does-not-limit-the-amount-of-plan-cache-you-can-have.aspx

What have I missed?

re: Want to Control the Procedure Cache?

Adam Machanic — Tue, 04 Dec 2007 15:18:59 GMT

Dan: You may not have missed anything at all. I have not yet had a chance to test SP2 on the kinds of workloads that prompted me to write this post...

re: Want to Control the Procedure Cache?

Sangeetha — Fri, 07 Dec 2007 18:23:21 GMT

Hi Adam, Have you had a chance to test out SQL 2005 SP2?

A year in review, The 21 + 1 best blog posts on SQLBlog

Denis Gobo — Thu, 27 Dec 2007 21:11:29 GMT

A year in review, The 21 + 1 best blog posts on SQLBlog Best posts according to me, it might have been

re: Want to Control the Procedure Cache?

Alan Danque — Wed, 02 Apr 2008 14:35:44 GMT

You have my vote too! I installed SP2 and proccache still bloats out due to application exec of sp_execute. thanks

SQL Server Procedure Cache: More Relief on the Way

Adam Machanic — Wed, 16 Apr 2008 00:27:24 GMT

If you've read many of my blog posts, you know that I consider lack of procedure cache control to be

re: Want to Control the Procedure Cache?

LC — Thu, 24 Apr 2008 00:44:57 GMT

I'm one of the many DBAs suffering from this lack of proc cache control. Our environment is mostly adhoc (parameterized, very few stored procs) by nature. I've been running dbcc freesystemcache('all') every 30mins for ~2yrs now. None of the 'fixes' from MS seem to fix the problem. Adam's link above is only for SQL2008 so everyone on SQL2005 seems to be hosed. With SQL2008 on the way I doubt MS will dedicate resources to SQL2005 and add any new features. They're in mtc mode already with SQL2005 and all new features are going to SQL2008.

Regarding Adam's link about caching the stub in SQL2008, I've been told by MS support recently that SQL will only cache a 300 byte 'stub' on the first run of the query (vs a minimum 32KB plan). If the query is executed again it will upgrade the stub and cache the full plan. I asked the support engineer twice and he verified the stub caching is only on the first run, not the subsequent runs so it doesn't sound like much help to me.

Connect Digest : 2010-01-11

Aaron Bertrand — Mon, 11 Jan 2010 22:04:02 GMT

Control over procedure cache We are still waiting for more control over procedure cache. Adam Machanic