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?
|show 17 more comments|
Let me summarize (and round!) the important data points in your spreadsheet:
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
On 2008 or greater, I would say turn on
I asked about
This MSDN article talks about the
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).