Tag Archives: hit

MSSQL – See buffer hit ratio

Whit this code, you can see how much of query in percent at this moment is reading from the cache/buffer.

DECLARE @value numeric(25, 2), @basevalue numeric(25, 2)

declare @Cachehitratio real

set @Cachehitratio = null

--Cache hit ratio
SELECT @value = cntr_value FROM master..sysperfinfo (nolock) 
WHERE counter_name = 'Buffer cache hit ratio'
SELECT @basevalue = cntr_value FROM master..sysperfinfo (nolock) 
WHERE counter_name = 'Buffer cache hit ratio base'
set @Cachehitratio= (@value / @basevalue) *100

select @Cachehitratio