/sqlmemory-review

name: sqlmemory-review
description: Analyze SQL Server memory pressure using buffer pool metrics, plan cache composition, memory grants, and memory clerk data. Applies 20 checks (O1–O20) covering Page Life Expectancy degradation, single-use plan cache bloat, RESOURCE_SEMAPHORE queue depth, memory grant timeouts, buffer pool concentration, ColumnStore and In-Memory OLTP footprint, OS memory pressure notifications, and server memory configuration. Use this skill when the server is paging, queries queue for memory grants, or PLE is low and dropping. Trigger when pasting output from sys.dm_os_memory_clerks, sys.dm_os_ring_buffers, sys.dm_exec_query_memory_grants, or PLE perf counters.
triggers:
  - /sqlmemory-review
  - /memory-review
  - /ple-check

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;

---

Thresholds Reference

| Metric | Info | Warning | Critical | |--------|------|---------|----------| | PLE (single NUMA node) | ≥ scaled floor `(GB/4)×300` | < scaled floor | < 25% of floor (or < 60 s) | | PLE (multi-NUMA, per node) | ≥ scaled floor `(GB/4)×300` | < scaled floor | < 25% of floor (or < 60 s) | | PLE decline rate (trend) | < 10 s/min | ≥ 10 s/min | ≥ 60 s/min | | Single-use plan cache as % of total plan cache | < 30% | ≥ 30% | ≥ 60% | | RESOURCE_SEMAPHORE wait (from sqlwait-review) | 0 sessions | 1–5 queued | > 5 queued | | Memory grant timeout | 0 | Any | — | | Stolen memory (non-buffer) as % of target | < 15% | ≥ 15% | ≥ 30% | | Buffer pool: one DB as % of pool | < 60% | ≥ 60% | ≥ 80% | | ColumnStore pool (COLUMNSTORE_OBJECT_POOL) | — | > 25% | > 50% | | In-Memory OLTP (XTP) memory | — | > 25% | > 50% |

> **Threshold provenance:** O1 (PLE, scaled by buffer pool) and O10 (hit-ratio direction) are grounded in Microsoft Learn. The remaining cutoffs — **O2–O9, O11, and O13** (single-use plan %, RESOURCE_SEMAPHORE queue depth, stolen-memory %, buffer-pool concentration, ColumnStore/XTP pool %, grant timeout/decline rates) — are **reasonable operational heuristics, not Microsoft-documented values.** Treat them as starting points and calibrate to your workload's baseline; a value crossing a line is a prompt to investigate, not proof of a problem.

---

Buffer Pool and PLE Checks (O1–O5)

Run these first to determine if SQL Server is under immediate memory pressure.

O1 — Low Page Life Expectancy

• **Trigger:** `Page life expectancy` `cntr_value` in `sys.dm_os_performance_counters` (object `Buffer Manager`, or per-node `Buffer Node`) below a **buffer-pool-scaled** floor — roughly `(buffer pool GB / 4) × 300` seconds (so ~9,600 s on a 128 GB pool), **or** a sudden/sustained dip (see O2). Microsoft does **not** endorse a fixed value: per MS Learn, "a higher, growing value is best; a sudden dip indicates a significant chur