Buffer Cache Hit Ratio is the percentage of sql server pages requested and retrieved from the buffer cache without reading from disk.

SQL Server reads data pages into a pool of memory called the Buffer Cache. The Buffer Cache Hit Ratio is a good indicator of Buffer Cache performance.

The calculation is: total number of cache hits \ total number of cache lookups from the previous few thousand page requests.

Reading from disk is expensive, it is generally faster to read from cache. Maintaining a high Buffer Cache Hit Ratio indicates a large amount of pages are accessed from cache – meaning data will return faster.

The Buffer Cache Hit Ratio is a good measure of memory pressure,along with the Page Life Expectancy measure . They both measure slightly different aspects of the buffer cache.

When using Buffer Cache Hit Ratio don’t use the absolute value, but measure over a period of time. Look for a trend. I prefer to monitor for at least 30 minutes over a typical user workload.

The Microsoft recommendation is 95% and higher. If the percentage drops less for a period of time – one way of increasing the Buffer Cache Hit Ratio could be to increase physical memory.

There are situations where it’s obvious increasing physical memory will improve the Buffer Cache Hit Ratio, but not always. In those situations look first to:

a)index optimization and sql server tuning

b)table design

c)quality queries, that could mean rewriting queries

d)SQL Server memory configuration

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

Method 1 – Perfmon. Add Buffer cache hit ratio for the SQL Server Instance

Method 2 – Query the sys.dm_os_performance_counters

--alter the 'MSSQLServer:Buffer Manager' if using a name instance e.g --'MSSQL$INST1:Buffer Manager'

SELECT ROUND(CAST(A.cntr_value1 AS NUMERIC) /
CAST(B.cntr_value2 AS NUMERIC),3) AS Buffer_Cache_Hit_Ratio
FROM ( SELECT cntr_value AS cntr_value1
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Buffer Manager' AND counter_name = 'Buffer cache hit ratio'
) AS A,

(SELECT cntr_value AS cntr_value2
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Buffer Manager' AND counter_name = 'Buffer cache hit ratio base'
) AS B;

The SQL Server Buffer Cache Hit Ratio is useful for memory bottleneck, but should be used with other indicators. I’ve mentioned Page Life Expectancy, but another related indicator is the SQL Server Plan Cache Object and Cache Hit Ratio – which I’ll write about in a future post.

Republished from http://www.sqlserver-dba.com.