SQL SERVER – Memory Grant Feedback – No Feedback Disabled

From the last few days, I am sharing my experience with the Memory Grant Feedback feature of SQL Server 2019. I love my job as I get to learn various new technology while working on Comprehensive Database Performance Health Check. Today we will discuss a very interesting topic of MemoryGrantInfo when the value of the IsMemoryGrantFeedbackAdjusted property displays No Feedback Disabled.

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

Demonstration – No Feedback Disabled

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 with different values. However, before you run the stored procedure, enable the actual execution plan by typing CTRL +M or following this blog post.

Now run the following stored procedures 20 times. If you notice there are two different values for a stored procedure.

EXEC GetStockDetails 120
EXEC GetStockDetails 223
GO 20

Please note that I am running the stored procedures 20 times so there will be a total of 40 executions. Now check the execution plan of 31, 32 and 33rd execution plan. You will notice that on the 33rd execution plan there is a different value in the Memory Grant Feedback. You will see as we run the above-stored procedure the memory requirements keep on changing and at one point SQL Server Engine gives up and says it is going to use one of the initial memory grants and uses the memory grant of the very first execution. As you will notice in our case that would be very horrible.

Well, the learning from this exercise is that while the feature of Memory Grant Feedback is awesome, it is can backfire as well and give you poor performance. We will see how to identify the queries which have this feature disable and also how to disable the feature entirely in future blog posts.

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