SQL SERVER – Memory Grant Feedback – MemoryGrantInfo and IsMemoryGrantFeedbackAdjusted

This is the follow-up blog post of my earlier blog post SQL SERVER – Introduction to Memory Grant Feedback. I just helped the customer gain their lost performance of the SQL Server 2019 while working on Comprehensive Database Performance Health Check. What I enjoy the most during my consulting engagement is that I enjoy sharing the learning with everyone. Today we will see a very interesting behavior of Memory Grant Feedback. We will discuss a very interesting topic of MemoryGrantInfo and IsMemoryGrantFeedbackAdjusted in the execution plan.

SQL SERVER - Memory Grant Feedback - MemoryGrantInfo and IsMemoryGrantFeedbackAdjusted memorygrant0-800x142

To understand the basics of the Memory Grant Feedback, I suggest you read this blog post: Introduction to Memory Grant Feedback. We will be using the same query which we had used in the earlier blog post.

Demonstration – MemoryGrantInfo & IsMemoryGrantFeedbackAdjusted

First, create the following stored procedure in the WideWorldImporters sample database.

USE WideWorldImporters
GO
CREATE OR ALTER PROCEDURE GetStockDetails (@StockItemID INT)
AS
SELECT ol.OrderID,ol.StockItemID,ol.Description,
ol.OrderLineID,
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
GO

Now we will be running the above-stored procedure multiple times. Here is how you can run the stored procedure the first time. However, before you run the stored procedure, enable the actual execution plan by typing CTRL +M or following this blog post.

EXEC GetStockDetails 120

Now go to execution plan and click on the left-most SELECT operator. Right Click over it and go to the property. Now this will open the separate property window on the right slide. In this window further example the property MemoryGrantInfo.

SQL SERVER - Memory Grant Feedback - MemoryGrantInfo and IsMemoryGrantFeedbackAdjusted memorygrant

Right under this property, there will be many different properties. Now pay attention to the property – IsMemoryGrantFeedbackAdjusted.

Over here, when you execute the stored procedure first time you will find the following details in the column IsMemoryGrantFeedbackAdjusted – NoFirstExecution. That means, the query has ran the first time and there is no history available for adjusting the memory.

SQL SERVER - Memory Grant Feedback - MemoryGrantInfo and IsMemoryGrantFeedbackAdjusted memorygrant1

Now let us run the stored procedure again the second time and observe the same property. This time you will notice that the property detail is now changed to YesAdjusting. This means the memory grant feedback is now taking feedback from the earlier execution and adjusting it for the next execution.

SQL SERVER - Memory Grant Feedback - MemoryGrantInfo and IsMemoryGrantFeedbackAdjusted memorygrant2

Now let us run it once again and you will see the status of the property once again change to YesStable. This means the memory grant has found the most optimal value and has applied to the query.

SQL SERVER - Memory Grant Feedback - MemoryGrantInfo and IsMemoryGrantFeedbackAdjusted memorygrant3

How if you run the query with the different parameter which is vastly different from the earlier value, it can also give you once again the older status of the YesAdjusting. You can check that by running the query for the different parameters as described here.

EXEC GetStockDetails 223

SQL SERVER - Memory Grant Feedback - MemoryGrantInfo and IsMemoryGrantFeedbackAdjusted memorygrant4

There is also two more value for the IsMemoryGrantFeedbackAdjusted, which we have yet not discussed. One of them is NoAccurateGrant. This means there was no need for an additional memory grant and there was no spill on the disk. Additionally, in some scenarios, we will also see the status of NoFeedbackDisabled. This happens when SQL Server Engine is not able to figure out what is the best possible memory grant for the query and stop adjusting the memory grant after a few attempts (32 to be precise).

This happens in a very interesting situation that actually happened to my client’s place and I will separately blog about it in the future.

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

Execution Plan, Memory Grant, SQL Memory, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Introduction to Memory Grant Feedback
Next Post
SQL SERVER – Memory Grant Feedback – No Feedback Disabled

Related Posts

Leave a Reply