Queries Waiting on Memory Grants: Diagnosis and Performance Tuning

What is a Memory Grant?

A memory grant is SQL Server's way of allocating RAM to a query for work operations:

• Sorts: ORDER BY, GROUP BY, DISTINCT
• Hashes: Hash joins, hash aggregates
• Spills: When working set exceeds memory grant, query spills to TempDB

Before executing a query, the optimizer estimates how much memory it needs and requests a grant. If granted, the query executes. If not, the query waits in a queue (RESOURCE_SEMAPHORE wait).

Why Grants Matter

The goal: Keep queries executing efficiently in memory, not spilling to TempDB. A well-estimated grant is fast. An underestimated grant causes TempDB spills. An overestimated grant wastes memory.

Diagnosing Memory Grant Waits

Check for Current Waits

-- Which queries are waiting for memory grants right now?
SELECT
    session_id,
    wait_type,
    wait_duration_ms,
    sql_handle,
    statement_start_offset,
    statement_end_offset
FROM sys.dm_exec_requests
WHERE wait_type = 'RESOURCE_SEMAPHORE'
ORDER BY wait_duration_ms DESC;

Check Memory Grant Details

-- What grants were requested vs. granted?
SELECT TOP 20
    session_id,
    requested_memory_kb,
    granted_memory_kb,
    used_memory_kb,
    max_used_memory_kb,
    CAST(100.0 * granted_memory_kb / NULLIF(requested_memory_kb, 0) AS DECIMAL(5,2)) AS grant_pct
FROM sys.dm_exec_query_memory_grants
WHERE session_id > 50  -- Exclude system sessions
ORDER BY requested_memory_kb DESC;

-- granted < requested = Waiting or undergranted
-- used > granted = Spill to TempDB (bad)

Historical Analysis: Which Queries Requested Large Grants?

-- Top memory-hungry queries (historical)
SELECT TOP 20
    query_hash,
    execution_count,
    total_grant_kb,
    total_grant_kb / execution_count AS avg_grant_kb,
    min_grant_kb,
    max_grant_kb
FROM sys.dm_exec_query_stats
WHERE total_grant_kb > 0
ORDER BY total_grant_kb DESC;

Root Causes of Memory Grant Issues

Cause 1: Insufficient Server Memory

The server simply doesn't have enough RAM for concurrent queries:

