In our previous posts of Memory Grant Internals, we have learned all about the memory grant internals. We have learned from where do they come from and how SQL Server will manage the memory for the memory grants.
In this series, we will learn all about SQL Server 2019 Intelligent Query processing.
Intelligent query processing is a set of features which helps improve the performance of the queries with very less effort in implementation. Below flow chart shows all the features that were released in SQL Server 2017 and SQL Server 2019 related to Intelligent query processing. In this series of posts, lets learn about each Intelligent query processing feature released in SQL Server 2019.
In this part, let us focus on the Memory Grant Feedback feature released in SQL Server 2017 for Batch mode execution and in SQL Server 2019 for Row mode execution.
Memory Grants are used by the SQL Server for the Hashes and Sort operations. SQL Server optimizer uses the statistics information and allocate the memory needed by the query before the query executes. When the query is executed, SQL Server uses the allocated memory to process the query for the hashes and sorts. If this memory grant is not enough to process the query, data will use tempdb spilling to disk. When too much memory is allocated based up on the estimates, we can effect the concurrency as all other queries requires memory grants to process the queries as well. Bad estimates can effect the memory grants allocated to the queries. Too much or too little memory grants is bad.
Now, how do we solve this issue?
By making sure we maintain the statistics up to date. Sometimes, we may see the queries spilling to disk due to low memory grant allocation than needed, in this case its really not possible to stop queries spilling to disk if we are in SQL Server 2016 or below versions. If you are on SQL Server 2019, Memory Grant Feedback is enabled on databases by default.
Memory Grant Feedback feature will collect the information of the memory that has actually spilled to disk and add that memory to the next executions of the query plus a possible buffer. In reverse if more memory grants are allocated to the queries than needed, Memory Grant Feedback feature will save the information in the query plan of how much memory has not being used and decrease that memory to the next executions plus a possible buffer. This feature adjust these grants as needed by the query.
Memory Grant Feedback is really helpful as this feature is especially to estimate the necessary amount of memory grants needed for the query to run without any performance degradation.
Let’s see how the queries work without Memory Grant feedback. As my database is in SQL Server 2019 compatibility mode, lets turn off the feature first, create a stored procedure and execute the stored procedure with a date range values for a year. Check for the actual execution plan.
ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF; GO DROP PROCEDURE IF EXISTS [Sales].[customerdata_OrderDate]; GO CREATE OR ALTER PROCEDURE [Sales].[customerdata_OrderDate] @StartDate DATETIME, @EndDate DATETIME AS SELECT oh.[CustomerID], oh.[OrderDate], oh.[ShipMethodID], od.[OrderQty], od.[ProductID] FROM [Sales].[SalesOrderHeaderEnlarged] oh JOIN [Sales].[SalesOrderDetailEnlarged] od ON oh.SalesOrderID = od.salesOrderID WHERE [OrderDate] >= @StartDate AND [OrderDate] <= @EndDate ORDER BY [OrderDate]; GO DECLARE @StartDate DATETIME = '2012-01-01' DECLARE @EndDate DATETIME = '2013-03-31' EXEC [Sales].[customerdata_OrderDate] @StartDate, @EndDate; GO
When you observe the actual execution plan, you see the warning that sort operator used tempdb to spill the data and wrote 2234 pages to disk.
SQL Server allocated memory grant for this query is not sufficient so it had to spill to disk. As the memory is granted to any query before execution, SQL Server cannot grant the memory on the fly during the query execution. This results in sorting data in tempdb which is I/O intensive. This causes queries to run very slow impacting query performance.
Now let’s enable the Memory grant feature to ON and run the query again. Check actual execution plan.
ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = ON; GO
Sort did not spill to disk this time. Look at the Memory Grant Feedback info from the properties. IsMemoryGrantFeedbackAdjusted shows as ‘NoFirstexecustion’. This is because we executed the stored procedure for the first time after we enabled the memory grant feedback feature on the database and that is the reason we also see LastrequestedMemory as 0. The requested memory shows as 117704KB.
Let’s run the same query one more time and see the IsMemoryGrantFeedbackAdjusted status. Now the status shows as ‘YesAdjusting’. The last requested memory is 117704KB which is exact memory we have seen for our last execution RequestedMemory. This time the RequestedMemory is 75016KB.
Let’s run the query once again and see the status. Now the status shows as ‘YesStable’. The last requested memory is 75016KB which is exact memory we have seen for our last execution RequestedMemory. This time the RequestedMemory is the same 75016KB as now the status shows as stable.
Isn’t it wonderful? OH YES!! but we need to remember few things here. What if we have lot of skew in our data and if the results drastically differ with each variable? Like for example, if your stored procedure gives 1000 rows with a variable and 1 million rows for different variable. If you have to execute the store procedure frequently, then the memory grant feedback tries to adjust itself to allocate the memory required by the query but as these results differ in many rows, memory grant feedback cannot adjust itself, it goes back and forth changing the memory grants and eventually turns itself OFF.
To describe this scenario, lets take an example of the same query we executed before but this time we run two different executions. First execution of the stored procedure with one year date range results shows as 600,000 rows and second query results in 6,000 rows. Let us the run these queries 20 times. Total 40 executions.
EXEC [Sales].[customerdata_OrderDate] '2012-01-01', '2013-03-31' go 20 EXEC [Sales].[customerdata_OrderDate] '2012-01-01', '2012-01-08' go 20
On the 22nd execution, I see the Memory Grant feedback info status shows as ‘YesAdjusting’ but when you check the 23rd execution, status shows as ‘NoFeedbackDisabled’. Once it shows as disabled, that means the memory grant feedback is turned off for this execution plan.
Points to Remember: Feedback will be change only the Cached plan. For the first execution, the memory grant feedback information will be stored inside the cached plan. In the next executions of the same query, the benefits of the feedback adjustments will be used. Feedback information is not captured in the query store. If there is memory pressure or if in any case the cached plan is evicted out of the cache, feedback will be lost for that evicted plan. If there is a failover, in that case feedback will be lost.
Note: Memory Grant Feedback is not the replacement for the out of date statistics.
Summary: Memory Grant Feedback feature is really useful in scenarios like pulling reports every couple of months with different variables. If you have lot of skew in your data, results differ a lot with each variable and you need to run the query very frequently, in that case Memory Grant feedback may not be helpful as the feature will turn itself OFF at certain point of time while trying to adjust itself. We need to make sure our statistics are up to date and we maintain them regularly.
Thank you for Reading!