Introduction

When a query is slow, spills to tempdb, or other queries suddenly start waiting for memory, don’t look only at CPU and logical reads.

Check the query’s memory grant.

SQL Server gives certain queries workspace memory for operations such as sorts and hash joins. A grant that is too small can cause expensive spills to disk; a grant that is too large can waste memory and reduce concurrency.

A useful troubleshooting sequence is:

Slow Query
    ↓
Check Execution Plan
    ↓
Check Memory Grant
    ↓
Look for Sort/Hash Spills
    ↓
Investigate Statistics / Cardinality / Query Design
    ↓
Retest

Explanation

Consider this query:

SELECT
    CustomerID,
    SUM(OrderAmount) AS TotalSales
FROM dbo.Orders
GROUP BY CustomerID
ORDER BY TotalSales DESC;

The database may need memory for:

• Hash aggregation
• Sorting
• Other execution operators

If the optimizer underestimates how much memory is required, the query may receive too small a grant and spill intermediate data to tempdb.

If it overestimates, the query may reserve much more memory than it actually needs.

Microsoft describes both situations as important performance problems: excessive grants can reduce concurrency, while insufficient grants can cause expensive disk spills.

Real-World Example

Imagine a reporting workload with 50 concurrent queries.

One query requests a very large memory grant:

Query A → 8 GB
Query B → 500 MB
Query C → 300 MB
Query D → 400 MB
...

Even if Query A doesn’t actually use all 8 GB, that large reservation can reduce the memory available for other concurrent queries.

The result can look like:

Application
    ↓
Queries become slow
    ↓
CPU looks normal
    ↓
Users report timeouts
    ↓
Memory grant waits discovered

This is why high CPU isn’t the only resource problem worth investigating.

Find Current Memory Grants

You can inspect queries that have requested or received execution memory with:

SELECT
    session_id,
    request_time,
    grant_time,
    requested_memory_kb,
    granted_memory_kb,
    required_memory_kb,
    used_memory_kb,
    max_used_memory_kb,
    wait_time_ms,
    is_next_candidate
FROM sys.dm_exec_query_memory_grants
ORDER BY granted_memory_kb DESC;

sys.dm_exec_query_memory_grants returns queries that are waiting for or have received a memory grant. Sort and hash operations are examples of operations that can require these grants.

On SQL Server, the required permissions depend on version; Azure SQL Database uses database-level permissions such as VIEW DATABASE STATE.

A More Useful Diagnostic Query

To identify the SQL text associated with current memory grants:

SELECT
    mg.session_id,
    mg.requested_memory_kb,
    mg.granted_memory_kb,
    mg.used_memory_kb,
    mg.max_used_memory_kb,
    mg.wait_time_ms,
    st.text AS sql_text
FROM sys.dm_exec_query_memory_grants AS mg
OUTER APPLY sys.dm_exec_sql_text(mg.sql_handle) AS st
ORDER BY mg.granted_memory_kb DESC;

Look for queries where:

Granted Memory >> Used Memory

or queries that are waiting for a grant.

But remember: a large grant is not automatically a problem. A large analytical query may legitimately need substantial workspace memory.

What Should You Look For in the Execution Plan?

When investigating memory grants, pay particular attention to:

Sort operators

Large sorts can require significant workspace memory.

Hash Match operators

Hash joins and hash aggregates can also require memory grants.

Spill warnings

If a sort or hash operation spills to tempdb, investigate why.

Conceptually:

Memory Grant Too Small
        ↓
Sort / Hash
        ↓
Not enough workspace memory
        ↓
Spill to tempdb
        ↓
Additional I/O
        ↓
Query slows down

Why Statistics Matter

One of the most important clues is cardinality estimation.

Suppose SQL Server estimates:

10,000 rows

but the query actually processes:

10,000,000 rows

The optimizer may make poor decisions about join strategy, memory requirements, and other operators.

Microsoft specifically recommends keeping statistics current because inaccurate statistics can contribute to unnecessarily high memory grants or spills caused by underestimated memory requirements.

So when you see a memory-grant problem, don’t immediately try to manipulate the grant.

Ask:

Why did the optimizer think this query needed this much or this little memory?

Memory Grant Feedback Can Help

Modern SQL Server versions and Azure SQL can use Memory Grant Feedback to adjust memory grants based on previous executions.

The goal is to:

Too Little Memory
      ↓
Spill
      ↓
Feedback
      ↓
Adjust Grant

or:

Too Much Memory
      ↓
Wasted Memory
      ↓
Feedback
      ↓
Reduce Grant

Row-mode memory grant feedback is available from SQL Server 2019 and Azure SQL at the appropriate compatibility level. SQL Server 2022 introduced persistent/percentile improvements, and Azure SQL Database supports these newer capabilities.

However, don’t use Memory Grant Feedback as a reason to ignore a fundamentally inefficient query.

If a query is processing far more rows than necessary, fix the query and data-access pattern first.

Best Practices

1. Look at the actual execution plan

Check:

• Memory Grant Info
• Granted Memory
• Used Memory
• Sort warnings
• Hash warnings
• Estimated vs actual rows

2. Keep statistics current

Bad cardinality estimates can contribute to memory-grant problems.

3. Reduce unnecessary rows early

Filtering data earlier can reduce the amount of data that downstream sort/hash operations must process.

4. Review indexes

An appropriate index can sometimes reduce scans, sorting, or other expensive operations.

5. Check concurrency

One large grant may be manageable.

Twenty simultaneous large grants may not be.

6. Understand Memory Grant Feedback

It can automatically adjust repeated workloads, but it isn’t a substitute for query tuning.

7. Measure before and after

Don’t change a query or index simply because the memory grant looks large.

Measure:

Duration
CPU
Logical Reads
Spills
Granted Memory
Used Memory
Concurrency

Common Mistakes to Avoid

Mistake 1: Assuming every large memory grant is bad

A large query may legitimately require a large grant.

Mistake 2: Looking only at granted_memory_kb

Compare the grant with actual usage and workload behavior.

Mistake 3: Ignoring spills

A query that repeatedly spills to tempdb deserves investigation.

Mistake 4: Immediately increasing server memory

More memory may help capacity, but it doesn’t fix bad cardinality estimates or inefficient query design.

Mistake 5: Using MAX_GRANT_PERCENT as the first solution

Query hints can be useful in specific situations, but they should not replace understanding why the optimizer requested the memory in the first place.

Mistake 6: Ignoring statistics

Statistics are often part of the root cause.

Pro Tip

When troubleshooting a slow query, add memory grant analysis to your standard checklist:

CPU
Logical Reads
Physical Reads
Waits
Blocking
Execution Plan
Cardinality Estimates
Memory Grant
Sort/Hash Spills
Statistics

This is especially valuable for reporting, ETL, aggregation, and analytical workloads, where large sorts and hash operations are common.

Summary

A query can be slow not because it lacks CPU, but because it received the wrong amount of execution memory. So, check memory grants and spills when troubleshooting serious SQL Server performance problems.