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.123USE [master]GOALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 140GO
Run the below query and view the Actual execution plan in SQL Server Management Studio.1234567SELECTOD.CustomerID,OD.CustomerPurchaseOrderNumber,OD.InternalComments,OL.Quantity,OL.UnitPriceFROM [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.
ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 150
Verify the database compatibility level is set to SQL Server 2019 (150)
Select Compatibility_level,name from sys.databases
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.
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)
In the ‘query_post_execution_showplan’ Xevent, we can find new attributes to show this memory grant feedback.
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
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.
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.
It shows that Memory grant feedback is in place and it may continue for the next runs as well.
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
IsMemoryGrantFeedbackAdjusted="No: First Execution"></
<MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="880" RequiredMemory="512" DesiredMemory="880" RequestedMemory="1024" GrantWaitTime="0" GrantedMemory="1024" MaxUsedMemory="0" MaxQueryMemory="707568" LastRequestedMemory="0" IsMemoryGrantFeedbackAdjusted="No: First Execution"></MemoryGrantInfo>
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’
<MemoryGrantInfo SerialRequiredMemory="2048" SerialDesiredMemory="4000" requiredMemory="2048" DesiredMemory="4000"
RequestedMemory="4000" GrantWaitTime="0" GrantedMemory="4000" MaxUsedMemory="560" MaxQueryMemory="707568"LastRequestedMemory="4912"
Run the statement few times and we will see stable value in the IsMemoryFeedbackAdjusted attributes of the XML execution plan under MemoryGrantinfo section.
<MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="6912" RequiredMemory="2816" DesiredMemory="9216" RequestedMemory="9216" GrantWaitTime="0"
GrantedMemory="9216" MaxUsedMemory="6184" MaxQueryMemory="684224"
LastRequestedMemory="9216" IsMemoryGrantFeedbackAdjusted="Yes: Stable"></MemoryGrantInfo>
Database 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.
ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = [OFF|ON];
- Turn off the ROW_MODE_MEMORY_GRANT_FEEDBACK
- Turn on the ROW_MODE_MEMORY_GRANT_FEEDBACK
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.
While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at email@example.com
View all posts by Rajendra Gupta