SQL Server Buffer Cache Hit Ratio and memory pressure

08 June,2011 by Jack Vamvas

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

Buffer cache hit ratio add 

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

Related Posts

SQL Server Performance ,memory pressure and memory usage

SQL Server – Memory: Pages sec, memory pressure and thrashing

SQL Server – sys.dm_exec_sessions and troubleshooting sql memory usage


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server Buffer Cache Hit Ratio and memory pressure


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer