As a SQL Server DBA, effectively controlling memory allocations is essential to guaranteeing optimal query performance. In SQL Server 2022, Microsoft enhanced a powerful feature called Memory Grant Feedback Persistence, which allows SQL Server to better anticipate memory requirements for future query executions. This feature solves a common issue wherein SQL Server may overestimate or underestimate the amount of memory required for a query, resulting in less-than-ideal performance or even the failure of the query.

Understanding Memory Grant Feedback Persistence

Memory Grant Feedback Persistence operates by recording and preserving data on the real memory consumption of queries as they are being executed. This data is kept in the plan cache and is used to modify the RAM allotment for the same or related queries that are executed again later on. SQL Server may enhance query performance and decrease resource contention by using this input to help it make more accurate memory allocation decisions.

If the SQL Server underestimates the memory grant requirement for the query, the query has to wait until it acquires the necessary memory. If SQL Server overestimates the memory grant requirements you may start facing memory pressure when multiple similar queries run in parallel.

An existing feature called memory grant feedback (MGF) modifies the amount of memory allotted for a query depending on historical performance. But in the early stages of the project, the memory grant adjustment was the only thing that was cached along with the plan. This meant that if a plan was removed from the cache, the feedback process would have to start over, which would have negatively impacted speed the first few times a query was run following eviction. To ensure that the advantages endure during cache evictions, the new approach is to store the grant information in the Query Store together with the other query information. Memory grant feedback constraints are addressed in a non-intrusive manner via memory grant feedback persistence.

Here's a step-by-step explanation of how Memory Grant Feedback Persistence works

• First Query Execution: Based on a number of variables, including query complexity, data size, and memory resources available, SQL Server determines how much memory will be needed when a query is run for the first time. We refer to this first estimate as the "memory grant."
• Capturing Memory consumption: SQL Server keeps track of the real memory consumption while a query is running and records the maximum amount of memory that the query uses. The query plan includes storing this data in the plan cache.
• Memory Grant Adjustment: SQL Server modifies the memory grant for reruns of the same query or queries that are similar if the actual memory consumption deviates noticeably from the initial memory grant.

SQL Server raises the memory grant for subsequent executions if the actual memory consumption is much above the initial memory grant or SQL Server lowers the memory grant for subsequent executions if the actual memory use is much less than the original memory grant.

Configuring Memory Grant Feedback Persistence

While Memory Grant Feedback Persistence is enabled by default in SQL Server 2022. To disable memory grant feedback persistence for all query executions originating from the database, execute the following within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERSISTENCE = OFF;

Disabling memory grant feedback persistence will also remove existing collected feedback.

To re-enable memory grant feedback persistence for all query executions originating from the database execute the following within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERSISTENCE = ON;

 Observing Memory Grant Feedback Persistence

To better understand how Memory Grant Feedback Persistence works, let's walk through a practical example using a sample database and query.

Step 1: Create a Sample Database and Table

Let's create a sample database and table to work with. We will use the code below to create the database.

CREATE DATABASE  TestMGFP;
GO

and this code to create the table:

USE TestMGFP;
GO
CREATE TABLE Orders(
OrderID INT IDENTITY(1,1) PRIMARY KEY,
OrderDate DATE,
CustomerID INT,
OrderAmount DECIMAL(10,2)
);
GO

Insert some sample data in the table.

Step 2: Execute a Query and Observe the Initial Memory Grant

Let's run a query that aggregates data from the 'Orders' table and observe the initial memory grant:

SELECT CustomerID, SUM(OrderAmount) AS TotalOrderAmount
FROM Orders
GROUP BY CustomerID
ORDER BY TotalOrderAmount DESC;
GO

When you execute the query for the first time, SQL Server will provide an estimated memory grant based on its internal calculations.

Step 3: Observe the Actual Memory Usage

To see the actual memory usage for the query, we can use the 'sys.dm_exec_query_stats' dynamic management view (DMV) and Actual execution plan for query.

SELECT query_stats.query_hash, query_stats.query_plan_hash, text, 
query_stats.max_grant_kb, query_stats.max_used_grant_kb
FROM sys.dm_exec_query_stats query_stats
CROSS APPLY sys.dm_exec_sql_text(query_stats.sql_handle)
ORDER BY query_stats.max_grant_kb DESC;
GO

This query will show the actual memory usage ('max_used_grant_kb') for the executed queries.  We can see that the initial 'max_grant_kb' value is 92744KB which is around 91 MB  and the actual 'max_used_grant_kb' value is 7488KB which is 7.3MB. This means Query was allocated max memory around 91 MB and it actually utilized 7.3 MB.

We can cross verify this with the actual execution plan. Here we can see that Memory grant is shown as 91 MB.

Step 4: Observe the Memory Grant Adjustment

Since the actual memory usage is much lower than the initial memory grant, SQL Server will adjust the memory grant for subsequent executions of the same query. Execute the original query again:

SELECT CustomerID, SUM(OrderAmount) AS TotalOrderAmount
FROM Orders
GROUP BY CustomerID
ORDER BY TotalOrderAmount DESC;
GO

Now, SQL Server will provide a lower memory grant based on the feedback from the actual memory usage in the previous execution. We can verify the new memory grant using the 'sys.dm_exec_query_stats' DMV and with actual execution plan.

SELECT query_stats.query_hash, query_stats.query_plan_hash, text, 
query_stats.max_grant_kb, query_stats.max_used_grant_kb
FROM sys.dm_exec_query_stats query_stats
CROSS APPLY sys.dm_exec_sql_text(query_stats.sql_handle)
ORDER BY query_stats.max_grant_kb DESC;
GO

You should see that the 'max_grant_kb' value has decreased for the query, reflecting the memory grant adjustment made by SQL Server based on the Memory Grant Feedback Persistence feature.

This can be verified in the actual execution plan. The memory grant is shown as 27 MB.

Benefits of Memory Grant Feedback Persistence

Memory Grant Feedback Persistence offers several benefits for managing query performance in SQL Server:

• Improved Query Performance: By accurately estimating the memory requirements for queries, SQL Server can allocate the appropriate amount of memory, reducing the need for spilling operations or query failures due to insufficient memory. This can lead to significant performance improvements, especially for memory-intensive queries.
• Efficient Memory Utilization: With more accurate memory allocation, SQL Server can better utilize available memory resources, minimizing waste and allowing for more efficient memory management across the system.
• Adaptive Memory Management: Memory Grant Feedback Persistence enables SQL Server to adapt to changing workloads and data sizes dynamically. As queries are executed and memory usage feedback is captured, SQL Server can adjust the memory grants accordingly, ensuring optimal performance even as the workload characteristics change over time.
• Reduced Administrative Overhead: By leveraging the automatic memory grant adjustments provided by Memory Grant Feedback Persistence, DBAs can spend less time manually tuning and adjusting memory grants, freeing up resources for other critical tasks.

Summary

By using input from real memory consumption during query execution, Memory Grant input Persistence in SQL Server 2022 is a potent feature that helps optimize query performance. In order to improve the accuracy of memory grant calculations for subsequent executions of the same or comparable queries, SQL Server gathers and stores this information.

This feature is a great addition to SQL Server's performance optimization capabilities since it can result in better query performance, efficient memory utilization, and decreased administrative overhead.