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.

---

Further Reading and Resources:

• Official Docs: sys.dm_os_sys_memory
• How to Get SQL Server Memory Dumps
• Analyzing Memory Clerk Usage
• Understanding Memory Grants Pending

Experiencing high Page Life Expectancy (PLE) drops? Use these scripts to verify if the OS is forcing SQL Server to release memory back to the system!

How to Identify SQL Server Index Lock Contention and Wait Times

Summary: Use sys.dm_db_index_operational_stats to identify tables and indexes with high row and page lock contention, helping you pinpoint performance bottlenecks in your SQL Server databases.

Identifying Database Lock Contention and Index Wait Times

Hey there, fellow database enthusiasts! Ever wondered about those pesky database locks that can slow things down? Analyzing lock waits is a crucial step in maintaining a healthy and performant SQL Server environment.

Today, we're sharing a super useful SQL query to help you identify and analyze lock waits. This script is a fantastic tool for pinpointing specific tables and indexes experiencing contention, allowing you to focus your optimization efforts where they matter most.

SQL Query: Analyze Row and Page Lock Waits

This query calculates cumulative wait counts and durations for both row-level and page-level locks since the last SQL Server restart (or since the index was created).

-- Identify indexes with significant lock contention
SELECT 
    o.name AS [table_name],
    i.name AS [index_name],
    ios.index_id,
    ios.partition_number,
    SUM(ios.row_lock_wait_count) AS [total_row_lock_waits],
    SUM(ios.row_lock_wait_in_ms) AS [total_row_lock_wait_in_ms],
    SUM(ios.page_lock_wait_count) AS [total_page_lock_waits],
    SUM(ios.page_lock_wait_in_ms) AS [total_page_lock_wait_in_ms],
    SUM(ios.page_lock_wait_in_ms) + SUM(ios.row_lock_wait_in_ms) AS [total_lock_wait_in_ms]
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS ios
INNER JOIN sys.objects AS o WITH (NOLOCK) ON ios.[object_id] = o.[object_id]
INNER JOIN sys.indexes AS i WITH (NOLOCK) ON ios.[object_id] = i.[object_id]
    AND ios.index_id = i.index_id
WHERE o.[object_id] > 100 -- Filter out system objects
GROUP BY o.name, i.name, ios.index_id, ios.partition_number
HAVING SUM(ios.page_lock_wait_in_ms) + SUM(ios.row_lock_wait_in_ms) > 0
ORDER BY total_lock_wait_in_ms DESC
OPTION (RECOMPILE);
        

How to Interpret the Results

This query leverages sys.dm_db_index_operational_stats, a dynamic management function that provides detailed information about index activity.

• Total Lock Wait (ms): The primary metric for identifying the biggest bottlenecks. Focus on the top rows first.
• Row vs. Page Locks: High page lock waits often suggest that multiple rows on the same 8KB page are being contested, which might be mitigated by evaluating your index fill factor or page-locking settings.
• Index Name: If you see high waits on a specific non-clustered index, consider if that index is strictly necessary or if query patterns are causing frequent updates that block reads.

---

Seeing high contention? Once you've identified the offending index, look at the underlying queries using sys.dm_exec_requests to see what is currently blocking your workload!

How to Audit SQL Server Columnstore Index Physical Stats and Fragmentation

Summary: Use sys.dm_db_column_store_row_group_physical_stats to audit the health of your Columnstore indexes, monitor delta store growth, and calculate row-group fragmentation.

Analyzing Columnstore Index Physical Health

Ever wondered how to peek behind the curtains of your SQL Server's Columnstore indexes? Unlike traditional B-Tree indexes, Columnstore indexes are composed of row groups that can be in various states (Open, Closed, or Compressed).

This handy query gives you a detailed look at their physical stats, helping you understand their internal health and identify when it's time for an index reorganize or rebuild.

Columnstore Row Group Physical Stats Query

-- Audit Columnstore row group health and fragmentation
SELECT 
    OBJECT_NAME(ps.object_id) AS [TableName],
    i.[name] AS [IndexName],
    ps.index_id,
    ps.partition_number,
    ps.delta_store_hobt_id,
    ps.state_desc,
    ps.total_rows,
    ps.size_in_bytes,
    ps.trim_reason_desc,
    ps.generation,
    ps.transition_to_compressed_state_desc,
    ps.has_vertipaq_optimization,
    ps.deleted_rows,
    CAST(100.0 * (ISNULL(ps.deleted_rows, 0)) / NULLIF(ps.total_rows, 0) AS DECIMAL(5,2)) AS [Fragmentation %]
FROM sys.dm_db_column_store_row_group_physical_stats AS ps WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK) 
    ON ps.object_id = i.object_id
    AND ps.index_id = i.index_id
ORDER BY [TableName], ps.partition_number, ps.row_group_id
OPTION (RECOMPILE);
        

Key Metrics to Monitor

• state_desc: Look for OPEN row groups. If you have too many small open row groups, it can indicate "trickle inserts" which hurt compression and performance.
• Fragmentation %: In Columnstore, fragmentation is caused by deleted_rows. Since rows aren't physically removed from compressed segments immediately, high fragmentation means you are reading "dead" data into memory.
• trim_reason_desc: This tells you why a row group was closed (e.g., BULKLOAD or MEMORY_LIMITATION). If you see MEMORY_LIMITATION frequently, your server may need more RAM to build optimal segments.

This SQL script dives into sys.dm_db_column_store_row_group_physical_stats to provide deep insights. It's an essential tool for any DBA managing large-scale data warehouses or real-time operational analytics.

---

Is your Fragmentation above 20%? Run ALTER INDEX ... REORGANIZE to physically remove deleted rows and merge smaller row groups for better compression!