SQL Server 2019中的行模式内存授予反馈

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.

在本文中，我将探索SQL Server 2019的另一个新功能，行模式内存授予反馈以及对自适应查询处理的回顾，示例等。

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.

对于每个版本SQL Server，Microsoft都对查询优化器进行了增强。 在SQL Server 2017中，我们在自适应查询处理方面享受了查询处理方面的改进。 有时，我们可能会在执行计划中面临与基数估计有关的问题，从而导致查询性能问题。 SQL Server 2017自适应查询处理通过反馈机制帮助提供更好的基数估计。 使用此方法，我们可以获得更好的执行计划，内存分配，联接选择等。

Below are the Adaptive Query Processing modes in SQL Server 2017

以下是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.

在继续之前，我们需要了解内存分配存在的问题。 为了演示，我们将在SQL Server 2019中使用WideWorldImporters示例数据库。

USE [master]GO
ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 140
GO

SELECT 
  OD.CustomerID,OD.CustomerPurchaseOrderNumber,
  OD.InternalComments,OL.Quantity,OL.UnitPrice
  FROM [Sales].[Orders] OD
INNER JOIN [Sales].[OrderLines] OL
ON OD.OrderID = OL.OrderID
ORDER 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.

在实际执行计划的Select运算符中，我们可以看到一个警告图标。 如果将鼠标悬停在操作员上方，我们将看到详细的警告消息。

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.’

我们可以看到警告消息“查询内存授权检测到“ ExcessiveGrant”，这可能会影响可靠性。 赠款大小：初始208800 KB，最终208800 KB，已用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.

根据错误消息，查询内存授予203 MB内存，而仅使用5.74 MB内存。 我们已授予过多的内存，这可能会在高度OLTP系统中造成性能问题。 如果过多的内存授予显得过于频繁，则可能会为缓冲区高速缓存，查询计划等留出较少的空间。

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’.

在SQL Server 2019中，Microsoft针对此类过多的内存授予为智能查询处理提供了进一步的更新。 Azure SQL数据库中已提供此功能。 此功能称为“行内存授予反馈”。

‘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.

“行模式内存授予反馈”是对SQL Server 2017中“批处理模式内存授予反馈”功能的扩展。此功能为批处理和行模式运算符调整内存授予大小。

We need to have a database with Compatibility level 150 in order to use this functionality.

为了使用此功能，我们需要具有兼容级别150的数据库。

USE [master]
GO
ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 150
GO

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

验证数据库兼容性级别设置为SQL Server 2019（150）

Select Compatibility_level,name  from sys.databases
where name='WideWorldImporters'

Run the query that we executed, in the above example, for SQL Server 2017 and view the actual execution plan.

在上例中，运行我们对SQL Server 2017执行的查询，并查看实际的执行计划。

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.”

您可以在此处看到警告，“查询内存授予检测到” ExcessiveGrant”，这可能会影响可靠性。 赠款大小：初始212200 KB，最终212200 KB，已用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?

我们再次在SQL Server 2019中收到了过多的内存授予消息。这是否意味着SQL Server 2019中也没有增强功能？

We cannot see any improvement in SQL Server 2019 behavior also until now. Well, run query one more time and observe the behavior.

直到现在我们也看不到SQL Server 2019行为的任何改善。 好了，再运行一次查询并观察行为。

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,

这次，在实际的执行计划中没有警告错误消息，因此让我们在执行计划中查看此select运算符的详细属性，

We can see here that only 11.87 MB memory allocation as compared to 207 MB memory in the first execution.

我们在这里可以看到，与第一次执行时的207 MB内存相比，只有11.87 MB的内存分配。

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
  如果SQL Server识别出已授予的内存大于已使用的内存，则在下一次运行中，内存授予反馈会再次计算该内存，因此，您可以在以后的运行中看到较少的内存
• 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

我们可以使用query_post_execution_showplan扩展事件查看“行模式内存授予反馈”。 您可以在底部描述部分中看到该描述，如下所示：

• ‘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.’
• '在执行SQL语句后发生。 此事件返回实际查询计划的XML表示形式。 使用此事件可能会产生大量的性能开销，因此仅在短期内对故障进行故障排除或监视时才应使用此事件。
  
  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)
GO

In the ‘query_post_execution_showplan’ Xevent, we can find new attributes to show this memory grant feedback.

在’query_post_execution_showplan’Xevent中，我们可以找到新属性来显示此内存授予反馈。

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

属性	值	描述
IsMemoryGrantFeedback已调整	是还是不是	是：值–显示查询中使用了内存授予反馈。 否：值-表示未调整任何内存授权反馈。 我们可以从下表中找到该值
LastRequestedMemory	内存（KB）	内存（KB）。 如果是第一次执行，则值为0

We can see below values in the ‘IsMemoryGrantFeedbackAdjusted’ along with Yes or No.

我们可以在“ IsMemoryGrantFeedbackAdjusted”中看到以下值以及是或否。

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.

值	描述
否：第一次执行	在查询的第一次执行中，“内存授予”反馈不会调整内存。 我们也在上面显示了它。
否：准确授予	如果没有溢出到磁盘，并且该语句使用了至少50％的已授权内存，则不会触发内存授权反馈。
否：反馈已禁用	如果在随后的运行中内存授权有很大的不同，则系统将禁用查询的内存授权反馈。
是：调整	它表明内存授予反馈已经到位，并且在下一次运行中也可能会继续。
是：稳定	如果系统确定授予的内存是稳定的，并且分配的内存与先前执行的内存相同，则可以看到此状态。

否：第一次执行 (No: First Execution)
You can see in the XML execution plan, for the first execution

您可以在XML执行计划中看到第一次执行

IsMemoryGrantFeedbackAdjusted="No: First Execution"></
LastRequestedMemory="0"
 
<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>

是：调整 (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’

在下一次运行中，我们可以看到它调整了授予的内存，但是仍然存在调整范围。 因此，我们可以看到“是：正在调整”值

<MemoryGrantInfo SerialRequiredMemory="2048" SerialDesiredMemory="4000" 
requiredMemory="2048" DesiredMemory="4000" 
RequestedMemory="4000" GrantWaitTime="0" 
GrantedMemory="4000" MaxUsedMemory="560" MaxQueryMemory="707568"LastRequestedMemory="4912"
IsMemoryGrantFeedbackAdjusted="Yes: Adjusting"></MemoryGrantInfo>

是：稳定 (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.

几次运行该语句，我们将在MemoryGrantinfo部分的XML执行计划的IsMemoryFeedbackAdjusted属性中看到稳定值。

<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

关闭ROW_MODE_MEMORY_GRANT_FEEDBACK

Turn on the ROW_MODE_MEMORY_GRANT_FEEDBACK

开启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.

行模式内存授予反馈（ ROW_MODE_MEMORY_GRANT_FEEDBACK ）是SQL Server 2019中的一项很好的增强功能，可以解决过多的内存授予问题。

翻译自: https://www.sqlshack.com/row-mode-memory-grant-feedback-sql-server-2019/