Sql-server – What does a “Buffer cache hit ratio” of 9990 mean

performancesql serversql-server-2012

I got this query from a blog post:

SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Buffer cache hit ratio'

The post said that it would give me a percentage of hits to the cache. It seemed to indicate that it would be a value of 0-100 (it showed a result of 87).

But when I run it I am getting very high numbers. Here is an example:

object_name               counter_name             cntr_value  
SQLServer:Buffer Manager  Buffer cache hit ratio   9990

Does this mean 99.90 %?

If not, what does it mean? And how can I get the real value?

NOTE: I have gotten values as low as 257 and as high as 352363

Incase it is relevant, here are a few other server stats:

  • Page life expectancy: 145
  • Page reads/sec: 1,380,009,009

Best Answer

Confusing, right?

Well, to actually get the ratio, you'll need to do it yourself using the Buffer cache hit ratio base in addition to the Buffer cache hit ratio by taking the result from Buffer cache hit ratio / Buffer cache hit ratio base.

Try the below query (from Less Than Dot), which should give you the % you're looking for:

SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 as BufferCacheHitRatio
FROM sys.dm_os_performance_counters  a
JOIN  (SELECT cntr_value, OBJECT_NAME 
    FROM sys.dm_os_performance_counters  
    WHERE counter_name = 'Buffer cache hit ratio base'
        AND OBJECT_NAME = 'SQLServer:Buffer Manager') b ON  a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = 'Buffer cache hit ratio'
AND a.OBJECT_NAME = 'SQLServer:Buffer Manager'
Related Question