Memory Pressure - Diagnosis & Resolution

SQL Server's buffer pool manager operates under constant memory pressure evaluation through the Resource Monitor component. When available physical memory drops below critical thresholds, the buffer pool begins aggressive page eviction using a clock algorithm that targets the least recently used pages. The Memory Manager coordinates with the Buffer Manager to shrink the buffer pool, reducing the data cache hit ratio and forcing more physical reads.

Internal memory pressure triggers several cascading effects. The Lazy Writer process activates more frequently to flush dirty pages, competing with user queries for I/O bandwidth. The buffer pool's free page list shrinks, causing new page requests to wait longer for available buffers. Plan cache entries get evicted to free memory, forcing query recompilation and consuming additional CPU cycles.

SQL Server 2012 introduced significant changes with columnstore indexes requiring large memory grants that can exhaust available memory faster than traditional row-based operations. SQL Server 2014 added Buffer Pool Extensions to SSD, providing an intermediate cache tier. SQL Server 2016's Query Store adds memory overhead but provides better visibility into memory-related performance degradation patterns.

The SQLOS scheduler relies on memory clerks to track memory usage across different components. When total server memory approaches max server memory, the Resource Semaphore begins throttling query execution by limiting concurrent memory grants. Large queries requiring substantial memory grants may queue indefinitely, creating apparent hang conditions while smaller queries continue executing.

Memory pressure manifests differently based on workload patterns. OLTP systems experience increased lock waits as buffer pool contention increases. OLAP workloads see dramatic performance degradation as large scans repeatedly read from disk. Mixed workloads create resource contention between small frequent queries and large analytical operations.

-- Buffer pool pressure and memory usage overview
SELECT 
    (physical_memory_kb/1024) AS physical_memory_mb,
    (virtual_memory_kb/1024) AS virtual_memory_mb,
    (committed_kb/1024) AS committed_memory_mb,
    (committed_target_kb/1024) AS committed_target_mb,
    CASE 
        WHEN committed_kb > committed_target_kb THEN 'Memory Pressure'
        ELSE 'Normal'
    END AS memory_status
FROM sys.dm_os_sys_info;

Shows current memory allocation versus targets, indicating if SQL Server is operating under memory constraints.

-- Buffer pool hit ratio and page life expectancy
-- Buffer cache hit ratio must be computed as ratio / base counter
SELECT 
    ratio.cntr_value AS buffer_cache_hit_ratio_raw,
    base.cntr_value  AS buffer_cache_hit_ratio_base,
    CASE WHEN base.cntr_value = 0 THEN NULL
         ELSE (1.0 * ratio.cntr_value / base.cntr_value) * 100.0
    END AS buffer_cache_hit_ratio_pct,
    ple.cntr_value AS page_life_expectancy_sec,
    CASE 
        WHEN ple.cntr_value < 300 THEN 'Critical'
        WHEN ple.cntr_value < 1000 THEN 'Warning'
        ELSE 'OK'
    END AS ple_status
FROM (SELECT cntr_value FROM sys.dm_os_performance_counters
      WHERE counter_name = 'Buffer cache hit ratio'
        AND object_name LIKE '%Buffer Manager%') ratio
CROSS JOIN (SELECT cntr_value FROM sys.dm_os_performance_counters
            WHERE counter_name = 'Buffer cache hit ratio base'
              AND object_name LIKE '%Buffer Manager%') base
CROSS JOIN (SELECT cntr_value FROM sys.dm_os_performance_counters
            WHERE counter_name = 'Page life expectancy'
              AND object_name LIKE '%Buffer Manager%') ple;

Reveals buffer pool efficiency metrics where PLE below 300 seconds indicates severe memory pressure.

-- Memory grants and query wait statistics
SELECT 
    wg.session_id,
    wg.request_id,
    wg.grant_time,
    wg.requested_memory_kb,
    wg.granted_memory_kb,
    wg.required_memory_kb,
    wg.used_memory_kb,
    wg.max_used_memory_kb,
    r.wait_type,
    r.wait_time,
    SUBSTRING(qt.text, r.statement_start_offset/2+1,
        CASE WHEN r.statement_end_offset = -1 
             THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2
             ELSE r.statement_end_offset
        END - r.statement_start_offset)/2 AS query_text
