How to calculate Buffer Cache Hit Ratio

 

The buffer cache hit ratio value measures how well your pages are staying in the buffer cache.  The closer the buffer cache hit ratio is to 100 % the better your buffer cache is being utilized, and the better performance you will get out of your SQL Server.   Here is how you would use this DMV to calculate the buffer cache hit ratio:

 

SELECT cast((CAST(A.cntr_value1 AS NUMERIC) / CAST(B.cntr_value2 AS NUMERIC))*100  as decimal(10,2)) AS Buffer_Cache_Hit_Ratio

FROM (

SELECT cntr_value AS cntr_value1

FROM sys.dm_os_performance_counters

WHERE object_name = 'SQLServer:Buffer Manager'

AND counter_name = 'Buffer cache hit ratio'

) AS A,

(

SELECT cntr_value AS cntr_value2

FROM sys.dm_os_performance_counters

WHERE object_name = 'SQLServer:Buffer Manager'

AND counter_name = 'Buffer cache hit ratio base'

) AS B