Introduction
When SQL Server runs a query, it needs memory for operations like sorting and joining data. It also relies on memory during query compilation to hold intermediate plans while the Query Optimizer finds the best execution strategy. In parallel processing scenarios, the memory requirement grows even further.
SQL Server manages this by pre-allocating memory for each query through the SQL Server Operating System (SQLOS). This process ensures that no single query can monopolize the server’s memory. The allocation is saved in the query plan and consists of two parts:
- Required Memory: The minimum necessary for sorting and joining.
- Additional Memory: Allocated based on the estimated row count.
Identifying Large Memory Grants
To find queries with the biggest memory grants, you can use the following query:
SELECT mg.granted_memory_kb,
mg.session_id,
t.text,
qp.query_plan
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
ORDER BY 1 DESC;
The Memory Grant Problem
Memory grants must strike a balance:
- Under-allocated Memory: If the memory grant is too small, sorting or joining can spill over to disk (tempdb), causing significant performance slowdowns.
- Over-allocated Memory: If the memory grant is too large, it wastes resources and can reduce overall server performance.
Accurate memory grants rely on up-to-date statistics and correct cardinality estimates. Outdated or incorrect statistics can lead to inefficient memory usage.
Row Mode Memory Grant Feedback
Overview
Row Mode Memory Grant Feedback is a feature introduced in SQL Server 2019 (when the database compatibility level is set to 150). It builds on the Batch Mode Memory Grant Feedback introduced in SQL Server 2017, but extends the functionality to rowstore (row-based) data.
This feature adjusts memory grants dynamically based on actual query performance. By comparing estimated memory usage with actual usage, the engine can optimize subsequent executions.
Key Concepts (from Microsoft Docs)
- Excessive Memory Grant: If a query’s memory grant is more than double its actual usage, SQL Server recalculates and updates the cached plan.
- Insufficient Memory Grant: If the query spills to disk, SQL Server recalculates the memory requirement for future executions.
For more details, see the official documentation:
Memory Grant Feedback.
How It Works
- First Execution: Memory Grant Feedback does not adjust anything on the first run. Instead, SQL Server collects performance metrics.
- Subsequent Executions: The engine applies the feedback it gathered to adjust the memory grant, leading to optimized performance in future runs.
- Stable State: After several executions and adjustments, the memory grant eventually stabilizes. From that point on, the allocation remains consistent unless data distribution or other factors change significantly.
Example Execution Plan Behavior:
- First Run: The plan shows
IsMemoryGrantFeedbackAdjusted = NoFirstExecution
. - Second Run: SQL Server adjusts the memory grant, and you see
IsMemoryGrantFeedbackAdjusted = YesAdjusting
. - Stable State: After several runs, the plan reaches
IsMemoryGrantFeedbackAdjusted = YesStable
, indicating that memory grants have stabilized.
Row Mode Memory Grant Feedback in Action
Below is an example using the WideWorldImporters database:
SELECT OD.CustomerID,
OD.CustomerPurchaseOrderNumber,
OD.InternalComments,
OL.Quantity,
OL.UnitPrice
FROM [Sales].[Orders] OD
INNER JOIN [Sales].[OrderLines] OL
ON OD.OrderID = OL.OrderID
ORDER BY OD.[Comments];
First Execution: The query might receive a large memory grant (e.g., 824 MB) because SQL Server makes conservative estimates.
Second Execution: Based on feedback, SQL Server may reduce the grant to something more efficient (e.g., 13 MB).
Enabling or Disabling Row Mode Memory Grant Feedback
You can toggle Row Mode Memory Grant Feedback as follows:
-- Disable
ALTER DATABASE SCOPED CONFIGURATION
SET ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF;
-- Enable
ALTER DATABASE SCOPED CONFIGURATION
SET ROW_MODE_MEMORY_GRANT_FEEDBACK = ON;
Conclusion
Row Mode Memory Grant Feedback in SQL Server 2019 is a powerful tool for improving query performance by dynamically adjusting memory grants. It minimizes both over-allocation and under-allocation, helping maintain optimal server performance. As part of the Intelligent Query Processing suite, it adds another layer of efficiency by reducing common memory-related bottlenecks and ensuring better resource utilization.
Optimize SQL Server Performance: Mastering Memory Grants and Intelligent Query Processing