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.
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.
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.
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.
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.
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
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)
