First published on MSDN on Jul 16, 2018
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!