Buffer Cache Hit Ratio vs Cache Hit Ratio (Perfmon)
-
I have been monitoring the buffer cache hit ratio and the cache hit ratio with perfmon. My buffer cache hit ratio stays around 99% and my cache hit ratio stays around 63%.
I know that with the cache hit ratio being at 63..this means that cache is hit 63 times for every one disk lookup.
The dramatic difference is alarming to me. I was wondering if there is anything else I need to be monitoring to help me figure out what could be causing this difference.
I just did some memory configuration on this box..set the max server memory to 6 gig..enabled awe etc..(the OS was left with 2 gig). Before this memory configuration..this sql server box did not have a max server memory set ..nor was awe enabled..nor the /3gb switch in the boot.ini...and there were performance complaints and the cache hit ratio was at around 63%..I was hoping after I put the /3gb switch and dedicating 6 gig to sql server that I would see an improvement in the cache hit ratio but I am not. Granted...I have not heard of any performance complaints since I did this memory configuration, but the cache hit ratio not increasing bothers me.
This server is running windows 2003..sql 2000 enterprise edition..sp3a.
Any insight into this would be appreciated.
Kindest Regards,JWA
-
JWA
Buffer cache hit ratio is a percentage, whereas cache hit ratio is a simple ratio. So a cache hit ratio of 63 means, as you say, 63 cache hits per one lookup, or a percentage hit ratio of 98.44%. I believe that both are averages since SQL Server was started, so if you have a large amount of RAM you would expect the ratio to start low and improve steadily over time as the results of more and more disk reads are cached into RAM.
John
-
John,
Thank you for your reply
Another question if I may..how did u convert the 63 hits to a percentage of 98%?
Kindest Regards,JWA
-
JWA
( 63 / (63 + 1) ) * 100
John
-
Cache hits may not increase just because you increased the amount of memory. Most of the issues with lower cache hits that I've seen are a result of other problems - user stored procedures that begin with sp_ and developers not calling a stored procedure with a fully qualified name (exec storedprocname vs. exec dbo.storedprocname). You can monitor the cache hits and misses using profiler. It's kind of time consuming because the text data does not get captured with cache hits and misses. I normally capture stored procs and T-sql along with the cache misses to a table. You can then search the table for the miss, identify the offending spid, and then move backwards from the cache miss row number until you find the statement with the same spid. That will usually give you the offending statement.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply