Memory grant feedback in SQL Server 2019

If the optimizer does not correctly calculate the required amount of memory to execute the query, it will be either a waste of memory that could be used by another process, or there will be disk data spill. To solve this problem, Microsoft has added Memory Grant Feedback. In this article, Greg Larsen explains how this works.

Memory Grant Feedback in earlier versions of SQL Server (prior to SQL Server 2019 or 15.x) was implemented only for queries running in batch mode. Queries in batch mode scan and calculate up to 900 rows at a time, in contrast to queries in row mode, when only one row is processed at a time. In version 15.x, memory grant feedback has been expanded to support querying in string mode.

What is memory grant feedback? This is the process of adjusting the calculation of memory needed for a query, taking into account how much memory was used during its previous runs. This means that if the cached query used too much memory at the last run, then SQL Server will reduce the memory allocation at its next run. Or if SQL Server detects a query that uses a disk due to the fact that the last time it was allocated insufficient memory, it will increase the memory for the query. The goal of feedback on memory grants is to adjust the memory requirements each time the query is executed until the query uses the amount of memory corresponding to the number of rows processed.

Functionality Memory grant feedback (in both batch and string modes) is part of a family of functions Intelligent Query Processing (IQP, Intelligent Request Processing). Figure 1 is a diagram showing all the IQP tools present in Azure SQL Database and SQL Server 2019, as well as the features that were originally part of Adaptive query processing (Adaptive Query Processing), included in previous versions of Azure SQL Database and SQL Server 2017.

Figure 1. Intelligent Query Processing

To better understand how memory grant feedback works, let’s take a look at the allocation of memory for a query during its first and subsequent executions.

See how memory grants change over time.

To see how memory allocation changes over time, we will execute the same query several times. At each launch, we will see how much memory has been allocated and how much has been used, as well as additional feedback information that helps the Database Engine adjust memory requirements. For the test we will use the WideWorldImporters database, which can be downloaded from here. A test request for checking feedback on memory grants in string mode is shown in Listing 1.

USE WideWorldImporters;
GO
SELECT * FROM Sales.Orders
ORDER BY OrderDate;

Listing 1. Test request

Before executing this query, let me tell you why the estimate of the required memory may be incorrect, and still do some presetting.

Why the estimate of the required memory may be erroneous

There are many factors that affect the amount of memory required for a request. When calculating the total required memory, the query optimizer must consider the memory requirements for each operation in terms of execution. Here are some parameters that the optimizer takes into account when calculating the memory needed to execute the query:

• The cardinality of the tables involved in the query
• Selectable speakers
• Estimated Row Size
• Is there a need to sort and / or join data
• Is the query running in parallel

To correctly calculate the required memory, SQL Server must take into account many aspects of where memory might be required when executing a query. If too much memory is allocated, then SQL Server wastes memory when it could be used by other processes. Or if not enough memory is allocated, then the request will merge the data to disk, which is an expensive operation.

SQL Server is not always good at calculating memory the first time. Outdated or inaccurate statistics are the most common reason SQL Server calculates the wrong amount of memory. But even if the statistics are correct, SQL Server can overestimate or underestimate the required amount of memory for some other reason. One of these reasons is that SQL Server does not correctly evaluate the Estimated Row Size for returned columns. In the following example, we will see how oversizing a row causes the query to receive an excessive grant warning due to three NVARCHAR (MAX) columns, which are always NULL.

But now, if SQL Server incorrectly calculates the required memory the first time the query is executed, then for subsequent executions, memory grant feedback is used. Each time the query is executed, information about the allocated memory for the previous execution of this query is analyzed. If at the previous start the memory allocation was incorrect, then the Database Engine corrects the memory allocation.

Setup for first run

In order to demonstrate how, over time, the allocation of memory adapts to the request, you need to do some preparatory work. The first is to change the compatibility level of the WideWorldImporters database to 150. This must be done because Memory Grant Feedback (Row Mode) is only available for databases with compatibility level 150. The code in Listing 2 changes the compatibility level databases on 150.

ALTER DATABASE WideWorldImporters SET COMPATIBILITY_LEVEL = 150;
GO

Listing 2. Setting the compatibility level to 150

The next thing to do is to clear the procedure cache so that when you run the test request, a new plan is created for it.

USE WideWorldImporters;
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

Listing 3. Clearing the procedure cache

After preconfiguring, you can run the query for the first time (Listing 1).

First run request

You can see the amount of memory used by the query and the feedback that the Database Engine provides in the actual execution plan.

At the first start, we get the execution plan shown in Figure 2.

Figure 2. Execution plan for the first run of the query from Listing 1

As you can see, there is a warning sign on the SELECT statement. When you hover over SELECT, the information shown in Figure 3 is displayed.

Figure 3. SELECT properties at the first start of the first execution.

For SELECT there is a warning “ExcessiveGrant”. The request was allocated 1,234,880 KB of memory, but it used only 11,296 KB. This means that the request has been allocated much more memory than required.

In SQL Server 15.x, in the properties of a SELECT statement in a section Memorygrantinfo Two new attributes have been added: IsMemoryGrantFeedbackAdjusted and LastRequestedMemory. These two attributes provide the feedback needed to adjust the allocated memory for subsequent query executions. You can see these two new attributes by looking at Memorygrantinfo in the graphical execution plan. To do this, in the execution plan, hover over the SELECT icon, right-click and select Properties from the menu that appears. These new query properties appear in the section. Memorygrantinfoshown in Figure 4.

Figure 4. Memory Allocation Information

Property IsMemoryGrantFeedbackAdjusted at first run equals NoFirstExecution, and the property LastRequestedMemory equals zero. Parameter RequestedMemory has a value of 1,234,880. This is the amount of memory that was requested when executing the request for the first time. In order to understand how the new feedback properties work, let’s look at the values ​​that they can take.

New MemoryGrantInfo Feedback Properties

These two new feedback properties are set each time the request is executed. The values ​​for these properties are based on what the Database Engine recognized at query time. This information is stored in a cached plan for use in subsequent executions of this query to determine if memory allocation needs to be adjusted.

Property IsMemoryGrantFeedbackAdjusted can take five different values, which are listed in table 1.

Table 1. Possible Values IsMemoryGrantFeedbackAdjusted

Table 1 shows that our previous query execution was the first, since the value is Firstexecution.

Property LastRequestedMemory shows the amount of requested memory in kilobytes (KB) the previous time the query was executed. Since the query was executed for the first time, the value was zero.
Let’s run the query a second time and see how these properties will be used to improve memory allocation.

Second query execution

As can be seen in Figure 5, after the second start, the warning sign that was present during the first run disappeared.

Figure 5. Plan for the second query execution

At the second start Memorygrantinfo shows that some values ​​of the feedback properties have changed, as well as the value RequestedMemory. Properties Memorygrantinfo for the second launch are shown in Figure 6.

Figure 6. MemoryGrantInfo properties for second run

Property IsMemoryGrantFeedbackAdjusted now it matters Yesadjusting. This status indicates that feedback was applied for the second run. Besides, LastRequestedMemory now takes the value of 1,234,880 – this is the requested memory (RequestedMemory) for the first execution of the request. Property RequestedMemory indicates that the second query execution requests only 21,120 KB of memory. This value is significantly less than the amount of memory requested at first run.

Before re-executing the request, I should note one nuance that I encountered. I found that if a lot of time elapses between repeated requests, the execution plan can be removed from the procedure cache. When this happens, the property IsMemoryGrantFeedbackAdjusted will matter NoFirstExecution. This indicates that the request has just been cached. Therefore, to see how the feedback works, you need to run it pretty quickly the second time to avoid problems with crowding out the cached plan.

Third query execution

Figure 7 shows the value Memorygrantinfo for the third execution of the request.

Figure 7. Third run

IsMemoryGrantFeedbackAdjusted now equal Yesstable. This means that the same amount of memory was allocated as in the second run. This can be verified by comparing the values. LastRequestedMemory and RequestedMemory. They match. For our request, feedback on memory grants allowed the request to adjust the required amount of memory after three executions.

When the required amount of memory is not stable

There are requests for which each execution requires a different amount of memory. If this happens, then SQL Server stops trying to adjust the size of the allocated memory and disables the feedback for such a request. One of the situations when this happens is the launch of a stored procedure, into which, at each execution, different parameter values ​​are passed.

To reproduce this situation when SQL Server disables feedback on memory grants, we execute a stored procedure with different parameters several times. Thus, at each execution, the query inside the procedure will require a different amount of memory due to the different number of returned records, depending on the parameter. The stored procedure code is shown in Listing 4.

USE WideWorldImporters;
GO
CREATE OR ALTER PROC TestProc (@KeyValue int)
AS
SELECT * FROM Sales.Orders
WHERE OrderID > 1 and OrderID < @KeyValue
ORDER BY OrderDate

Listing 4. Test stored procedure

The stored procedure accepts a parameter named @KeyValue. The code for testing the stored procedure is shown in Listing 5.

DECLARE @I INT = 1;
DECLARE @TestKeyValue INT;
WHILE @I < 35
BEGIN
	IF @I % 2 = 0
	 EXEC TestProc @KeyValue = 20000;
	ELSE
	 EXEC TestProc @KeyValue = 100;
	SET @I = @I + 1;
END

Listing 5. Code for calling a stored procedure

Looking at the code in Listing 5, you see that the stored procedure runs 35 times. Each time the stored procedure starts, the parameter value @KeyValue alternates between 20,000 and 100. When the parameter is set to 20,000, the query returns 19,998 rows, but if the parameter value is 100, only 98 rows are returned. As you can guess, each execution will require a different amount of memory, because the number of lines is very different.

Run the code in Listing 5 in the SSMS and see the actual execution plan. It can be seen that for starts from the second to the 32nd value IsMemoryGrantFeedbackAdjust It was Yesadjusting. But at execution 33, SQL Server stopped trying to adjust the memory for the stored procedure and turned off the feedback. In figure 8 you can see the properties Memorygrantinfo for the 33rd execution of the stored procedure.

Figure 8. Feedback on memory allocation is disabled during execution 33

As you can see, the parameter IsMemoryGrantFeedbackAdjusted has the meaning NoFeedbackDisabled. This means that the Database Engine was not able to use the feedback to get a stable allocation of memory for the stored procedure, and it was decided to disable the feedback for this request. For all subsequent executions of the stored procedure (after the 33rd start), feedback is disabled.

Not all requests need feedback

Sometimes SQL Server correctly determines the amount of memory the first time, so feedback is not used. Let's look at the code example in Listing 6.

USE WideWorldImporters;
GO
SELECT * FROM Sales.Orders
WHERE OrderID < 3
ORDER BY OrderDate;

Listing 6. Example with the correct memory allocation

At startup, we get the following execution plan.

Figure 9. Execution plan when running the code from Listing 6.

Property IsMemoryGrantFeedback shows that this is the first execution. In addition, the request requested 1024 KB of memory, as seen in RequestedMemory (figure 10).

Figure 10. MemoryGrantInfo for the first query execution

The memory allocation information for the second run is shown in Figure 11.

Figure 11. Memorygrantinfo for the second run

For the second run IsMemoryGrantFeedbackAdjusted has the meaning NoAccurateGrant. This means that the second run did not require more memory, so feedback is not used. This can be verified by looking at the fact that RequestedMemory identical in figures 10 and 11.

Disabling Memory Grant Feedback

If for any reason you want to disable memory grant feedback in row mode (Memory Grant Feedback - Row Mode), there are several ways to do this. The first is to set the compatibility level for the database to less than 150. There is only one problem with this method: all other functions that appeared in version 15.x will also be disabled. Or, you can disable database-level feedback through the option ROW_MODE_MEMORY_GRANT_FEEDBACK.

USE WideWorldImporters;
GO
ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF;
GO

Listing 7. Disabling memory allocation feedback for the database

This turns off feedback on memory grants at the database level. Now requests for WideWorldImporters will not use string feedback. But this is not the only way to disable feedback. You can also disable feedback for a specific request using HINT, as shown in Listing 8.

USE WideWorldImporters;
GO
SELECT * FROM Sales.Orders
ORDER By  OrderDate
OPTION
(USE HINT ('DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK'));

Listing 8. Using HINT to disable feedback

Listing 8 uses the hint DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACKwhich disables memory grant feedback for a single request.

Optimization of calculation of necessary memory for request

Feedback on memory grants in batch mode has been around for quite some time. But only in version 15.x did this feedback become available for queries in row mode. All that is needed to enable feedback for queries in string mode is to set the database compatibility level to 150. This, of course, will work if you use the current version of Azure SQL Database or SQL Server 2019. When memory grant feedback functionality is enabled in string mode, SQL Server uses memory allocation information from previous executions of the cached query to adjust the memory allocation for the current execution. Correcting the required memory with feedback each time the query is executed will sooner or later automatically lead to the calculation of the correct amount of memory.

Learn more about the course.