sys.dm_os_sys_memory - Usage & Examples

SQL Server's Memory Manager continuously monitors system memory through the Host Extension APIs, which populate sys.dm_os_sys_memory with real-time OS memory statistics. The SQL Server process queries these values every few seconds to make buffer pool sizing decisions and determine when to apply memory pressure to internal caches.

The total_physical_memory_kb column reflects the total RAM detected by the operating system at startup, while available_physical_memory_kb shows memory immediately available for allocation. When available memory drops below 10% of total physical memory, SQL Server's Resource Monitor begins aggressive cache trimming and may pause new query compilation.

The system_cache_kb column represents the OS file system cache, which competes with SQL Server's buffer pool for physical memory. On systems without proper max server memory configuration, this competition creates memory pressure oscillations where SQL Server releases memory to the OS cache, then immediately needs to reclaim it.

SQL Server 2016 introduced significant changes to memory management with the addition of query memory grants and columnstore segment caches being tracked separately. SQL Server 2019 added hybrid buffer pool support, which affects how this DMV reports available memory when persistent memory devices are configured. SQL Server 2022 enhanced the memory broker algorithms that use these values for more granular cache management decisions.

The system_memory_state_desc column indicates Windows memory resource notification state with descriptive text such as 'Available physical memory is high', 'Physical memory state is steady', or 'Available physical memory is low'. When this column reports a low-memory state, Windows has signaled global memory pressure, causing SQL Server to immediately begin releasing non-essential cached data regardless of its own memory pressure calculations.

-- Current system memory state and pressure indicators
SELECT 
    total_physical_memory_kb / 1024 AS total_physical_memory_mb,
    available_physical_memory_kb / 1024 AS available_physical_memory_mb,
    (available_physical_memory_kb * 100.0) / total_physical_memory_kb AS available_memory_pct,
    system_cache_kb / 1024 AS system_cache_mb,
    system_memory_state_desc
FROM sys.dm_os_sys_memory;

-- Memory pressure timeline analysis comparing system and SQL Server memory
SELECT 
    GETDATE() AS sample_time,
    osm.available_physical_memory_kb / 1024 AS available_physical_mb,
    osm.system_cache_kb / 1024 AS system_cache_mb,
    osm.system_memory_state_desc,
    pi.physical_memory_in_use_kb / 1024 AS sql_memory_in_use_mb,
    pi.available_commit_limit_kb / 1024 AS commit_limit_mb
FROM sys.dm_os_sys_memory osm
CROSS JOIN sys.dm_os_process_memory pi;

-- Historical memory pressure events using ring buffers
SELECT 
    DATEADD(ms, -1 * (inf.ms_ticks - rb.timestamp), GETDATE()) AS event_time,
    rb.timestamp,
    rb.record.value('(./Record/ResourceMonitor/Notification)[1]', 'varchar(20)') AS notification_type,
    rb.record.value('(./Record/ResourceMonitor/IndicatorsProcess)[1]', 'int') AS process_indicators,
    rb.record.value('(./Record/ResourceMonitor/IndicatorsSystem)[1]', 'int') AS system_indicators,
    rb.record.value('(./Record/MemoryNode/AvailableMemory)[1]', 'bigint') AS available_memory_kb
FROM sys.dm_os_ring_buffers rb
CROSS JOIN (SELECT ms_ticks FROM sys.dm_os_sys_info) inf
WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
ORDER BY rb.timestamp DESC;

-- Memory configuration validation against system capacity
SELECT 
    osm.total_physical_memory_kb / 1024 AS total_system_memory_mb,
    cfg.value_in_use * 1024 AS max_server_memory_mb,
    (cfg.value_in_use * 1024 * 100.0) / (osm.total_physical_memory_kb / 1024) AS sql_memory_pct,
    CASE 
        WHEN cfg.value_in_use = 2147483647 THEN 'UNLIMITED - HIGH RISK'
        WHEN (cfg.value_in_use * 1024 * 100.0) / (osm.total_physical_memory_kb / 1024) > 80 THEN 'OVER 80% - MEDIUM RISK'
        ELSE 'CONFIGURED PROPERLY'
    END AS memory_config_status
