SQL Server memory pressure spiral: PLE collapse, physical reads, and I/O saturation

The signature of a memory pressure spiral is a rapid Page Life Expectancy (PLE) drop combined with rising PAGEIOLATCH_* waits and increasing I/O stall on data files. CPU sits low to moderate because the bottleneck is not compute. It is the buffer pool being drained, which forces physical reads, which saturate the storage path, which makes every query slower, which piles up more concurrent sessions, which pressures memory further.

Once the spiral starts, it accelerates until either the trigger is removed or the instance becomes effectively unresponsive. Catching it early requires reading several signals together, not any single counter in isolation.

The critical triage question is whether the I/O saturation is a cause or a symptom. If PLE is stable but I/O stalls are high, the storage subsystem is the problem. If PLE is dropping while I/O stalls are rising, you are inside the memory pressure spiral and the storage layer is downstream of buffer pool pressure.

What this means

SQL Server’s buffer pool caches 8KB data pages in memory. When a query needs a page that is not cached, it triggers a physical read. Under healthy operation, the lazy writer evicts cold pages and keeps free space available.

When something consumes buffer pool memory, the lazy writer evicts pages more aggressively. Queries that previously hit the cache now require physical reads, saturating the storage path and piling up concurrent sessions until the system thrashes.

flowchart TD
    A[Trigger: grant, OS pressure, XTP] --> B[Buffer pool shrinks]
    B --> C[Lazy writer evicts pages]
    C --> D[Cache hits become physical reads]
    D --> E[I/O subsystem saturates]
    E --> F[Query latency rises]
    F --> G[More sessions pile up]
    G --> B
    F --> H[Worker thread pressure]
    H --> I[Instance unresponsive]

PLE measures how long a page survives in the buffer pool before eviction. A drop is a lagging indicator, but it is one of the cleanest signals that the buffer pool is being drained. Buffer cache hit ratio declining alongside PLE confirms the diagnosis.

Common causes

Quick checks

Run these read-only checks first. None modify instance state.

-- OS-level 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;

-- SQL Server process memory pressure flags
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;

-- Top memory clerks by size
SELECT TOP 10
    type, name,
    pages_kb / 1024 AS size_mb
FROM sys.dm_os_memory_clerks
WHERE pages_kb > 0
ORDER BY pages_kb DESC;

-- Queries holding or waiting on memory grants
SELECT
    session_id, request_time, grant_time,
    requested_memory_kb, granted_memory_kb, used_memory_kb,
    wait_time_ms, dop, query_cost
FROM sys.dm_exec_query_memory_grants
ORDER BY wait_time_ms DESC;

-- PLE per NUMA node and instance-wide
SELECT instance_name, cntr_value AS ple_seconds
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'
  AND object_name LIKE '%Buffer Node%';

SELECT cntr_value AS ple_seconds_instance
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'
  AND object_name LIKE '%Buffer Manager%';

-- Top waits excluding benign idle waits
SELECT TOP 15
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    signal_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;

-- I/O stall per file (cumulative, compute deltas over your window)
SELECT
    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,
    vfs.io_stall_write_ms,
    vfs.num_of_writes,
    CASE WHEN vfs.num_of_writes > 0
         THEN vfs.io_stall_write_ms * 1.0 / vfs.num_of_writes
         ELSE 0 END AS avg_write_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 + vfs.io_stall_write_ms DESC;

-- Memory Grants Pending counter
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%';

-- Buffer cache hit ratio
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
WHERE a.counter_name = 'Buffer cache hit ratio'
  AND b.counter_name = 'Buffer cache hit ratio base';

How to diagnose it

1. Confirm the spiral pattern. Verify PLE is dropping while PAGEIOLATCH_SH and PAGEIOLATCH_EX waits are rising in your recent wait-stats delta. If PLE is stable, you are looking at a storage problem, not a memory pressure spiral.
2. Check OS-level pressure first. Query sys.dm_os_sys_memory. If system_memory_state_desc reports low memory, or process_physical_memory_low = 1 in sys.dm_os_process_memory, the OS is trimming SQL Server’s working set. Look for co-tenant processes, VM balloon activity, or max server memory set too high.
3. Identify memory grant hoarding. Query sys.dm_exec_query_memory_grants. A single session with a large granted_memory_kb relative to the others is a strong candidate. If wait_time_ms is nonzero on other sessions, they are queued on RESOURCE_SEMAPHORE.
4. Inspect memory clerks. Query sys.dm_os_memory_clerks sorted by pages_kb. The expected dominant consumer is MEMORYCLERK_SQLBUFFERPOOL. Large CACHESTORE_SQLCP indicates plan cache pollution from ad-hoc queries. Large MEMORYCLERK_XTP indicates In-Memory OLTP growth. Large OBJECTSTORE_LOCK_MANAGER indicates queries acquiring many locks.
5. Check per-NUMA-node PLE. On multi-NUMA systems, instance-wide PLE can hide one starved node. Compare per-node PLE values. An imbalance indicates foreign memory access patterns.
6. Look for the trigger. Cross-reference with active requests, scheduled jobs, and recent deployments. A new report query, a parameter sniffing regression with an oversized memory grant, or an ETL window opening are common triggers.

