Buffer Cache Hit Ratio

Hey guys, In this blog I am going to explain you about Buffer Cache Hit Ratio

Buffer Cache Hit Ratio- It is a pool of memory pages into which data pages are read. In other words, the data pages which are read from Disk are stored in Memory (Cache) to provide data faster. When a Data Read request is submitted, what SQL Engine does is it will go to disk retrieve the data from disk and place it in Memory (Cache) and return back the requested data. How this helps? When the request to same data pages is sent next time, there is no need to go to Disk again, it just reads from Cache and returns the data making data retrieval faster and there by less IO Operations. So, what are the impact when my buffer cache hit ratio is low, how does it impact performance.

Let’s us take an example. Say that you see that the Buffer Cache hit ratio (query is below to find the buffer cache hit ratio) is around 95% when you request data. This mean 95 times out of 100 requests; SQL was able to provide data from Memory(cache) and only 5 times it had to go to Disk.

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'

--BufferCacheHitRatio is between 90% to 100% then all is good.

--BufferCacheHitRatio is less than 90% then we need to think other counters.

select * from sys.dm_os_performance_counters where

object_name like '%SQLServer:Buffer Manager%'and counter_name like '%buffer ca%'

Low buffer cache results in High IO operations and thus leading to low performance.

Ø To measure the Buffer Cache Hit Ratio trend, there are two methods

Method-1: Using Performance monitor,

                   Go to Search ->Type Performance monitor -> Monitoring tools -> 

                   Performance monitor -> Add counter -> SQL Server Buffer Manager -> 

                   Buffer cache hit ratio

Fig-1

Fig-2

Method-2: Using SQL Server Query,

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'