procedure cache

Question

procedure cache

madhu-686862

SSChampion

Points: 10707
More actions
April 30, 2009 at 6:46 pm

#133825

hi,
We are using Quest Spotlight for monitoring SQL Sevrer 2005.Iam frequently getting Procedure cache alaram as it is falls below 80%(Its not going behond 75%)
Alarm raisedProcedure Cache hit rate is 79.31%.InformationMemory - Procedure Cache Hit Rate Alarm
This means we have any memory pressure? What would be the recommended % of Procedure cache?
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

That can be one sign of memory pressure, but it could also be a sign of a ad-hoc workload where parametrization is not being used in the queries submitted to the server which causes your hit ratio to drop. When you are looking at memory pressure, you need to look at the whole picture, not just one particular counter only.

SQLBOT SSCrazy Eights Points: 8014 More actions · Answer 2

Jonathan is probably right.

If you're using spotlight, drill into the memory counters and see if the proc cache is being pinched by any of the other memory areas (lock, object cache, connection). If it's not being pinched then you might have too many different stored procs running (cycling throught the cache too much) or too much ad-hoc (more likely). Query out sys.dm_exec_cached_plans to get a better picture of what's inside the cache, how long it stays there... pay most attention at this point to the objtype field.

that will give you a better picture of what's type of plan is taking up the most space in the cache.

If it's Ad Hoc, then we probably have the answer.

Also, It may not even be a real issue for you: On some of my bigger instances (500+ db's) it's not uncommon for the proc cache hit ratio to be about 75% due to the natural order of caching and high activity. I think not being able to specify the proc cache size is a joke because I don't think SQL OS manages it very well as a ratio of the general single page allocation pool.

last word: if it is ad-hoc, turning forced parameterization at the db level can help... esp. if it's 1 or 2 db's that are causing most of the problem.

Cheers!

~BOT

Craig Outcalt

Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

madhu-686862 SSChampion Points: 10707 More actions · Answer 3

These are share point databases sitting in sql server 2005.So, I can not change anything. Because MS says If you change any index,alter any stored proc, they do not support.

What we can do in this situation to know what really causing the procedure cache hit ratio to go down?

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 4

Your first step would be to query the DMV mentioned above, and see what is in the cache. Another thing would be to look at what activity might be happening on the server when the cache drops below 80%, is SPS doing an index crawl or some other activity that would be suspect of causing this? Just looking at my sharepoint database server activity, I see that there are a lot of sp_execute's happening from sharepoint, and that a lot of them have different queries being run, though they are parameterized, this can cause the problem you are asking about as well, if enough permutations of the queries get executed. They still have to be optimized and cached.