Memory Pressure Triage

performance critical

#memory#buffer-pool#page-life-expectancy#performance

Memory pressure degrades SQL Server performance by forcing pages out of the buffer cache, increasing physical I/O, and blocking query compilation. Early detection using DMV-based threshold monitoring is critical.

Overview

Memory pressure manifests in three tiers:

Buffer pool pressure — Low page life expectancy (PLE), high PAGEIOLATCH waits
Memory grant pressure — Queries waiting for query execution memory grants (RESOURCE_SEMAPHORE)
Cache pressure — Plan cache eviction causing frequent recompilation (RESOURCE_SEMAPHORE_QUERY_COMPILE)

Triage Steps

1. Measure buffer pool health

Query sys.dm_os_performance_counters for Page life expectancy (target: > 300 seconds per 4 GB of buffer pool). Low PLE combined with high PAGEIOLATCH_SH waits indicates insufficient memory for working set.

2. Check process memory state

sys.dm_os_process_memory reveals physical memory in use, reserved pages, and page faults. A high page_fault_count growth rate signals memory pressure.

3. Analyze memory clerk usage

sys.dm_os_memory_clerks breaks down memory usage by component (buffer pool, plan cache, object store). Sort by pages_kb descending to identify top consumers. The MEMORYCLERK_SQLBUFFERPOOL entry should dominate in a well-tuned system.

4. Check memory grants

RESOURCE_SEMAPHORE waits indicate queries waiting for query execution memory grants. High waits suggest memory grant sizing issues or insufficient max server memory. RESOURCE_SEMAPHORE_QUERY_COMPILE indicates compile blocking due to concurrent compilation memory pressure.

5. Evaluate page I/O

Elevated PAGEIOLATCH_SH waits combined with low PLE suggest the buffer cache is too small for the working set, forcing physical reads on each access.

Key Scripts

Check how much memory SQL Server is consuming — Start here for baseline memory usage
Check memory usage of each memory clerk — Identify which component is consuming memory
Check page life expectancy — Buffer pool health metric
Analyze buffer pool — Deep-dive buffer pool analysis