Metrics and signals to monitor

Fixes

Massive memory grant from a single query

If sys.dm_exec_query_memory_grants shows one session holding a disproportionate grant, assess whether KILL is safe. Warning: KILL triggers a rollback proportional to the work already done, which can temporarily increase lock and resource pressure. If the session has been running for hours on a large transaction, the rollback itself may worsen the spiral.

Longer term, identify the query. A bad plan with an oversized sort or hash is the usual culprit. Apply MIN_GRANT_PERCENT or MAX_GRANT_PERCENT query hints, fix statistics, or force a better plan via Query Store. Memory grant feedback (batch mode from SQL Server 2017, row mode from 2019, both on by default) corrects repeated offenders automatically once plans recompile.

External OS memory pressure

If process_physical_memory_low = 1, the OS is reclaiming memory from SQL Server. Identify the competing process. Common offenders include backup agents, antivirus scanning data files, co-tenant workloads on a shared VM host, and VM balloon drivers.

Verify max server memory leaves the OS enough headroom. On Windows, enable Lock Pages in Memory (LPIM) for the SQL Server service account to prevent working-set trimming, and pair LPIM with an explicit max server memory value so SQL Server cannot starve the OS. On Linux, set an explicit memory limit via mssql-conf set memory.memorylimitmb (default 80% of physical RAM) and lower the OOM priority of sqlservr so the kernel does not pick it first under pressure, e.g. with OOMScoreAdjust= (a negative value) in the mssql-server systemd unit.

VM balloon driver reclaiming memory

Hypervisor memory overcommitment can silently reclaim SQL Server memory. The SQL Server error log may report error 17890 (“A significant part of sql server process memory has been paged out”). Check hypervisor-side metrics for balloon driver activity. Reserve memory for the SQL Server VM or disable ballooning against it.

In-Memory OLTP growth

Memory-optimized tables consume memory outside the normal buffer pool, tracked under MEMORYCLERK_XTP; sys.dm_db_xtp_memory_consumers breaks that memory down per consumer (no Microsoft-published threshold exists). If XTP memory is growing without bound, inspect the memory-optimized table sizes and the garbage collection backlog. Bound XTP memory with resource pools if your workload allows.

Background job pressure

If index rebuilds, DBCC CHECKDB, or large ETL loads coincide with the spiral, schedule them outside the OLTP peak window. Consider MAXDOP limits for maintenance operations and online rebuilds where edition allows. DBCC CHECKDB creates an internal database snapshot and consumes TempDB, which can compound the pressure.

Plan cache pollution

If CACHESTORE_SQLCP is unusually large, ad-hoc queries without parameterization are filling the plan cache and stealing buffer pool memory. Enable optimize for ad hoc workloads to store only stubs on first execution. Parameterize application queries or evaluate forced parameterization, with awareness that forced parameterization can introduce parameter sniffing issues.

Prevention

• Set max server memory explicitly to leave the OS at least 2 to 4 GB plus headroom for any co-tenant workloads. The default is effectively unlimited, which lets SQL Server consume everything.
• Enable LPIM on Windows for the SQL Server service account, paired with an explicit max server memory value.
• Track PLE as a trend, not a threshold. A community heuristic is PLE should be at least (buffer_pool_size_GB / 4) * 300 seconds, but the trend matters more than the absolute value. Sudden 50%+ drops from baseline are the warning sign.
• Snapshot wait statistics every 30 to 60 seconds and compute deltas. Cumulative sys.dm_os_wait_stats over the entire uptime is useless for diagnosing current issues.
• Watch Memory Grants Pending as a leading indicator. It should be zero. Any sustained nonzero value is investigate-now, not wait-and-see.
• Pre-size database and log files to avoid autogrow stalls, which compound I/O pressure during a spiral.
• Schedule maintenance windows so index rebuilds and DBCC CHECKDB do not compete with OLTP peaks.

How Netdata helps

Netdata collects the signals that confirm or rule out the spiral pattern at per-second resolution.

• PLE, buffer cache hit ratio, and Memory Grants Pending are collected per second, so a drop or spike is visible immediately rather than on a multi-minute polling interval.
• PAGEIOLATCH_* and RESOURCE_SEMAPHORE wait statistics are tracked with automatic delta computation, making dominant wait shifts obvious without manual DMV snapshots.
• Per-file I/O latency from sys.dm_io_virtual_file_stats correlates directly with wait stats, distinguishing buffer pool pressure from a pure storage fault.
• Anomaly detection on PLE, hit ratio, and physical reads flags deviations from the per-instance baseline automatically, rather than relying on static thresholds that break across workload types and instance sizes.
• Correlated dashboards let you see the OS memory state, SQL Server internal memory clerks, and wait stats in one view, shortening the path from symptom to cause.

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
• Microsoft SQL Server monitoring checklist: the signals every production instance needs
• Microsoft SQL Server monitoring maturity model: from survival to expert