SQL Server Buffer Cache Hit Ratio

The buffer cache hit ratio details, without performing physical reads from disk, the percentage of pages found in the SQL Server buffer pool. It is widely accepted that this counter should not be assessed alone. The SQL Server performance monitor counter trends can provide important information about database health, but doesn’t provide complete diagnosis of overall SQL Server instance performance.

How to view buffer cache hit ratio

Buffer Cache Hit Ratio can be viewed either through the SQL Server Performance Monitor under “SQLServer:Buffer Manager” (or when using a named instance “MSSQL$InstanceName:Buffer Manager”) or by searching the sys.dm_os_performance_counters dynamic management view and filtering against the counter_name column using the value “Buffer cache hit ratio”.

Analyzing buffer cache hit ratio

Careful analysis of this data is imperative. Small drops in the buffer cache hit ratio over several hours or even a day does not indicate that there is a specific problem. Likely the user was requesting data that had been removed from memory due to recent lack of usage.

This information is better utilized with paired with the Page Life Expectancy counter, also found in the “SQLServer:Buffer Manager” performance monitor counter group, which shows how long the data within the buffer pool is expected to be kept in the SQL Server memory. There is no problem if the SQL Server shows a drop in the buffer cache hit ratio but the Page Life Expectancy hasn’t decreased very much. However, if both the buffer cache hit ratio and the Page Life Expectancy drop drastically, then a SQL Server performance problem is likely.

The buffer cache hit ratio should be as high as possible as it is undesirable to have the SQL Server reading from disk unless it is necessary. Increasing the buffer cache hit ratio in generally an easy fix. Repair any indexing problems so that clustered index scans (or table scans) do not happen so that large quantities of data pages are not read from the disk into memory. After completing this repair, add more RAM to allow more data to fit into memory.