SQL Server buffer cache hit ratio low: when the working set no longer fits in memory

A low buffer cache hit ratio (BCHR) gets two reactions in production: teams page on-call for a single dip during a maintenance window, or they ignore a sustained decline because the counter is “unreliable.” Both are wrong. BCHR is a weak signal alone, but paired with Page Life Expectancy (PLE), PAGEIOLATCH_* waits, and workload context, it tells you whether your working set still fits in the buffer pool.

For OLTP, BCHR should sit above 99%. Below 95% on OLTP means the working set no longer fits comfortably in the buffer pool and you are paying for it in physical I/O. Below 90% on OLTP, with elevated PAGEIOLATCH_* waits and rising I/O stall, is critical. A data warehouse doing large sequential scans legitimately runs at 80-95%, and treating that as an incident is a category error.

The counters behind BCHR are cumulative since server start, which is why the ratio becomes very stable over time and why a sudden drop is informative while a slow decline is hard to spot without trending. A single sample taken during a burst scan, a cold start, or a DBCC operation misleads.

What this means

BCHR is the percentage of page requests satisfied from the buffer pool without a physical disk read. It is computed as Buffer cache hit ratio divided by Buffer cache hit ratio base, multiplied by 100. Both counters live in sys.dm_os_performance_counters under the Buffer Manager object.

-- Read BCHR as a percentage
SELECT
    (a.cntr_value * 1.0 / NULLIF(b.cntr_value, 0)) * 100.0 AS buffer_cache_hit_ratio
FROM sys.dm_os_performance_counters a
JOIN sys.dm_os_performance_counters b
    ON a.object_name = b.object_name
   AND ISNULL(a.instance_name, '') = ISNULL(b.instance_name, '')
WHERE a.counter_name = 'Buffer cache hit ratio'
  AND b.counter_name = 'Buffer cache hit ratio base';

Because the counters are cumulative, once the buffer pool has been warm for hours the ratio barely moves. A recent regression is visible as a slow drift, not a cliff. Trend the value; do not alert on a single sample.

BCHR includes TempDB page reads. On workloads with heavy TempDB usage (large sort/hash spills, aggressive temp table use, RCSI version store activity), TempDB churn can pull the ratio down even when the user database working set fits in memory. Check whether TempDB is the consumer before concluding the buffer pool is undersized.

Workload context is mandatory

A single BCHR threshold does not apply across workload types:

A data warehouse at 85% BCHR during a nightly scan window is healthy. An OLTP database at 85% during business hours is in trouble. Classify the workload before interpreting the number.

Common causes

The last row is the trap. Read-ahead keeps the cache populated with pages the scan is about to consume, so BCHR can stay high even when the buffer pool is churning violently. This is why BCHR alone is a weak signal and must be read with PLE.

Quick checks

Run these read-only checks before escalating. They take seconds and resolve most misdiagnoses.

-- 1. Current BCHR
SELECT
    (a.cntr_value * 1.0 / NULLIF(b.cntr_value, 0)) * 100.0 AS buffer_cache_hit_ratio
FROM sys.dm_os_performance_counters a
JOIN sys.dm_os_performance_counters b
    ON a.object_name = b.object_name
   AND ISNULL(a.instance_name, '') = ISNULL(b.instance_name, '')
WHERE a.counter_name = 'Buffer cache hit ratio'
  AND b.counter_name = 'Buffer cache hit ratio base';

-- 2. Page Life Expectancy (instance-wide and per NUMA node)
SELECT 'instance' AS scope, cntr_value AS ple_seconds
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'
  AND object_name LIKE '%Buffer Manager%'
UNION ALL
SELECT instance_name AS scope, cntr_value AS ple_seconds
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'
  AND object_name LIKE '%Buffer Node%';