FROM sys.dm_os_sys_memory osm
CROSS JOIN (SELECT value_in_use FROM sys.configurations WHERE name = 'max server memory (MB)') cfg;

Immediate Memory Pressure Relief

-- Force SQL Server to release non-essential cached memory immediately
-- WARNING: This will clear plan cache and temporary caches, causing performance impact
DBCC FREESYSTEMCACHE('ALL') WITH MARK_IN_USE_FOR_REMOVAL;
DBCC FREESESSIONCACHE;
DBCC FREEPROCCACHE;

-- Shrink buffer pool if extreme memory pressure exists
-- Only use when available memory is critically low (<5%)
CHECKPOINT;
DBCC DROPCLEANBUFFERS;

Configure Appropriate Max Server Memory

-- Calculate and set max server memory based on system capacity
-- Reserves memory for OS, other applications, and SQL Server non-buffer pool usage
DECLARE @total_memory_mb INT, @recommended_max_mb INT;

SELECT @total_memory_mb = total_physical_memory_kb / 1024 
FROM sys.dm_os_sys_memory;

SET @recommended_max_mb = CASE 
    WHEN @total_memory_mb <= 2048 THEN @total_memory_mb - 512
    WHEN @total_memory_mb <= 4096 THEN @total_memory_mb - 1024  
    WHEN @total_memory_mb <= 8192 THEN @total_memory_mb - 2048
    WHEN @total_memory_mb <= 16384 THEN @total_memory_mb - 3072
    ELSE @total_memory_mb - 4096
END;

EXEC sp_configure 'max server memory (MB)', @recommended_max_mb;
RECONFIGURE;

Enable Lock Pages in Memory (Windows)

-- Check current Lock Pages in Memory privilege status
-- This must be configured at OS level through Local Security Policy
-- Prevents Windows from paging SQL Server's working set to disk

SELECT 
    locked_page_allocations_kb / 1024 AS locked_pages_mb,
    CASE 
        WHEN locked_page_allocations_kb > 0 THEN 'ENABLED - Good'
        ELSE 'DISABLED - Consider enabling for production servers >4GB'
    END AS lock_pages_status
FROM sys.dm_os_process_memory;

-- Note: Lock Pages privilege must be granted to SQL Server service account
-- through Windows Local Security Policy or Group Policy

Configure max server memory to leave 10-20% of physical memory available for the operating system and other processes. On dedicated SQL Server instances with 8GB+ RAM, reserve minimum 2-4GB for OS operations. Monitor sys.dm_os_sys_memory.available_physical_memory_kb to ensure it rarely drops below 500MB.

Enable Lock Pages in Memory privilege for the SQL Server service account on production systems with 4GB+ RAM. This prevents Windows from paging SQL Server's buffer pool to disk during memory pressure, maintaining consistent performance. Validate the privilege is active by checking sys.dm_os_process_memory.locked_page_allocations_kb shows non-zero values.

Implement automated monitoring that alerts when available_physical_memory_kb drops below 10% of total physical memory for more than 5 minutes. Set up secondary alerts when system_memory_state_desc reports a low-memory state, indicating Windows-level memory exhaustion requiring immediate intervention.

Avoid co-locating memory-intensive applications with SQL Server unless absolutely necessary. Applications like reporting services, web servers, or backup software can create unpredictable memory pressure patterns that force SQL Server into defensive memory management, degrading performance.

Configure Windows page file size to 1.5x physical RAM minimum, placed on fast storage separate from SQL Server data files. Although SQL Server avoids paging through Lock Pages in Memory, other system processes require adequate virtual memory to prevent system-wide memory allocation failures.