THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

Want to Control the Procedure Cache?

... I know I do.  How many times have you seen the procedure cache bloat, for no good reason, because of badly designed applications? How many times have you been frustrated by the fact that SQL Server handles this in a relatively boneheaded way, and just keeps growing it and growing it--an especially huge problem on 64-bit systems?

So far I've not had great luck with Connect, but I figured I'd try again.  This is something we need now.  So if you are concerned about this issue, please vote...

 

Published Tuesday, August 14, 2007 9:29 AM by Adam Machanic

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Denis Gobo said:

You got my vote

August 14, 2007 9:28 AM
 

Linchi Shea said:

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

August 14, 2007 11:35 AM
 

Glenn Berry said:

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.

August 14, 2007 1:08 PM
 

Adam Machanic said:

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...

August 15, 2007 1:41 PM
 

Whitney said:

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 ;) )?

August 15, 2007 2:47 PM
 

Adam Machanic said:

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 :)

August 15, 2007 7:40 PM
 

Lara Rubbelke said:

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).  

August 15, 2007 9:53 PM
 

Theo Ekelmans said:

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

August 16, 2007 7:16 AM
 

WesleyB said:

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

August 16, 2007 10:03 AM
 

WesleyB said:

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

August 16, 2007 10:04 AM
 

Dan said:

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?

November 22, 2007 7:34 AM
 

Adam Machanic said:

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...

December 4, 2007 10:18 AM
 

Sangeetha said:

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

December 7, 2007 1:23 PM
 

Denis Gobo said:

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

December 27, 2007 4:11 PM
 

Alan Danque said:

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

April 2, 2008 10:35 AM
 

Adam Machanic said:

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

April 15, 2008 8:27 PM
 

LC said:

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.  

LC

April 23, 2008 8:44 PM
 

Aaron Bertrand said:

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

January 11, 2010 5:04 PM

Leave a Comment

(required) 
(required) 
Submit

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement