SQL SERVER – Introduction to Memory Grant Feedback

I just finished helping my second client while working on Comprehensive Database Performance Health Check for SQL Server 2019. The entire experience has been amazing and my client has been extremely happy with how everything ended up. Every single upgrade they had done before, they actually faced performance regression but this is the first time with a systematic process, I was able to help them with the optimal performance during the deployment process on production (or golive process as some call it). While working together we had a very interesting conversation about the Memory Grant Feedback and let me share that with you all here.

Memory Grant Feedback

SQL Server uses available memory for Hash Match and Sort operation. SQL Server estimates the number of rows from available statistics and predicts the memory needed for your query. When the query is executed, SQL Server eventually knows what is the actual amount of memory is needed. SQL Server is pretty good at estimating the need for memory but quite often it also gets it completed wrong. Memory Grant Feedback is the process where the SQL Server Engine gets feedback from the previously executed query and adjust the Memory Grant for the next query to be executed.

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

The goal of this entire exercise of memory grant feedback is to estimate the appropriate amount of memory for the query to avoid performance degradation.

Sample Script

Let us see a sample example of how memory grant feedback works. I will be using the sample database WideWorldImporters.

USE WideWorldImporters
SELECT ol.OrderID,ol.StockItemID,ol.Description,
o.Comments, o.CustomerID
FROM Sales.OrderLines ol
INNER JOIN Sales.Orders o ON ol.OrderID = o.OrderID
WHERE ol.StockItemID > @StockItemID
ORDER BY ol.Description, o.Comments

Now let us run the stored procedure first time and see the execution plan.

EXEC GetStockDetails 120

You may see in the execution plan there is a warning it says the SQL Server engine has overestimated the memory grant. Now let us run the same stored procedure one more time.

EXEC GetStockDetails 223

When you run it again and check the execution plan, you will notice that the warning is disappeared and the execution plan now automatically predicts the correct amount of the memory required by the query.

Well, this is the simplest example of how Memory Grant Feedback works. In future blog posts, we will inspect why this new feature actually created trouble for my client and how we overcome the situation.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Memory Grant, SQL Memory, SQL Scripts, SQL Server 2019
Previous Post
SQL SERVER – ColumnStore Index Displaying Actual Number of Rows To Zero
Next Post
SQL SERVER – Memory Grant Feedback – MemoryGrantInfo and IsMemoryGrantFeedbackAdjusted

Related Posts

6 Comments. Leave new

  • Hi Pinal,

    > When you run it again and check the execution plan, you will notice that the warning is disappeared and the execution plan now automatically predicts the correct amount of the memory required by the query.

    I continue to see excessive memory grants. Is there perhaps a setting at the database level to allow this type of feedback?

    Also, did you intend to change the input parameter from 120 to 223?


    • Please change it to the latest compatibility level.

      Yes, I intended to change the input parameter so the memory grant does not get settled.

  • Is this example only intended to work with SQL Server 2019? I was using SQL Server 2017 with compatibility level = 140, when trying out your sample.

    I even upgraded to the latest CU (I think it is 18, if memory serves correctly). Sorry, I’m not sitting at the computer at the moment, so I can’t quote exact numbers. The initial memory grant was very close to your example, and it dropped some, but it was still in the 500,000 KB range on each successive run, including after updating table stats!

  • I think you are saying that it should have worked as advertised, in SQL Server 2017, CU18 (Developer Edition), but I’m only seeing an initial drop (to something still in excess of 500,000 KB for a memory grant.

    Do you have access to this version to test, or perhaps another reader who might be reading this Q&A – if you are using the same version as me, what results are you getting back?


Leave a ReplyCancel reply

Exit mobile version