How to Check SQL Server Memory Pressure and Usage using DMVs

Summary: Use sys.dm_os_process_memory and sys.dm_os_sys_memory to monitor SQL Server memory health, detect low memory conditions, and audit physical vs. virtual memory usage.

Monitoring SQL Server Memory Health and Performance

When working with SQL Server, monitoring memory is crucial for optimal performance. These queries provide valuable insights into your system's memory state, helping you identify and address potential memory pressure issues before they impact your users.

1. Process Memory Low Conditions

The following query helps determine if your SQL Server process is experiencing low physical or virtual memory. Ideally, both values should be 0.


-- Check for low memory flags (0 = Normal, 1 = Low)
SELECT 
    m.process_physical_memory_low,
    m.process_virtual_memory_low
FROM sys.dm_os_process_memory m;

2. Current Instance Memory Usage

This query displays the amount of physical memory (RAM) currently consumed by your SQL Server instance.


-- Total memory currently used by the SQL process
SELECT 
    m.physical_memory_in_use_kb / 1024 AS [Mem Used in MB]
FROM sys.dm_os_process_memory m;

3. Overall System Memory Status

Gain a comprehensive overview of the host's memory health. The system_memory_state_desc is the most important field for a quick health check.


-- System-level memory overview
SELECT 
    m.total_physical_memory_kb / 1024 AS [Total Mem MB],
    m.available_physical_memory_kb / 1024 AS [Available Mem MB],
    m.system_memory_state_desc AS [Memory State]
FROM sys.dm_os_sys_memory m;

4. OS Memory & Page File Details

For deep troubleshooting, monitor the page file and system cache. For optimal performance, the state should be "Available physical memory is high."


-- Detailed OS memory audit
SELECT 
    total_physical_memory_kb/1024 AS [Total Memory (MB)],
    available_physical_memory_kb/1024 AS [Available Memory (MB)],
    total_page_file_kb/1024 AS [Total Page File (MB)],
    available_page_file_kb/1024 AS [Available Page File (MB)],
    system_cache_kb/1024 AS [System Cache (MB)],
    system_memory_state_desc AS [System Memory State]
FROM sys.dm_os_sys_memory WITH (NOLOCK) 
OPTION (RECOMPILE);

Understanding Memory State Descriptions:

Available physical memory is high: System health is excellent.
Physical memory usage is steady: Standard operational state.
Available physical memory is low: The OS is under pressure; check for other processes hogging RAM.
Available physical memory is running low: Critical state; SQL Server may start trimming its working set.

SQL Server help

Saturday, May 29, 2021