Memory grant is like a container for a query where it flourishes, sorts, joins and stores temporary result sets. Too small grant makes a query slow whereas too large suffers other queries. Understanding how SQL Server calculates memory grants, how to monitor them, and fix common issues are essential knowledge for every DBA. This guide breaks down the key concepts, shows how to identify problematic queries, and offers practical steps to troubleshoot and optimize memory grants in real workloads. Let's start.
Figure-1: Memory Grants
Memory Grant
Query memory grant or query work buffer is a part of SQL Server memory which is allocated to a query before execution. It is used for operators such as hash joins, hash aggregates, sort, spool operators and exchange operators (parallelism). It is called "grant" as SQL Server requires queries to reserve the necessary memory before they begin execution.
Top Memory Consumers
In execution phase, a query needs memory at:
- Query Optimizer - Optimizer picks the low cost one among hundreds of potentials plans for query execution, which requires significant amount of memory. Insufficient memory will increase query compilation time, and potentially inefficient plans. Memory utilization time is short.
- Plan Cache - Based on the query, SQL Server may take from millisecond to minutes to generate a execution plan. To avoid overhead, SQL Server stores plans for future usage. Lack of memory would result-in unnecessary plan recompilation and CPU usage. SQL Server tends to keep the plan as long as possible i.e. memory utilization time is longer.
- Memory Grant - It is used to store temporary result set for hash joins, hash aggregates, sort, and spool operation. The lifetime of memory grant is the same as the lifetime of query. In case of inadequate memory, SQL Server will use tempdb, and query will crawl.
Memory Grant Feedback (MGF)
MGF is the process where the SQL Server Engine gets feedback from the previously executed query and adjust the Memory Grant for subsequent executions of the same query. This helps to optimize memory usage dynamically by either reducing excessive grants or increasing insufficient grants to prevent spills. There are two modes:
- Batch Mode MGF - Adjusts grants for batch mode operations.
- Row Mode MGF - Adjusts grants for both batch and row mode operations.
Memory Grants Pending
The number of SQL Server queries which are waiting for memory grants. Usually, it will be zero unless there is high memory stress. Following query will display pending memory grants (Figure-2).
SELECT object_name, counter_name, cntr_value FROM sys.dm_os_performance_counters
WHERE counter_name = 'Memory Grants Pending'
AND [object_name] LIKE '%Memory Manager%'
Figure-2: Memory Grants Pending
Memory Grants Properties
From any select query execution plan, right click on the SELECT operator, you will see below properties under MemoryGrantInfo:
Figure-3: Memory Grants Properties
- Desired Memory - Total memory a query wish to have for execution.
- For parallel plan, Desired Memory > Required Memory. As it requires additional memory for thread synchronization
- For serial plan, Desired Memory ≈ Required Memory.
- Granted Memory - Total granted memory to the memory.
- Grant Wait Time - How much time the query waited to obtain the memory.
- Is Memory Grant Feedback Adjusted: Current status of memory grant feedback for the query. There are five status:
- NoFirstExecution - The query ran only once and there is no history of memory adjustment.
- YesAdjusting - SQL Server is taking feedback from previous execution and adjusting it.
- YesStable - Memory grant value is optimal for this query.
- NoAccurateGrant - No need for further memory grant and no spill on tempdb.
- NoFeedbackDisabled - SQL Server did not find out optimal memory grant after few iteration and stopped adjusting the memory.
- Last Requested Memory - Amount of granted memory (in KB) at previous execution of the same query. If IsMemoryGrantFeedbackAdjusted is YesAdjusting, then Last Requested Memory may differ from the current Granted Memory.
- Max Query Memory - Max amount of memory available for individual query grants.
- Max Used Memory - Max amount of memory usage during previous execution of the query.
- Requested Memory - Total amount of requested memory for a query. It cannot exceeds Max Query Memory.
- Required Memory - Total required memory to start query execution.
- Serial Desired Memory - Total desired memory to run the query in serial execution (single degree of parallelism) mode.
- Serial Required Memory - Minimum amount of memory required to start execution in serial execution (single degree of parallelism) mode. This is required to create the internal data structure for the memory consuming operators.
Memory Grant Issues
Sometimes SQL Server cannot calculate memory grant accurately. It either over or underestimates and problems arise.
Overestimation
When SQL Server allocate additional memory unwisely to a query, then other queries may not get sufficient memory to start execution and wait for longer period. Ultimately, it hampers overall performance. Open execution plan for following query (courtesy brentozar.com) and right click on the SELECT operator. You will see something like Figure-4.
SELECT CONVERT(NVARCHAR(4000), u.DisplayName) AS DisplayName
FROM dbo.Users AS u
WHERE u.CreationDate >= '2016-10-11'
AND u.CreationDate < '2016-10-12'
ORDER BY u.CreationDate
Figure-4: Memory grant overestimation
Underestimation
When SQL Server allocates insufficient memory, the sort operation will spill to tempdb though memory is available. Hence, performance degrades.
SELECT u.DisplayName
FROM dbo.Users AS u
WHERE u.CreationDate >= '2016-10-11'
AND u.CreationDate < '2016-10-12'
ORDER BY u.CreationDate;
Figure-5: Memory grant underestimation
Detect Problematic Queries
To identify queries with underestimated or overestimated memory grant, execute below queries.
SELECT
COALESCE(DB_NAME(st.dbid), DB_NAME(CONVERT(INT, qp.dbid))) AS [DatabaseName],
qs.creation_time AS [PlanCreationTime],
qs.last_execution_time AS [LastExecutedTime],
qs.max_grant_kb,
SUBSTRING(
st.text,
(qs.statement_start_offset / 2) + 1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset) / 2) + 1
) AS [QueryText],
st.text, cp.objtype,qp.query_plan
FROM sys.dm_exec_cached_plans AS cp
JOIN sys.dm_exec_query_stats AS qs ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE qp.query_plan.exist('declare namespace n=" http://schemas.microsoft.com/sqlserver/2004/07/showplan "; //n:MemoryFractions') = 1
AND COALESCE(DB_NAME(st.dbid), DB_NAME(CONVERT(INT, qp.dbid)))
NOT IN ('master', 'model', 'msdb', 'tempdb')
ORDER BY qs.max_grant_kb DESC;
Figure-6: Identify queries with underestimated or overestimated memory grant
Memory Grant Mitigation
- Analyze and rewrite inefficient queries, particularly those involving large sorts, hashes, or complex joins, to reduce their memory requirements.
- Ensure accurate cardinality estimates by regularly updating statistics and rebuilding indexes. This helps SQL Server generate more efficient execution plans and memory grant requests.
- Review schema design and optimize column data types and sizes, especially for string columns, to reduce the overall memory footprint.
- Configure the max server memory setting in SQL Server to define the upper limit of memory that SQL Server can use, preventing it from consuming all available system memory.
Final Words
Memory grants ensure SQL Server allocates enough workspace memory for operations like sorting and hashing, but inaccurate estimates can lead to spills, slow reads. By monitoring plans for excessive grants or repeated spills, DBAs can identify queries that are over- or under-estimating memory needs. Improving statistics, indexing, and query design helps the optimizer make more accurate estimates. Ultimately, well-managed memory grants lead to faster queries and a more stable, predictable SQL Server environment.
References
Going Further
If SQL Server is your thing and you enjoy learning real-world tips, tricks, and performance hacks—you are going to love my training sessions too!
Need results fast? I am also available for 1-on-1 consultancy to help you troubleshoot and fix your database performance issues.
Let’s make your SQL Server to take your business Challenge!
For any queries, mail to mamehedi.hasan[at]gmail.com.