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
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
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
WHERE object_name = 'SQLServer:Buffer Manager' AND counter_name = 'Buffer cache hit ratio'
) AS A,
(SELECT cntr_value AS cntr_value2
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.
Republished from SQL Server DBA [65 clicks].
Read the original version here [32134 clicks].