Sql-server – Is it good sign? If SQL Server Buffer and Procedure cache hit ratio always showing 100%

cacheplan-cachesql serversql-server-2008

If 100% Buffer and Procedure cache hit ratio is considered not good in general?

I check Buffer Cache hit ratio with below query and it showing 100% (100.0000000000000)

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'

Are there instances that it is actually bad and can contribute to server performance degradation? I want to know how much of Buffer and Procedure Cache hit ratio is reasonable to have normal SQL server?

Best Answer

If 100% Buffer and Procedure cache hit ratio is considered not good in general?

You should not ONLY rely on buffer cache hit ratio it does not accurately measures or give signs of memory pressure the reason has been shown by Jonathan Kehayias in this Blog. The reason is concept called as Read Ahead in SQl Server.

Due to read aheads more than required pages are brought into memory every time a single scatter gather read happens and this mostly keeps buffer pool populated with pages giving indication that their is no memory pressure due to churning out of pages. If you read the blog you would find other important counters to look for memory pressure.

The read-ahead mechanism allows the Database Engine to read up to 64 contiguous pages (512KB) from one file. The read is performed as a single scatter-gather read to the appropriate number of (probably non-contiguous) buffers in the buffer cache. If any of the pages in the range are already present in the buffer cache, the corresponding page from the read will be discarded when the read completes. The range of pages may also be "trimmed" from either end if the corresponding pages are already present in the cache.

One cannot accurately say SQL Server has sufficient memory or not by just looking at BCHR, there are other counters to be taken into account

Related Question