-- 3. Top waits (exclude benign idle waits)
SELECT TOP 15
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms,
    CAST(100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS DECIMAL(5,2)) AS pct
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    'SLEEP_TASK', 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP',
    'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT', 'LAZYWRITER_SLEEP',
    'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'XE_TIMER_EVENT',
    'XE_DISPATCHER_WAIT', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
    'BROKER_EVENTHANDLER', 'SP_SERVER_DIAGNOSTICS_SLEEP',
    'HADR_FILESTREAM_IOMGR_IOCOMPLETION', 'DIRTY_PAGE_POLL',
    'DISPATCHER_QUEUE_SEMAPHORE', 'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
    'QDS_ASYNC_QUEUE', 'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH',
    'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE', 'HADR_WORK_QUEUE',
    'BROKER_TRANSMITTER', 'KSOURCE_WAKEUP'
)
AND waiting_tasks_count > 0
ORDER BY wait_time_ms DESC;

-- 4. OS memory state
SELECT
    total_physical_memory_kb / 1024 AS total_physical_mb,
    available_physical_memory_kb / 1024 AS available_physical_mb,
    system_memory_state_desc
FROM sys.dm_os_sys_memory;

-- 5. SQL Server process memory and pressure flag
SELECT
    physical_memory_in_use_kb / 1024 AS sql_physical_mb,
    process_physical_memory_low,
    process_virtual_memory_low
FROM sys.dm_os_process_memory;

-- 6. Memory grants pending (should be zero)
SELECT cntr_value AS memory_grants_pending
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Memory Grants Pending'
  AND object_name LIKE '%Memory Manager%';

-- 7. I/O stall per file (focus on data files for read latency)
SELECT TOP 20
    DB_NAME(vfs.database_id) AS database_name,
    mf.name AS file_name,
    mf.type_desc,
    vfs.io_stall_read_ms,
    vfs.num_of_reads,
    CASE WHEN vfs.num_of_reads > 0
         THEN vfs.io_stall_read_ms * 1.0 / vfs.num_of_reads
         ELSE 0 END AS avg_read_latency_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN sys.master_files mf
    ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
ORDER BY vfs.io_stall_read_ms DESC;

For TempDB space consumers, run the following in a TempDB-connected session:

-- 8. TempDB space consumers (run in TempDB context)
USE tempdb;
SELECT
    SUM(user_object_reserved_page_count) * 8 / 1024 AS user_objects_mb,
    SUM(internal_object_reserved_page_count) * 8 / 1024 AS internal_objects_mb,
    SUM(version_store_reserved_page_count) * 8 / 1024 AS version_store_mb,
    SUM(unallocated_extent_page_count) * 8 / 1024 AS free_space_mb
FROM tempdb.sys.dm_db_file_space_usage;

These checks tell you whether the low BCHR is a memory problem, a storage problem, a workload problem, or a non-problem.

How to diagnose it

1. Confirm the workload type. Is this OLTP or a data warehouse / reporting workload? If the latter and BCHR is 80-95% during scan-heavy windows, it is likely not an incident. Check whether the drop correlates with a known reporting job or maintenance window.

2. Rule out cold start. If the instance restarted recently, BCHR and PLE will both be low and climbing. Check SQL Server uptime. If the trend is upward and there are no user complaints, wait for warmup before acting.

3. Read BCHR alongside PLE. This is the single most important step. PLE tells you how long pages survive in the buffer pool before eviction. A reasonable community heuristic (not Microsoft-specified) is that PLE should be at least (buffer_pool_size_GB / 4) * 300 seconds. The old “PLE below 300” rule is calibrated for 32-bit systems with roughly 4GB and is misleading on modern hardware.

   • BCHR low and PLE low and dropping: buffer pool memory pressure. Move to step 4.
   • BCHR low and PLE stable: likely a scan-heavy workload or storage latency issue, not memory pressure. Check I/O stall and the active query profile.
   • BCHR high and PLE low: read-ahead is masking churn. The buffer pool is cycling pages fast even though hits are being served. This is the classic “100% BCHR with PLE of 103 seconds” trap. Treat it as memory pressure.

