Rajendra Gupta

Row Mode Memory Grant Feedback in SQL Server 2019

December 6, 2018 by

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

  1. Interleaved Executions
  2. Batch Mode Memory Grant Feedback
  3. 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.
  • Run the below query and view the Actual execution plan in SQL Server Management Studio.

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.

Verify the database compatibility level is set to SQL Server 2019 (150)

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.

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

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’

Yes: Stable

Run the statement few times and we will see stable value in the IsMemoryFeedbackAdjusted attributes of the XML execution plan under MemoryGrantinfo section.

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.

  • Turn off the ROW_MODE_MEMORY_GRANT_FEEDBACK

  • Turn 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.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)

Related posts:

  1. SQL Server 2017: SQL Sort, Spill, Memory and Adaptive Memory Grant Feedback
  2. Adaptive Query Processing in SQL Server 2017
  3. Memory-Optimized TempDB Metadata in SQL Server 2019
  4. The new SQL Server 2019 function Approx_Count_Distinct
  5. SQL Table Variable Deferred Compilation in SQL Server 2019
168 Views