Buffer Cache Hit Ratio is the percentage of SQL server pages requested and retrieved from the buffer cache without reading from disk. Reading data from disk is slower than reading from memory. High Buffer Cache Hit Ratio indicates a large amount of data pages are accessed from cache, its meaning data will return much faster. The Microsoft recommended minimum Buffer Cache Hit Ratio is 95. If the percentage drops less than 95% for a period of time , the one way of increasing the Buffer Cache Hit Ratio would be to increase physical memory.
Here is the query that will return Buffer Cache Hit Ratio.
1 2 3 4 5 6 7 8 9 10 |
SELECT dopc.object_name,dopc.counter_name, cast((cast(((dopc.cntr_value * 1.0 / sdopc.cntr_value) * 100.0) as decimal(8,5))) as varchar(20)) + '%' AS [Ratio] FROM sys.dm_os_performance_counters AS dopc INNER JOIN (SELECT cntr_value, [object_name] FROM sys.dm_os_performance_counters WHERE counter_name = N'Buffer cache hit ratio base' AND [object_name] LIKE N'%Buffer Manager%') AS sdopc ON dopc.[object_name] = sdopc.[object_name] WHERE dopc.counter_name = N'Buffer cache hit ratio' AND dopc.[object_name] LIKE N'%Buffer Manager%' |
Leave a Comment