How big is your procedure cache? - SimonS Blog on SQL Server Stuff

How big is your procedure cache?

There have been continuing reports by most people I speak to about theire procedure cache becoming huge. Especially on 64 bit systems with lots of memory. Memory that you want for your data.

The issue is due to the storinging of compiled adhoc plans. They just aren't getting aged out of the cache.

We are lobbying for there to be a way of limitiing the size of the procedure cache. If I have 8Gb of memory I don't want 2 Gb to be used by the procedure cache. If you agree please vote on it here

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=293188

So you don't think it applies to you. I bet you will be suprised, run

select name, SUM(single_pages_kb + multi_pages_kb)/1024.0 MBUsed

from sys.dm_os_memory_clerks

group by name

order by 2 desc

and see how many MB is used by SQLPlans

Generate the scripts for a database and see what the difference is, I am sure you will be suprised.

You will also find that many of Microsoft tools that don't provide parameterised queries also cause the problem

Vote now your database needs you

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=293188


-
Published 15 August 2007 21:57 by simonsabin
Filed under: , ,

Comments

16 August 2007 09:00 by Colin Leversuch-Roberts

# re: How big is your procedure cache?

as I added to Tony's post whilst I can see this being a good idea can you imagine how this setting can be abused.

16 August 2007 09:07 by Colin Leversuch-Roberts

# re: How big is your procedure cache?

I'm also not convinced this view is actually updated that frequently, as you might know I'm having problems with the size of the TokenAndPermUserStore , as we speak I have this pegged at 750Mb from this query, however if I clear this the size does not usually change although if I count the number of entries of this type the count reduces - I'd be wary of this view. I have asked microsoft but they declined to clarify if using this view was the right way to size the cache.

16 August 2007 10:24 by simonsabin

# re: How big is your procedure cache?

I would envisage that the setting is only a maximum limit. So I don't see what abuse can be achieved. Ok someone can set it too small, but thats like saying don't allow people to use knives because they can cut themselves.