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