4. Check per-NUMA-node PLE. On NUMA systems, the instance-wide PLE is an average across nodes, not a minimum. One node can be starved while the aggregate looks healthy. Query Buffer Node(NNN)\Page life expectancy for each node. If nodes are imbalanced, you have a foreign-memory-access problem or a workload skew problem, not a simple memory shortage.

5. Check wait statistics for PAGEIOLATCH_*. PAGEIOLATCH_SH and PAGEIOLATCH_EX are the waits that directly reflect physical page reads. If they are dominant (more than 30-40% of total waits) and rising, the buffer pool is not absorbing reads. This confirms the low BCHR is real pressure, not a sampling artifact.

6. Check I/O stall on data files. Use sys.dm_io_virtual_file_stats deltas. If read latency is above 20ms sustained on data files, the storage subsystem is struggling to keep up with the physical read rate the buffer pool is generating. This is the second-order effect of memory pressure, but it can also be the root cause: slow storage makes the buffer pool look undersized.

7. Check for a single query hoarding a memory grant. A bad plan with an overestimated sort or hash can grab a large memory grant, shrinking the buffer pool and triggering the spiral. Query sys.dm_exec_query_memory_grants ordered by granted_memory_kb or wait_time_ms.

8. Check OS-level memory pressure. system_memory_state_desc showing anything other than “Available physical memory is high”, or process_physical_memory_low = 1, means the OS is pressuring SQL Server. Check for co-located processes, VM balloon drivers, or max server memory set too high and starving the OS.

flowchart TD
    A[Low BCHR reported] --> B{Workload type?}
    B -- Data warehouse / scan --> C[Check window and maintenance schedule]
    B -- OLTP --> D{Recently restarted?}
    D -- Yes --> E[Wait for warmup, trend upward]
    D -- No --> F{Read PLE}
    F -- PLE low and dropping --> G[Memory pressure: grants, OS, max memory]
    F -- PLE stable --> H[Scan or storage: I/O stall and active queries]
    F -- PLE low but BCHR high --> I[Read-ahead masking: treat as memory pressure]
    G --> J[Correlate with PAGEIOLATCH waits and I/O stall]
    H --> J
    I --> J
    J --> K[Confirm root cause before acting]

Metrics and signals to monitor

Fixes

Buffer pool undersized

If max server memory is set too low relative to physical RAM and the workload, raise it. On a dedicated SQL Server host, max server memory should typically be set to roughly 75-85% of physical RAM, leaving the remainder for the OS and external components. The exact OS reservation depends on system size; available physical memory below 1-2GB on a dedicated host is concerning and risks working-set trimming. Ensure Lock Pages in Memory (LPIM) is granted on Windows to prevent the OS from paging SQL Server’s working set. Without LPIM, OS memory pressure can page out the working set, which is catastrophic for performance and largely invisible in SQL Server metrics.

If physical RAM is genuinely insufficient for the working set, adding memory is the only durable fix. Track database growth versus buffer pool size to estimate runway.

Memory pressure spiral (bad plan or grant hoarding)

If sys.dm_exec_query_memory_grants shows one query holding a large grant, kill the session after assessment. The rollback may take as long as the original transaction. Then identify the plan: use Query Store (SQL 2016+) to compare plans and force a known-good one, or clear the specific plan handle with DBCC FREEPROCCACHE(<plan_handle>) . Long-term, address the cardinality estimation error through statistics updates, parameter sniffing fixes, or missing indexes.

OS-level or external memory pressure

Identify the competing process. Common culprits: antivirus scanning database files (exclude SQL directories, per Microsoft guidance), backup agents, co-located applications, and VM balloon drivers. If max server memory is set too high and the OS is under pressure, lower it. On Linux, check the OOM score for sqlservr and adjust mssql-conf settings to protect the process.

Scan-heavy workload on OLTP

