What does a “Buffer cache hit ratio” of 9990 mean?

Question

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

Answer 1

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'

Answer 2

If you don't have a default instance of SQL server, but named instance, you have to modify query like this:

  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 LIKE '%:Buffer Manager%') b ON  a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = 'Buffer cache hit ratio'
AND a.OBJECT_NAME LIKE '%:Buffer Manager%'