Wednesday, November 21, 2007

DMV Performance Counters - Buffer Cache hit ratio example

Demonstrates the dmv, sys_dm_os_performance_counters.
Returns a single value, i.e. the buffer cache hit ratio.
This represents how well pages stay in buffer cache.
The closer the result is to 100%, the better.

Corrected from version here

This version -
  1. includes the necessary join
  2. will run on any server (wildcarded the server name)
SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 [BufferCacheHitRatio]
FROM (SELECT *, 1 x
     FROM sys.dm_os_performance_counters
    WHERE counter_name = 'Buffer cache hit ratio'
      AND object_name like '%:Buffer Manager%') a
JOIN
  (SELECT *, 1 x
  FROM sys.dm_os_performance_counters
    WHERE counter_name = 'Buffer cache hit ratio base'
      AND object_name like '%Buffer Manager%') b
ON a.x = b.x

No comments: