Row Mode Memory Grant Feedback in SQL Server 2019
Row Mode Memory Grant Feedback in SQL Server 2019
We can see a warning icon in the Select operator in the actual execution plan. If we hover the mouse over the operator, we can see the detailed warning message. In the properties section, we can see the detailed memory utilization. Notice the difference in the desired memory and the granted memory. We can see the warning message ‘ The query memory grant detected “ExcessiveGrant”, which may impact the reliability. Grant size: Initial 208800 KB, Final 208800 KB, Used 5880 KB.’ As per the error message, the query memory granted 203 MB while it used only 5.74 MB memory. We have granted excessive memory that might create performance issues in the highly OLTP system. If the excessive memory grant is appearing too very frequent, it might leave less space for the buffer cache, query plan etc. In SQL Server 2019, Microsoft is providing a further update to the intelligent query processing for such excessive memory grants. This feature was already available in the Azure SQL database. This feature is called ‘Row Memory Grant Feedback’. ‘Row mode memory grant feedback’ is an extension to the’ batch mode memory grant feedback’ feature in SQL Server 2017. This feature adjusts the memory grant sizes for both batch and row mode operator. We need to have a database with Compatibility level 150 in order to use this functionality. 1234 USE [master]GOALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 150GO Verify the database compatibility level is set to SQL Server 2019 (150) 12 Select Compatibility_level,name from sys.databaseswhere name='WideWorldImporters' Run the query that we executed, in the above example, for SQL Server 2017 and view the actual execution plan. You can notice the warning here “The query memory grant detected “ExcessiveGrant”, which may impact the reliability. Grant size: Initial 212200 KB, Final 212200 KB, Used 5872 KB.” We again got the excessive memory grant message in SQL Server 2019. Does it mean that there is no enhancement in SQL Server 2019 as well? We cannot see any improvement in SQL Server 2019 behavior also until now. Well, run query one more time and observe the behavior. There is no warning error message in the actual execution plan, this time, so let us see the detailed property of this select operator in the execution plan, We can see here that only 11.87 MB memory allocation as compared to 207 MB memory in the first execution. Click on the Select operator and view the property. You can notice that the Desired and the Granted memory are same. We get the following benefits from this memory grant feedback If SQL Server identifies that granted memory is more than the used memory, in next run memory grant feedback calculates the memory again, therefore, you can see less memory in further runs There might be scenarios related to the spill disk issue. In that case, also memory grant feedback helps to recalculate the memory and grant appropriate memory in further runs
Turn on the ROW_MODE_MEMORY_GRANT_FEEDBACK
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022
SQLShack
SQL Server training EspañolRow Mode Memory Grant Feedback in SQL Server 2019
December 6, 2018 by Rajendra Gupta In this article, I’ll be exploring another new feature with SQL Server 2019, row mode memory grant feedback, along with a retrospective on adaptive query processing, examples and more. With each version of SQL Server, Microsoft is providing enhancements to the query optimizer. In SQL Server 2017, we enjoyed the query processing improvements in terms of Adaptive Query processing. From time to time, we might face issues related to the cardinality estimates in execution plans that result in a Query performance issue. SQL Server 2017 Adaptive Query Processing helps to provide better cardinality estimation with the feedback mechanism. Using this, we can get a better execution plan, memory allotment, join selection etc. Below are the Adaptive Query Processing modes in SQL Server 2017 Interleaved Executions Batch Mode Memory Grant Feedback Batch Mode Adaptive Joins Before we move, we need to understand the issue that exists with the memory allocation. To demonstrate, we will use WideWorldImporters sample database in SQL Server 2019. Default Compatibility level is 150 in SQL Server 2019. Therefore, first, set the database compatibility level to SQL Server 2017 (level 140) for ‘ WideWorldImporters’ database. 123 USE [master]GOALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 140GO Run the below query and view the Actual execution plan in SQL Server Management Studio. 1234567 SELECT OD.CustomerID,OD.CustomerPurchaseOrderNumber, OD.InternalComments,OL.Quantity,OL.UnitPrice FROM [Sales].[Orders] ODINNER JOIN [Sales].[OrderLines] OLON OD.OrderID = OL.OrderIDORDER BY OD.[Comments]We can see a warning icon in the Select operator in the actual execution plan. If we hover the mouse over the operator, we can see the detailed warning message. In the properties section, we can see the detailed memory utilization. Notice the difference in the desired memory and the granted memory. We can see the warning message ‘ The query memory grant detected “ExcessiveGrant”, which may impact the reliability. Grant size: Initial 208800 KB, Final 208800 KB, Used 5880 KB.’ As per the error message, the query memory granted 203 MB while it used only 5.74 MB memory. We have granted excessive memory that might create performance issues in the highly OLTP system. If the excessive memory grant is appearing too very frequent, it might leave less space for the buffer cache, query plan etc. In SQL Server 2019, Microsoft is providing a further update to the intelligent query processing for such excessive memory grants. This feature was already available in the Azure SQL database. This feature is called ‘Row Memory Grant Feedback’. ‘Row mode memory grant feedback’ is an extension to the’ batch mode memory grant feedback’ feature in SQL Server 2017. This feature adjusts the memory grant sizes for both batch and row mode operator. We need to have a database with Compatibility level 150 in order to use this functionality. 1234 USE [master]GOALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 150GO Verify the database compatibility level is set to SQL Server 2019 (150) 12 Select Compatibility_level,name from sys.databaseswhere name='WideWorldImporters' Run the query that we executed, in the above example, for SQL Server 2017 and view the actual execution plan. You can notice the warning here “The query memory grant detected “ExcessiveGrant”, which may impact the reliability. Grant size: Initial 212200 KB, Final 212200 KB, Used 5872 KB.” We again got the excessive memory grant message in SQL Server 2019. Does it mean that there is no enhancement in SQL Server 2019 as well? We cannot see any improvement in SQL Server 2019 behavior also until now. Well, run query one more time and observe the behavior. There is no warning error message in the actual execution plan, this time, so let us see the detailed property of this select operator in the execution plan, We can see here that only 11.87 MB memory allocation as compared to 207 MB memory in the first execution. Click on the Select operator and view the property. You can notice that the Desired and the Granted memory are same. We get the following benefits from this memory grant feedback If SQL Server identifies that granted memory is more than the used memory, in next run memory grant feedback calculates the memory again, therefore, you can see less memory in further runs There might be scenarios related to the spill disk issue. In that case, also memory grant feedback helps to recalculate the memory and grant appropriate memory in further runs
Extended events for Row Mode Memory Grant feedback
We can view the ‘row mode memory grant feedback’ with the query_post_execution_showplan extended event. You can see the description in the bottom description section as ‘Occurs after a SQL statement is executed. This event returns an XML representation of the actual query plan. Using this event can create significant performance overhead so it should only be used when troubleshooting or monitoring specific problems for brief periods of time.’ You can find it in the execution category as shown here. 123456 CREATE EVENT SESSION [Monitor_memory_Grant_Feedback] ON SERVER ADD EVENT sqlserver.query_post_execution_showplan ADD TARGET package0.event_file(SET filename=N'Monitor_memory_Grant_Feedback')WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)GO In the ‘query_post_execution_showplan’ Xevent, we can find new attributes to show this memory grant feedback. Attribute Value Description IsMemoryGrantFeedbackAdjusted Yes or No Yes: Value – It shows that Memory grant feedback is used in the query. No: value- it shows that no memory grant feedback adjusted. We can find the value from the below table LastRequestedMemory Memory in KB Memory in KB. If it is first executed, the value is 0 We can see below values in the ‘IsMemoryGrantFeedbackAdjusted’ along with Yes or No. Value Description No First Execution In the first execution of the query, Memory grant feedback does not adjust memory. We have shown it above as well. No: Accurate Grant If there is no spill to disk and the statement uses at least 50% of the granted memory, then memory grant feedback is not triggered. No: Feedback disabled If there is a huge variation in the memory grant in subsequent runs, the system disables the memory grant feedback for the query. Yes Adjusting It shows that Memory grant feedback is in place and it may continue for the next runs as well. Yes Stable If the system identifies that granted memory is stable and the memory allocated is the same as of previous execution, you can see this status.No First Execution
You can see in the XML execution plan, for the first execution 1234 IsMemoryGrantFeedbackAdjusted="No First Execution">Yes Adjusting
In the next runs, we can see that it adjusts the granted memory but there is still a scope of adjustments. Therefore, we can see the values ‘Yes Adjusting’ 123Yes Stable
Run the statement few times and we will see stable value in the IsMemoryFeedbackAdjusted attributes of the XML execution plan under MemoryGrantinfo section. 123Database Scoped Configurations
Sometimes we might want to apply ‘row mode memory grant feedback’ for our workloads. In this case, we can control this behaviour by using the Database Scoped Configurations. Go to the database properties -> Options. In the database-scoped configurations, there is no option added to turn it on or off. Although, we can turn it on or off using the below query. 1 ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = [OFFON]; Turn off the ROW_MODE_MEMORY_GRANT_FEEDBACKTurn on the ROW_MODE_MEMORY_GRANT_FEEDBACK
Conclusion
Row mode memory grant feedback (ROW_MODE_MEMORY_GRANT_FEEDBACK) is a nice enhancement in SQL Server 2019 to resolve the excessive memory grant issues. Author Recent Posts Rajendra GuptaHi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022