FROM sys.dm_exec_query_memory_grants wg
JOIN sys.dm_exec_requests r ON wg.session_id = r.session_id 
    AND wg.request_id = r.request_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS qt
ORDER BY wg.requested_memory_kb DESC;

Identifies queries consuming large memory grants and those waiting for memory allocation.

-- Memory pressure ring buffer events
-- Convert ring buffer timestamp (ms since system start) to actual datetime
DECLARE @ms_ticks BIGINT = (SELECT ms_ticks FROM sys.dm_os_sys_info);

SELECT 
    record.value('(./Record/@id)[1]', 'int') AS record_id,
    DATEADD(ms, -1 * (@ms_ticks - t.[timestamp]), GETDATE()) AS event_time,
    record.value('(./Record/ResourceMonitor/Notification)[1]', 'varchar(max)') AS notification_type,
    record.value('(./Record/MemoryRecord/MemoryUtilization)[1]', 'int') AS memory_utilization,
    record.value('(./Record/MemoryRecord/TotalPhysicalMemory)[1]', 'bigint') AS total_physical_memory,
    record.value('(./Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS available_physical_memory
FROM (SELECT [timestamp], 
             CAST(record AS xml) AS record
      FROM sys.dm_os_ring_buffers 
      WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR') AS t
WHERE record.value('(./Record/ResourceMonitor/Notification)[1]', 'varchar(max)') IS NOT NULL
ORDER BY event_time DESC;

Examines recent memory pressure notifications from the Resource Monitor.

-- Top memory consuming queries
SELECT TOP 20
    qs.sql_handle,
    qs.plan_handle,
    qs.total_worker_time/qs.execution_count AS avg_cpu_time,
    qs.max_grant_kb,
    qs.last_grant_kb,
    qs.total_grant_kb,
    qs.max_used_grant_kb,
    qs.last_used_grant_kb,
    qs.total_used_grant_kb,
    qs.execution_count,
    SUBSTRING(qt.text, qs.statement_start_offset/2+1,
        CASE WHEN qs.statement_end_offset = -1
             THEN DATALENGTH(qt.text)/2
             ELSE (qs.statement_end_offset - qs.statement_start_offset)/2
        END) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE qs.max_grant_kb > 0
ORDER BY qs.max_grant_kb DESC;

Identifies queries historically requiring the largest memory grants.

Adjust max server memory setting Reserves memory for the operating system and other applications by limiting SQL Server's memory consumption.

-- Calculate appropriate max server memory (leave 2-4GB for OS)
DECLARE @total_memory_gb INT = (SELECT physical_memory_kb/1024/1024 FROM sys.dm_os_sys_info);
DECLARE @recommended_max_gb INT = 
    CASE 
        WHEN @total_memory_gb <= 4 THEN @total_memory_gb - 1
        WHEN @total_memory_gb <= 16 THEN @total_memory_gb - 2  
        WHEN @total_memory_gb <= 32 THEN @total_memory_gb - 3
        ELSE @total_memory_gb - 4
    END;

-- Apply the setting
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', @recommended_max_gb * 1024;
RECONFIGURE;
PRINT 'Max server memory set to ' + CAST(@recommended_max_gb * 1024 AS VARCHAR(10)) + ' MB';

Warning: Test memory settings during maintenance windows. Incorrect values can cause service instability or startup failures.

Kill expensive memory-consuming queries Terminates sessions with excessive memory grants that may be causing resource starvation.

-- Identify and kill sessions with memory grants above threshold
DECLARE @memory_threshold_mb INT = 5000; -- Adjust threshold as needed

SELECT 
    s.session_id,
    mg.requested_memory_kb/1024 AS requested_memory_mb,
    mg.granted_memory_kb/1024 AS granted_memory_mb,
    'KILL ' + CAST(s.session_id AS VARCHAR(10)) AS kill_command
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_query_memory_grants mg ON s.session_id = mg.session_id
WHERE mg.granted_memory_kb/1024 > @memory_threshold_mb
    AND s.session_id > 50 -- Exclude system sessions
ORDER BY mg.granted_memory_kb DESC;

-- Uncomment to execute kills (USE WITH EXTREME CAUTION)
/*
DECLARE kill_cursor CURSOR FOR
    SELECT s.session_id
    FROM sys.dm_exec_sessions s
    JOIN sys.dm_exec_query_memory_grants mg ON s.session_id = mg.session_id
    WHERE mg.granted_memory_kb/1024 > @memory_threshold_mb
        AND s.session_id > 50;

DECLARE @session_id INT;
OPEN kill_cursor;
FETCH NEXT FROM kill_cursor INTO @session_id;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC('KILL ' + @session_id);
    FETCH NEXT FROM kill_cursor INTO @session_id;
END;
CLOSE kill_cursor;
DEALLOCATE kill_cursor;
*/

Critical Warning: Only execute kill commands after verifying the sessions are not critical business processes. Document killed sessions for application teams.

Force memory cleanup Manually triggers checkpoint and buffer pool cleanup to free memory immediately.

-- Perform manual checkpoint to flush dirty pages
CHECKPOINT;

-- Clear plan cache to free memory (impacts performance temporarily)
-- Only use during severe memory pressure emergencies
DBCC FREESYSTEMCACHE('ALL') WITH MARK_IN_USE_FOR_REMOVAL;

-- Force buffer pool cleanup (SQL Server 2016+)
DBCC DROPCLEANBUFFERS; -- DO NOT run in production unless absolutely necessary

PRINT 'Memory cleanup completed. Monitor system performance closely.';

Critical Warning: These commands will temporarily degrade performance as caches rebuild. Only use during critical memory pressure situations and inform application teams of potential performance impact.

Enable Resource Governor for memory control Implements workload isolation to prevent runaway queries from consuming all available memory.

-- Create resource pools with memory limits
CREATE RESOURCE POOL high_memory_pool
WITH (
    MIN_MEMORY_PERCENT = 20,
    MAX_MEMORY_PERCENT = 60,
    MIN_CPU_PERCENT = 0,
    MAX_CPU_PERCENT = 100
);

CREATE RESOURCE POOL limited_memory_pool  
WITH (
    MIN_MEMORY_PERCENT = 5,
    MAX_MEMORY_PERCENT = 30,
    MIN_CPU_PERCENT = 0,
    MAX_CPU_PERCENT = 50
);

-- Create workload groups
CREATE WORKLOAD GROUP priority_queries
USING high_memory_pool
WITH (
    REQUEST_MAX_MEMORY_GRANT_PERCENT = 25,
    REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 120,
    MAX_DOP = 4
);

CREATE WORKLOAD GROUP background_queries
USING limited_memory_pool
WITH (
    REQUEST_MAX_MEMORY_GRANT_PERCENT = 5,
    REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 60,
    MAX_DOP = 2
);

-- Apply configuration
ALTER RESOURCE GOVERNOR RECONFIGURE;

Warning: Resource Governor changes require testing workload classification functions. Monitor query performance after implementation to ensure proper workload routing.

Configure max server memory to leave adequate headroom for the operating system, typically 2-4 GB on servers with more than 8 GB RAM. Monitor Page Life Expectancy consistently, establishing baselines during normal operation periods. Values consistently below 300 seconds indicate chronic memory pressure requiring infrastructure scaling.

Implement proactive monitoring of buffer cache hit ratios, targeting 95%+ for OLTP workloads. Configure alerts when PLE drops below established thresholds or when free list stalls per second exceed 2-3. These metrics provide early warning before user-visible performance degradation occurs.

Review query memory grants regularly using DMV analysis to identify queries requesting excessive memory allocations. Implement Resource Governor policies to limit per-query memory consumption for non-critical workloads. Establish memory grant timeout values to prevent queries from indefinitely waiting for memory allocation.

Design application queries to minimize large memory grants by avoiding unnecessary sorting operations, reducing result set sizes through proper filtering, and implementing appropriate indexing strategies. Large hash joins and sorts consume disproportionate memory resources, particularly in analytical workloads.

Establish capacity planning processes that account for memory growth patterns. SQL Server's memory usage typically grows over time as buffer pools warm up and plan caches populate. Size servers with sufficient memory headroom to accommodate normal growth plus peak usage scenarios without entering pressure conditions.

Consider implementing Buffer Pool Extensions on servers with available SSD storage when memory upgrades are not feasible. This provides an intermediate caching tier that reduces I/O pressure during memory-constrained periods, though performance remains inferior to actual RAM.