sqlmemory-review

SQL Server Memory Review Skill

Purpose

Analyze SQL Server memory state and identify the root cause of memory pressure. Applies 20 checks (O1–O20) across four categories:

• O1–O5 — Buffer pool and Page Life Expectancy: detect low PLE, declining trends, NUMA node imbalance, and buffer pool concentration in a single database
• O6–O10 — Plan cache: single-use plan bloat, excessive compile counts, large individual plans, and high plan cache churn
• O11–O15 — Memory grants and RESOURCE_SEMAPHORE: detect grant queuing, grant timeouts, oversized grants, and Resource Governor misconfigurations
• O16–O20 — Memory clerks, OS pressure, and configuration: ColumnStore/In-Memory OLTP memory footprint, OS pressure notifications, stolen (non-buffer) memory dominance, Lock Pages in Memory misconfiguration, and Max Server Memory not explicitly set

Input

Accept any of:

• Output from sys.dm_os_memory_clerks capture query below (paste the result grid)
• Output from sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_RESOURCE_MONITOR' — memory pressure notifications (also accept RING_BUFFER_OOM records for out-of-memory events)
• Output from sys.dm_exec_query_memory_grants for current grant queue state
• PLE counter values from sys.dm_os_performance_counters or SSMS Activity Monitor
• Output from sys.dm_os_sys_memory for OS-level memory state
• Combined paste of two or more of the above; apply all applicable checks
• A natural language description of symptoms ("PLE is 200 and dropping, RESOURCE_SEMAPHORE is 15% of waits")

Recommended capture queries

-- 1. Memory clerks — top consumers (paste top 20+ rows)
SELECT TOP 20
    type,
    name,
    memory_node_id,
    pages_kb,
    virtual_memory_reserved_kb,
    virtual_memory_committed_kb,
    awe_allocated_kb,
    shared_memory_reserved_kb,
    shared_memory_committed_kb
FROM sys.dm_os_memory_clerks
ORDER BY pages_kb DESC;

-- 2. Page Life Expectancy (PLE)
SELECT object_name, counter_name, instance_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Buffer Manager%'
  AND counter_name = 'Page life expectancy';

-- 3. Plan cache single-use waste
SELECT
    SUM(CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) / 1048576 AS single_use_mb,
    SUM(size_in_bytes) / 1048576                                            AS total_plan_cache_mb,
    COUNT(*)                                                                AS total_plans,
    SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END)                         AS single_use_plans
FROM sys.dm_exec_cached_plans
WHERE objtype IN ('Adhoc', 'Prepared');

-- 4. Memory grant queue (current requests waiting)
SELECT
    session_id,
    request_id,
    scheduler_id,
    grant_time,
    requested_memory_kb,
    granted_memory_kb,
    required_memory_kb,
    used_memory_kb,
    max_used_memory_kb,
    query_cost,
    timeout_sec,
    resource_semaphore_id,
    wait_order,
    is_next_candidate
FROM sys.dm_exec_query_memory_grants
ORDER BY wait_order;

-- 5. OS memory state
SELECT
    total_physical_memory_kb,
    available_physical_memory_kb,
    total_page_file_kb,
    available_page_file_kb,
    system_memory_state_desc
FROM sys.dm_os_sys_memory;