Whit this code, you can see how much of query in percent at this moment is reading from the cache/buffer.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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 |