Public Preview of Row Mode Memory Grant Feedback in Azure SQL Database

  • Article

Last year SQL Server 2017 and Azure SQL Database introduced query processing improvements that adapt optimization strategies to your application workload’s runtime conditions. These improvements included: batch mode adaptive joins, batch mode memory grant feedback, and interleaved execution for multi-statement table valued functions.

In Azure SQL Database, we are further expanding query processing capabilities with several new features under the Intelligent Query Processing (QP) feature family.  In this blog post we’ll discuss one of these Intelligent QP features that is now available in public preview, row mode memory grant feedback.  Row mode memory grant feedback expands on the memory grant feedback feature by adjusting memory grant sizes for both batch and row mode operators.

Key feature benefits:

  • Reduce wasted memory. For an excessive memory grant condition, if the granted memory is more than two times the size of the actual used memory, memory grant feedback will recalculate the memory grant. Consecutive executions will then request less memory.
  • Decrease spills to disk. For an insufficiently sized memory grant that results in a spill to disk, memory grant feedback will trigger a recalculation of the memory grant. Consecutive executions will then request more memory.

To enable the public preview of row mode memory grant feedback in Azure SQL Database, enable database compatibility level 150 for the database you are connected to when executing the query:

 ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 150;

As with batch mode memory grant feedback, row mode memory grant feedback activity will be visible via the memory_grant_updated_by_feedback XEvent. We are also introducing two new query execution plan attributes for better visibility into the current state of a memory grant feedback operation for both row and batch mode.  As of the time of this writing, the attributes are not visible in SQL Server Management Studio graphical query execution plans, but for early testing you can view them using SET STATISTICS XML ON or the query_post_execution_showplan XEvent.  The two new attributes are IsMemoryGrantFeedbackAdjusted and LastRequestedMemory added to the MemoryGrantInfo query plan XML element:

The new LastRequestedMemory attribute shows the granted memory in Kilobytes (KB) from the prior query execution. The new IsMemoryGrantFeedbackAdjusted attribute allows you to check the state of memory grant feedback for the statement within an actual query execution plan. Values surfaced in this attribute are as follows:

IsMemoryGrantFeedbackAdjusted Value Description
No: First Execution Memory grant feedback does not adjust memory for the first compile and associated execution.
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 memory grant feedback is continually triggered and fluctuates between memory-increase and memory-decrease operations, we will disable memory grant feedback for the statement.
Yes: Adjusting Memory grant feedback has been applied and may be further adjusted for the next execution.
Yes: Stable Memory grant feedback has been applied and granted memory is now stable, meaning that what was last granted for the previous execution is what was granted for the current execution.

We hope that you have an opportunity to test this new feature! If you have feedback on this feature or other features in the Intelligent QP feature family, please email us at IntelligentQP@microsoft.com.

Thanks!