— Below script will help you to find the Buffer cache hit ratio value in %.
–Method 1:
SELECT [object_name],
[counter_name],
[cntr_value] FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE ‘%Manager%’
AND [counter_name] = ‘Page life expectancy’
—Method 2:
DECLARE @Buffer_cache_hit_ratio INT;
DECLARE @Buffer_cache_hit_ratio_base INT;
SELECT @Buffer_cache_hit_ratio = cntr_value FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE ‘%Buffer Manager%’ AND [counter_name] = ‘Buffer cache hit ratio’
SELECT @Buffer_cache_hit_ratio_base = cntr_value FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE ‘%Buffer Manager%’ AND [counter_name] = ‘Buffer cache hit ratio base’
select 100*@Buffer_cache_hit_ratio/@Buffer_cache_hit_ratio_base as ‘Buffer_cache_hit_ratio_value’
—-Method 3:
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’
selectstarfromyogi 