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

CauseWhat it looks likeFirst thing to check
Massive memory grant hoardingOne session dominates sys.dm_exec_query_memory_grants with a large granted_memory_kbSELECT session_id, granted_memory_kb, requested_memory_kb FROM sys.dm_exec_query_memory_grants ORDER BY granted_memory_kb DESC
External OS pressureprocess_physical_memory_low = 1 in sys.dm_os_process_memorySELECT system_memory_state_desc, available_physical_memory_kb FROM sys.dm_os_sys_memory
VM balloon driver reclaiming memorySQL Server memory drops without an internal cause, error log 17890Check hypervisor memory overcommitment and balloon driver activity
In-Memory OLTP growthMEMORYCLERK_XTP is a top consumer in sys.dm_os_memory_clerksInspect memory-optimized table sizes and XTP memory consumers
Max server memory misconfiguredAvailable OS memory too low or buffer pool too small for the working setCompare max server memory to physical RAM and OS needs
Background job pressureIndex rebuild or DBCC CHECKDB running concurrently with OLTPCheck active requests for maintenance operations

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

SignalWhy it mattersWarning sign
PLE per NUMA nodeDirect measure of buffer pool churnSudden 50%+ drop from baseline, regardless of absolute value
Buffer cache hit ratioFraction of reads served from memoryOLTP sustained below 95%
PAGEIOLATCH_* wait timeTime spent waiting for disk reads to completeBecoming the dominant wait type
I/O stall per data fileEngine’s own view of storage latencyAverage read latency above 20ms sustained
Memory Grants PendingQueries queued waiting for a memory grantAny sustained nonzero value
RESOURCE_SEMAPHORE waitsAggregated memory grant wait timeGrowing queue with high wait time
system_memory_state_descOS-level memory stateReports “Available physical memory is low”
process_physical_memory_lowOS has notified SQL Server of pressureFlag is 1
Top memory clerksWhere memory is allocated inside the engineNon-buffer-pool clerk unusually large
Signal wait ratioCPU scheduler pressure from piled-up runnable threadssignal_wait_time_ms above 20% of total wait time

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 and review the OOM score for the mssql service so SQL Server is not the first process killed under pressure.

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. 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.