If a reporting query or ad-hoc scan is evicting hot OLTP pages, isolate the workload. Options: Resource Governor (Enterprise edition), a readable AG secondary for reporting, off-peak scheduling, or query and index tuning to reduce the scan footprint. A covering index that turns a table scan into an index seek can eliminate the eviction pressure entirely.

TempDB-driven skew

If TempDB internal objects (sort/hash spills) or version store are pulling BCHR down, address the root cause: fix memory grants so queries do not spill, shorten long-running transactions under RCSI so the version store does not grow, and configure TempDB file count appropriately. Microsoft guidance starts with 1 data file per logical core up to 8, then adjust in multiples of 4 if PAGELATCH contention persists.

Storage latency

If PLE is stable but I/O stall is high, the storage subsystem is the bottleneck, not memory. Adding memory will not help. Check for SAN latency, disk failure, RAID rebuild, VM storage throttling (IOPS or throughput caps on cloud disks), or misplaced files with log and data on the same spindle.

Prevention

• Trend BCHR and PLE together. A single sample of either is noise. Track both as time series with at least per-minute granularity. Watch for sustained trends, not single dips.
• Establish a workload-type-aware baseline. Do not apply OLTP thresholds to a data warehouse. Classify each database and set thresholds accordingly.
• Pre-size database and log files. Auto-growth events pause I/O to the affected file. Log file auto-growth is especially expensive because Instant File Initialization does not apply to log files.
• Monitor max server memory against physical RAM. Re-evaluate after hardware changes, OS updates, or adding co-located services.
• Track memory grants pending proactively. It should be zero. Any sustained nonzero value is a leading indicator of the spiral.
• Schedule maintenance windows with monitoring awareness. DBCC CHECKDB, index rebuilds, and statistics updates legitimately drive BCHR down and PAGEIOLATCH up. Suppress or de-prioritize alerts during these windows rather than disabling monitoring.
• Watch per-NUMA-node PLE on multi-NUMA systems. The instance-wide average hides single-node starvation.

How Netdata helps

• Per-second BCHR and PLE collection catches the difference between a single dip and a sustained decline, which is exactly the distinction that prevents both over- and under-reaction.
• Correlated memory pressure view. Netdata surfaces BCHR, PLE (instance and per NUMA node where available), PAGEIOLATCH_* waits, lazy writer pages/sec, and memory grants pending in the same time window, so you can confirm whether a low BCHR is real pressure or a sampling artifact without bouncing between DMV queries.
• Wait statistics as a time series. Snapshotting sys.dm_os_wait_stats deltas at per-second granularity turns the cumulative DMV into a current-behavior signal, which is how wait stats are meant to be used.
• I/O stall per file alongside BCHR. When BCHR drops, seeing read latency on data files in the same view tells you immediately whether you have a memory problem, a storage problem, or both.
• ML anomaly detection on workload baselines. Batch requests/sec, transactions/sec, and BCHR all have time-of-day and day-of-week patterns. Anomaly detection flags deviations from the baseline rather than relying on fixed thresholds that do not fit both OLTP and data warehouse workloads.

Netdata’s Microsoft SQL Server monitoring brings these signals together with per-second metrics and ML anomaly detection.

Related guides

• SQL Server user connections climbing: connection pool leaks and retry storms
• SQL Server CPU utilization high: telling query load apart from a bad plan
• SQL Server CXPACKET and CXCONSUMER waits: parallelism, MAXDOP, and what is actually wrong
• SQL Server Error 9002: the transaction log for the database is full
• SQL Server high compilations per second: plan cache pollution and CPU burn
• How Microsoft SQL Server actually works in production: a mental model for operators
• SQL Server log autogrow stall: why every write pauses while the log file grows
• SQL Server log backups missing: the full-recovery log that grows forever
• SQL Server log_reuse_wait_desc: why the transaction log will not truncate
• SQL Server transaction log percent used climbing toward full
• SQL Server max server memory: setting it so the OS and buffer pool both survive
• SQL Server Memory Grants Pending above zero: queries queued before they can run