-- Check server memory allocation
SELECT
    (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Total Server Memory (KB)') / 1024.0 AS Total_MB,
    (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Target Server Memory (KB)') / 1024.0 AS Target_MB,
    (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Free Memory (KB)') / 1024.0 AS Free_MB;

If Free_MB is consistently < 10% of Total, memory is constrained.

Cause 2: Inaccurate Cardinality Estimates

The optimizer estimates row counts wrong, underestimating the memory needed:

-- Query estimates 1000 rows, actually processes 1 million
-- Requests 50 MB, but needs 5 GB
-- Waits for large grant, causing queue backup

This is the most common cause. Poor statistics lead to bad estimates.

Cause 3: Overly Aggressive Parallelism

A 4-way parallel query requests 4x the memory it would serially. If multiple parallel queries run, memory exhausts quickly:

-- Parallel query (8 cores, MAXDOP 0)
-- Requests 1 GB per thread = 8 GB total
-- But only 16 GB server memory; other queries starved

Cause 4: TempDB Spills Force Re-requests

A query spills to TempDB because it underestimated memory. It must re-request memory to handle the spill, causing cascading waits.

Performance Tuning: Strategies

Strategy 1: Update Statistics

Why: Bad statistics = bad cardinality estimates = wrong memory grants.

-- Update all statistics on the table
EXEC sp_updatestats @resample = 'RESAMPLE';

-- Or manually
UPDATE STATISTICS MyTable;
UPDATE STATISTICS MyTable (idx_MyIndex);

-- Verify statistics are fresh
DBCC SHOW_STATISTICS ('MyTable', 'idx_MyIndex');

Strategy 2: Add Covering Indexes

Why: Fewer rows processed = smaller sort/hash operations = smaller memory grants.

-- Before: Full table scan, then sort in memory
SELECT * FROM Orders
ORDER BY CustomerID, OrderDate;
-- Requests large grant

-- After: Index covers query, fewer rows scanned
CREATE INDEX idx_cust_date ON Orders(CustomerID, OrderDate) INCLUDE (Amount);
SELECT Amount FROM Orders
ORDER BY CustomerID, OrderDate;
-- Requests smaller grant (only Amount + key columns)

Strategy 3: Use MAXDOP Hints to Limit Parallelism

Why: Parallel queries request more memory. Limiting parallelism reduces grant requests.

-- High parallelism, high memory demand
SELECT * FROM LargeTable
GROUP BY Category
HAVING COUNT(*) > 100;
-- Requests 500 MB with MAXDOP 0 (all cores)

-- Limited parallelism, lower memory demand
SELECT * FROM LargeTable
GROUP BY Category
HAVING COUNT(*) > 100
OPTION (MAXDOP 2);
-- Requests 100 MB with MAXDOP 2

Trade-off: Query runs slower, but doesn't starve other queries.

Strategy 4: Reduce Working Set with Filtering

Why: Sorting/hashing fewer rows means smaller memory grants.

-- Bad: Sort entire 100M-row table
SELECT TOP 10 * FROM Orders
WHERE Status = 'Pending'
ORDER BY OrderDate DESC;
-- Sorts all Pending orders, requests large grant

-- Better: Filter first, then sort
SELECT TOP 10 * FROM Orders
WHERE Status = 'Pending' AND OrderDate >= DATEADD(MONTH, -1, GETDATE())
ORDER BY OrderDate DESC;
-- Fewer rows, smaller sort, smaller grant

Strategy 5: Break Into Smaller Batches

Why: Processing in smaller chunks reduces peak memory demand.

-- Bad: One large operation
UPDATE Orders SET Status = 'Processed' WHERE Year(OrderDate) = 2024;
-- Sorts/hashes millions of rows, requests huge grant

-- Better: Batch processing
DECLARE @BatchSize INT = 10000;
DECLARE @Processed INT = 0;

WHILE @Processed < (SELECT COUNT(*) FROM Orders WHERE Year(OrderDate) = 2024)
BEGIN
    UPDATE TOP (@BatchSize) Orders
    SET Status = 'Processed'
    WHERE Year(OrderDate) = 2024;

    SET @Processed = @Processed + @@ROWCOUNT;
END;
-- Each batch requests smaller grant

Strategy 6: Increase Server Memory (if available)

Why: More RAM = larger grant pool = fewer waits.

-- If server has unused RAM
EXEC sp_configure 'max server memory (MB)', 64000;  -- 64 GB
RECONFIGURE;

-- Restart to apply, or use dynamic memory (if supported)

Strategy 7: Use TRACE FLAG 8649

Why: This flag changes memory grant feedback behavior in SQL Server 2019+.

-- Enable memory grant feedback (SQL Server 2019+)
-- Helps optimizer learn from previous execution and adjust grants

-- No T-SQL equivalent in older versions; must use TF

Real-World Example: The Runaway Query

A report query started requesting 4 GB memory grants. Inventory queries waited 30+ seconds:

-- The problem query
SELECT
    CustomerID,
    COUNT(*) AS Orders,
    AVG(Amount) AS AvgAmount
FROM OrderHistory  -- 1 billion rows
GROUP BY CustomerID
ORDER BY Orders DESC;

Diagnosis

Statistics hadn't been updated in 6 months. The optimizer estimated 10M rows, actually processing 1B. Requested 200 MB, needed 4 GB. Undergranted, spilled to TempDB, causing cascade waits.

Fixes Applied

1. Updated statistics on OrderHistory (SAMPLE 30%)
2. Added index on (CustomerID, Amount)
3. Changed MAXDOP from 0 to 2 (reduce parallelism)
4. Filtered data to last 12 months instead of all history

Results

• Memory grant: 4 GB → 500 MB
• Query time: 45 seconds → 8 seconds
• Inventory queries: 30 sec wait → 0 sec wait

Monitoring and Alerting

Query That Detects Large Grants

-- Alert if query requested > 1 GB
SELECT
    session_id,
    requested_memory_kb / 1024.0 AS requested_mb,
    granted_memory_kb / 1024.0 AS granted_mb,
    SQL_TEXT = (
        SELECT TEXT FROM sys.dm_exec_sql_text(sql_handle)
    )
FROM sys.dm_exec_query_memory_grants
WHERE requested_memory_kb > 1024 * 1024  -- > 1 GB
ORDER BY requested_memory_kb DESC;

Set Up Baseline

-- Collect memory grant baseline daily
CREATE TABLE MemoryGrantBaseline (
    CollectionDate DATETIME2,
    AvgGrantKB NUMERIC(15,2),
    MaxGrantKB BIGINT,
    Sessions_Waiting INT
);

INSERT INTO MemoryGrantBaseline
SELECT
    GETDATE(),
    (SELECT AVG(granted_memory_kb) FROM sys.dm_exec_query_memory_grants),
    (SELECT MAX(granted_memory_kb) FROM sys.dm_exec_query_memory_grants),
    (SELECT COUNT(*) FROM sys.dm_exec_requests WHERE wait_type = 'RESOURCE_SEMAPHORE');

-- Alert if max grant spike > 2x normal
-- Alert if waiting sessions > 0 for > 5 minutes

The Verdict

Memory grant waits are usually caused by one of four things: bad statistics, poor indexes, over-parallelism, or insufficient server memory. Fix statistics and indexes first — they're the highest-impact optimizations. If problems persist, tune parallelism or increase memory.

Tuning roadmap:

1. Update statistics on tables with large sorts/hashes
2. Add indexes to reduce working set
3. Limit parallelism with MAXDOP hints on expensive queries
4. Batch large operations
5. Monitor and alert on memory grant spikes
6. Increase server memory only if other options exhausted