SQL Server – How to Improve Procedure Cache Hit Ratio

performancesql serversql-server-2005

From what I can tell Procedure Cache Hit Ratio below 95% is a problem. On my box, the values hover from 85 to 95%.

How do I fix this issue? The server seems to have plenty of RAM so that shouldn't be an issue. What else can it be?

Best Answer

Let me summarize (and round!) the important data points in your spreadsheet:

      Total                                     Use Count 1
      ---------------------------------------   -----------------------
      Total Plans   Total MBs   Avg Use Count   Total Plans   Total MBs   
      -----------   ---------   -------------   -----------   ---------
Adhoc      55,987       3,054               3        38,314       2,036
Proc          709       1,502           1,549           135         527

So the first row shows the bad stuff, taking up about 2/3 of your plan cache (things which are mostly only ever used once, with a few very minor exceptions). You need to try and get rid of as much of these as you can. The second row shows the good stuff. These are the things you want in your plan cache (plans with a high amount of reuse). The rest of the data is largely irrelevant IMHO. One other point though: you say that access is exclusively through stored procedures, but if those procedures use dynamic SQL, those statements are cached as AdHoc plans, not Proc plans.

On 2008 or greater, I would say turn on optimize for ad hoc workloads and move on to the next problem - this would take the amount of MBs your single-use plans currently occupy down to next to nothing. Unfortunately, on 2005, your options are quite limited, aside from refactoring those stored procedures to use statement-level OPTION (RECOMPILE) and/or less / no dynamic SQL, or turning on forced parameterization at the database level - which tries to get better plan reuse out of similar queries by treating literals as parameters for plan matching purposes. I hesitate to even mention plan guides because they're not for the timid and - as I discuss later on in this answer - I'm not sure it's worth going down that path unless you know your plan cache is definitely the source of your performance issue.

I asked about @@VERSION because, before SP2, the algorithm for the amount of memory that could be allocated to the plan cache was relatively loosey-goosey. As of SP2 they tightened that up quite a bit (the change is documented and explained in this post and this post). In your case, the plan cache is relatively full, so it is not surprising you're getting cache misses. 26 GB = an upper limit of 5.8 GB; I see ~4.5 GB in the spreadsheet but there may be some calculation or configuration difference here that I don't know about.

This MSDN article talks about the optimize for ad hoc workloads server setting added in 2008, and mentions trace flag 8032, which will allow you to allocate more memory to your caches (presumably in the absence of setting this setting at the server level, which I now recommend to all of our customers, or at least the 99% that are no longer on 2005). I have never tested this trace flag on 2005 SP3 or SP4, and honestly not even sure when it was introduced. I also don't know if it will solve your problem or just shift it, since I think even if you had some % more RAM allocated to caches, you'd still be filling it and having lots of cache misses because of the nature of your stored procedures.

Or, of course, if there is even a problem to solve that relates directly to the plan cache at all. Just because your cache hit ratio isn't as high as you might expect does not mean that it is causing your problem, and of course the converse is that even at 100% cache hit ratio - which doesn't seem realistic given that so many of your plans are single-use and ad hoc - your users may still be suffering from performance problems caused by something else entirely.

My suggestion is to look for better smoking guns than plan cache hit ratio. Get more specifics about your users' performance complaints. Are all queries always slow? Certain queries? Certain times of day / week / business cycle? Are only reporting queries slow? Take a serious read through this admittedly dry and long document on SQL Server best practices - in particular, the section on waits and queues, which can help you formulate a logical approach to identifying, diagnosing and solving performance issues. Making some number on a dashboard look better - a number that you don't even know directly contributes to the problem - might be very satisfying, but if it doesn't solve your users' performance issues, then it hasn't really gotten you anywhere.

These may also be useful in reading up on compilation / recompilation and plan cache reuse. Some of these are focused on 2008 (particularly those about the ad hoc workloads setting), but much of the information is still useful for 2005 and/or to better understand the benefits of upgrading (hint, hint).

Related Question