Buffer Cache Hit Ratio: Why a Healthy Number Can Lie

A high buffer cache hit ratio does not prove your memory is fine. It is a percentage built from two counters, and it can look perfect while a user waits on a slow report. Read it with other counters, not alone.

The call that starts the argument

A manager says the application feels slow. You open a monitoring tool. Buffer cache hit ratio says 99.9 percent, so you say memory is fine and close the ticket.

The user is still waiting. Both of you are right, and that is the problem. The counter measures how often a page request was answered from memory. It does not measure how many pages were requested, or how long the unlucky ones waited.

Why a big number can hide a bad day

Let me use made-up numbers, because the idea is plain arithmetic. A quiet server answers one million page requests and misses 20,000 of them. That is 98 percent.

Now one runaway report scans cached pages and adds nine million more requests. All nine million are hits. The misses stay at 20,000, so the ratio climbs to 99.8 percent. The server did ten times the work, and the counter says things got better.

SELECT Situation,
       HitRequests,
       MissRequests,
       100.0 * HitRequests / (HitRequests + MissRequests) AS HitPercent
FROM (VALUES
    ('Quiet server',           980000, 20000),
    ('After one runaway scan', 9980000, 20000)
) AS Demo (Situation, HitRequests, MissRequests);

The result shows 98.000000 for the quiet server and 99.800000 after the scan. Nothing here is measured. It is only a way to see how a flattering number is built.

Read the real counter with its base

The counter lives in sys.dm_os_performance_counters, and it comes in two rows. One is the ratio value. The other has the word base on the end. Divide the first by the second and multiply by 100. The raw value alone means nothing.

SELECT RTRIM(object_name) AS counter_group,
       MAX(CASE WHEN RTRIM(counter_name) = 'Buffer cache hit ratio'
                THEN cntr_value END) AS ratio_value,
       MAX(CASE WHEN RTRIM(counter_name) = 'Buffer cache hit ratio base'
                THEN cntr_value END) AS base_value,
       100.0 * MAX(CASE WHEN RTRIM(counter_name) = 'Buffer cache hit ratio'
                        THEN cntr_value END)
             / NULLIF(MAX(CASE WHEN RTRIM(counter_name) = 'Buffer cache hit ratio base'
                               THEN cntr_value END), 0) AS HitPercent
FROM sys.dm_os_performance_counters
WHERE RTRIM(counter_name) IN ('Buffer cache hit ratio', 'Buffer cache hit ratio base')
GROUP BY RTRIM(object_name);

You get one row for the Buffer Manager. On my test instance the percentage was at or very near 100 in every run, but the raw values changed each time. Yours will differ too.

Counters named per second are not rates

Here is another trap in the same view. Page reads/sec sounds like a rate. It is a running total since the instance started. To get a real rate you take two samples and divide the difference by the seconds between them.

DROP TABLE IF EXISTS #Before;

SELECT RTRIM(counter_name) AS counter_name, cntr_value, SYSDATETIME() AS taken_at
INTO #Before
FROM sys.dm_os_performance_counters
WHERE RTRIM(object_name) LIKE '%:Buffer Manager'
  AND RTRIM(counter_name) IN ('Page reads/sec', 'Lazy writes/sec');

WAITFOR DELAY '00:00:05';

SELECT b.counter_name,
       n.cntr_value AS running_total,
       n.cntr_value - b.cntr_value AS change_in_window,
       (n.cntr_value - b.cntr_value) / DATEDIFF(second, b.taken_at, SYSDATETIME()) AS per_second
FROM #Before AS b
JOIN sys.dm_os_performance_counters AS n
  ON RTRIM(n.counter_name) = b.counter_name
 AND RTRIM(n.object_name) LIKE '%:Buffer Manager'
ORDER BY b.counter_name;

DROP TABLE #Before;

Look at running_total first. In my run it is a large number that has nothing to do with this second. The per_second column is the honest rate for those five seconds. Mine shows a small number for page reads and zero for lazy writes. Run it again during a busy report and watch it move.

Put the number beside better evidence

Two more checks give the ratio some company. Page life expectancy says how long a page tends to stay in memory, in seconds. Average read latency per file says how long the misses actually waited, in milliseconds. Judge both as a trend on your own server, not as one reading.

SELECT RTRIM(object_name) AS counter_group,
       cntr_value AS page_life_expectancy_sec
FROM sys.dm_os_performance_counters
WHERE RTRIM(counter_name) = 'Page life expectancy'
ORDER BY object_name;

SELECT file_id,
       num_of_reads,
       io_stall_read_ms,
       CONVERT(decimal(10,2), 1.0 * io_stall_read_ms / NULLIF(num_of_reads, 0)) AS avg_read_ms
FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL)
ORDER BY file_id;

On mine, the first query returns two rows because the instance reports both a Buffer Manager and a Buffer Node. The second returns one row per file in the current database. Do not chase one magic threshold. A low page life expectancy that stays low, slow reads, and a user complaint together tell a story. A single number never does.

If a report is the suspect, run it on a test system and read its STATISTICS IO output. Scans can use read-ahead to pull pages in early, which the ratio flatters. Please do not clear the production cache just to make a demo.

Next time someone quotes the ratio, ask what the reads and the waits look like.

A healthy ratio is not a healthy server, it is one clue in a longer story.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.