What is a Memory Grant in SQL Server?

A memory grant is a specific portion of workspace RAM that SQL Server allocates to a query before execution, required for operations like sorting (ORDER BY) and hashing (HASH JOIN). It is distinct from the Buffer Pool used for caching data pages.

What causes RESOURCE_SEMAPHORE waits in SQL Server?

RESOURCE_SEMAPHORE waits occur when a query is ready to execute but there is not enough available workspace memory (RAM) to satisfy its memory grant request. The query must wait in a queue until other queries finish and release their memory.

How do I fix excessive memory grants and RESOURCE_SEMAPHORE waits?

The root cause is usually poor cardinality estimates leading SQL Server to request too much RAM. You can fix this by updating statistics, adding covering indexes to eliminate Sorts/Hash Joins, or using the MAX_GRANT_PERCENT query hint to cap memory usage.

How can I check active memory grants in SQL Server?

You can identify queries consuming or waiting for memory by querying the sys.dm_exec_query_memory_grants Dynamic Management View (DMV). Look for queries where granted_memory_kb is much larger than used_memory_kb.

Check Memory Grants in 45 Seconds. From Symptoms to Root Clause. The "45 Seconds DBA Series" – What Real DBAs Check First

Before we dive into today's topic, if you missed my previous post you can take a look at Check IO Bottlenecks in 45 Seconds. The "45 Seconds DBA Series" – What Real DBAs Check First | Part 5.

👉 If you found this deep-dive helpful, feel free to check out the ads—your support helps me keep creating high-quality SQL Server content for the community.

Memory grants are the silent concurrency killers of SQL Server. In this post, I will show you how to identify memory-hogging queries and resolve catastrophic RESOURCE_SEMAPHORE waits in just 45 seconds—before your server grinds to a complete halt!

TL;DR

✔️ Memory Grants are for Sorting and Hashing: They are workspace RAM, completely separate from the Buffer Pool caching your data pages. 🛠️
✔️ RESOURCE_SEMAPHORE Waits: The ultimate red flag indicating that queries are queuing up, starving for execution memory. 💣
✔️ Identify the Culprits: Use sys.dm_exec_query_memory_grants to instantly spot queries demanding massive, unjustified amounts of RAM. 🧪
✔️ Fix the Root Cause: 99% of the time, memory bloat is caused by outdated statistics or bad indexing, not a lack of physical server RAM. ✔️

Hi SQL SERVER Guys,

Have you ever encountered a scenario where your CPU is practically idle, your disk I/O is normal, but user applications are timing out left and right? Welcome to the dreaded RESOURCE_SEMAPHORE bottleneck. This happens when your instance runs out of workspace memory for query execution. Let's dissect how to diagnose and fix memory grant issues from symptoms to root causes in under a minute!

What It Really Is: Workspace Memory

When a query execution plan includes operations like an ORDER BY (Sort) or a HASH JOIN, SQL Server cannot process these on the fly. It must reserve a specific chunk of RAM—known as a Memory Grant—before the query is allowed to start executing.

If a poorly optimized query demands 20GB of RAM and your server only has 10GB of available workspace memory ...that query waits. But not only, it is even worse, because every query behind it waits too.

💣 The Symptom: A sudden spike in RESOURCE_SEMAPHORE waits in your wait stats or active sessions.
✔️ The Misconception: "We need more RAM." No, you need better cardinality estimates.

🧪 The 45-Second Diagnostic Script

Run this T-SQL immediately when you suspect memory pressure. It reveals exactly who is asking for RAM, who has it, and who is waiting:

SELECT 
    session_id,
    request_id,
    request_time,
    grant_time,
    requested_memory_kb / 1024 AS Requested_MB,
    granted_memory_kb / 1024 AS Granted_MB,
    used_memory_kb / 1024 AS Used_MB,
    ideal_memory_kb / 1024 AS Ideal_MB,
    query_cost,
    wait_time_ms
FROM sys.dm_exec_query_memory_grants
ORDER BY requested_memory_kb DESC;

How to read this: Look at the gap between Granted_MB and Used_MB. If a query is granted 5,000 MB but only uses 10 MB, it is aggressively hoarding memory it does not need, starving the rest of your server.

🚀 My REAL Strategy

In my experience, throwing physical RAM at a RESOURCE_SEMAPHORE issue is like pouring water into a bucket with a hole. It just buys you a few hours. The root cause is almost always Bad Cardinality Estimates.

If SQL Server's statistics tell the optimizer to expect 10 million rows, it will ask for a massive memory grant to sort them. If the query actually only returns 50 rows, all that requested memory is locked up and completely wasted.

1. Fix the Stats: First step is always UPDATE STATISTICS YourTable WITH FULLSCAN; on the tables involved in the worst offending queries.
2. Kill the Sorts: If a query is sorting massive datasets, provide a covering index with the correct key order. An index naturally pre-sorts data, eliminating the memory grant requirement entirely.
3. Emergency Tourniquet: If you cannot change the code or indexes immediately, use the MAX_GRANT_PERCENT query hint to throttle the rogue query:
OPTION (MAX_GRANT_PERCENT = 5)

📢 Support the Blog: Did you find this deep-dive helpful? The ads you see here are selected to reflect your interests. If a partner's offer catches your eye, give it a look! Your engagement helps me continue publishing premium SQL Server content for the community.

Biondi Luca @2026 - Sharing over 25 years of Gained Knowledge for Passion. Share if you like my posts!

Search This Blog

SQL Server Performance & Troubleshooting – Where Milliseconds Matter 🚀