内存授予反馈

项目
02/01/2024

适用于：SQL Server 2017 (14.x) 及更高版本、Azure SQL 托管实例、Azure SQL 数据库

有时，查询执行时授予的内存太大或太小。如果内存授予太大，我们将禁止服务器上的并行性。如果过小，我们可能会溢出到磁盘，这是一项开销很大的操作。内存授予反馈尝试记住先前执行的内存需求（使用百分位反馈、多次过去执行）。根据此历史查询信息，内存授予反馈会相应地调整给予查询的授予，以用于后续执行。

此功能已发布三次。批处理模式内存授予反馈，然后是行模式内存授予反馈，SQL Server 2022 (16.x) 引入了使用查询存储的磁盘持久性内存授予反馈，以及一种称为百分位数授予的改进算法。

注意

有关其他查询反馈功能，请参阅基数估算 (CE) 反馈和并行度 (DOP) 反馈。

批处理模式内存授予反馈

适用于：SQL Server（从 SQL Server 2017 (14.x) 开始）和 Azure SQL 数据库

查询的执行计划包括执行所需的最小内存和能将所有行纳入内存的理想内存授予大小。如果内存授予大小不正确，性能将受到影响。如果授予过量，则会导致内存浪费，减少并发执行。如果内存授予不足，则会导致到磁盘的昂贵溢出。通过解决重复工作负荷，批处理模式内存授予反馈可重新计算查询所需的实际内存，并更新缓存计划的授予值。执行相同的查询语句时，查询将使用修改后的内存授予大小，减少影响并发的过量内存授予，并修复造成到磁盘的昂贵溢出的估计不足的内存授予。

下图是使用批处理模式自适应内存授予反馈的一个示例。对于首次执行查询，由于高溢出，持续时间为 88 秒：

DECLARE @EndTime datetime = '2016-09-22 00:00:00.000';
DECLARE @StartTime datetime = '2016-09-15 00:00:00.000';

SELECT TOP 10 hash_unique_bigint_id
FROM dbo.TelemetryDS
WHERE Timestamp BETWEEN @StartTime AND @EndTime
GROUP BY hash_unique_bigint_id
ORDER BY MAX(max_elapsed_time_microsec) DESC;

Graph of granted versus spilled MBs of memory, indicating high spills.

启用内存授予反馈后，对于第二次执行，持续时间为 1 秒（从 88 秒减少），完全消除溢出，且授予内存更高：

Graph of granted versus spilled MBs of memory, indicating no spills.

内存授予反馈大小调整

对于内存授予过量的情况，如果授予的内存是实际使用内存大小的两倍，内存授予反馈将重新计算内存授予并更新缓存的计划。内存授予不足 1 MB 的计划将不会针对超额重新进行计算。

对于内存授予大小不足，造成批处理模式运算符溢出到磁盘的情况，内存授予反馈将触发内存授予的重新计算。溢出事件会报告给内存授予反馈，并且可通过 spilling_report_to_memory_grant_feedback 扩展事件显示出来。此事件将返回计划的节点 ID 和该节点溢出的数据大小。

调整后的内存授予通过 GrantedMemory 属性显示在实际（执行后）计划中。

可以在图形显示计划的根运算符或显示计划 XML 输出中看到此属性：

<MemoryGrantInfo SerialRequiredMemory="1024" SerialDesiredMemory="10336" RequiredMemory="1024" DesiredMemory="10336" RequestedMemory="10336" GrantWaitTime="0" GrantedMemory="10336" MaxUsedMemory="9920" MaxQueryMemory="725864" />

要使工作负载自动符合此改进，请为数据库启用兼容性级别 140。

示例：

ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 140;

内存授予反馈和参数敏感型方案

为保持最优，不同的参数值可能还需要不同的查询计划。此类查询被定义为“参数敏感型”。

对于参数敏感型计划，如果查询具有不稳定的内存要求，则内存授予反馈对该查询禁用。多次重复运行查询后，内存授予反馈功能将被禁用，这可以通过监视 memory_grant_feedback_loop_disabled 扩展事件来观察。 SQL Server 2022 (16.x) 中引入的内存授予反馈的持久性和百分位数模式可以缓解这种情况。内存授予反馈的持久性功能要求在数据库中启用查询存储，并将其设置为“read write”模式。

有关参数截取和参数敏感度的详细信息，请参阅查询处理体系结构指南。

内存授予反馈缓存

反馈可以存储在单个执行的缓存计划中。它是该语句的连续执行，但受益于内存授予反馈调整。此功能适用于重复执行语句。内存授予反馈将只更改缓存的计划。在 SQL Server 2022 (16.x) 之前，查询存储中不会捕获更改。

如果从缓存中逐出计划，则不会保存反馈。如果存在故障转移，反馈也会丢失。使用 OPTION (RECOMPILE) 的语句可创建新的计划，但不会缓存它。由于它未被缓存，因此不会产生内存授予反馈，也不会针对编译和执行存储。但如果缓存了未使用 OPTION (RECOMPILE) 的等效语句（即具有相同查询哈希），然后重新执行，则第二次及以后的连续执行将受益于内存授予反馈。

跟踪内存授予反馈活动

可以使用 memory_grant_updated_by_feedback 扩展事件跟踪内存授予反馈事件。此事件可跟踪当前执行计数历史记录，内存授予反馈更新计划的次数，修改前理想的额外内存授予，以及内存授予反馈修改缓存计划后理想的额外内存授予。

内存授予反馈、资源调控器和查询提示

实际内存授予服从资源调控器或查询提示确定的查询内存限制。

在不更改兼容性级别的情况下禁用批处理模式内存授予反馈

可在数据库或语句范围内禁用内存授予反馈，同时将数据库兼容级别维持在 140 或更高。若要对源自数据库的所有查询执行禁用批处理模式内存授予反馈，请在对应数据库的上下文中执行 SQL 语句：

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

-- Starting with SQL Server 2019, and in Azure SQL Database
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;

启用后，此设置在 sys.database_scoped_configurations 将显示为已启用。

若要对源自数据库的所有查询执行重新启用批处理模式内存授予反馈，请在对应数据库的上下文中执行 SQL 语句：

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;

-- Azure SQL Database, SQL Server 2019 and higher
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

此外，将 DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK 指定为 USE HINT 查询提示也可为特定查询禁用批处理模式内存授予反馈。例如：

SELECT * FROM Person.Address
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT ('DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK'));

USE HINT 查询提示的优先级高于数据库范围的配置或跟踪标志设置。

行模式内存授予反馈

适用于：SQL Server（从 SQL Server 2019 (15.x) 开始）和 Azure SQL 数据库

通过调整批处理模式和行模式运算符的内存授予大小，行模式内存授予反馈扩展了批处理模式内存授予反馈功能。

要在 Azure SQL 数据库中启用行模式内存授予反馈，请为执行查询时连接到的数据库启用数据库兼容性级别 150 或更高级别。

示例：

ALTER DATABASE [<database name>] SET COMPATIBILITY_LEVEL = 150;

与批处理模式内存授予反馈一样，行模式内存授予反馈活动通过 memory_grant_updated_by_feedback XEvent 可见。我们还引入了两个新的查询执行计划属性，以便更好地了解行和批处理模式下的内存授予反馈操作的当前状态。

内存授予反馈不需要查询存储，但 SQL Server 2022 (16.x) 中引入的持久性改进要求为数据库启用查询存储并处于“read write”状态。有关持久性的详细信息，请参阅本文后面的百分位数和持久性模式内存授予反馈。

行模式内存授予反馈活动通过 memory_grant_updated_by_feedback 扩展事件可见。

从行模式内存授予反馈开始，为实际的执行后计划显示了两个新的查询计划属性：IsMemoryGrantFeedbackAdjusted 和 LastRequestedMemory，这两个属性将添加到 MemoryGrantInfo 查询计划 XML 元素中。

LastRequestedMemory 属性显示上次查询执行时的授予内存 (KB)。
使用 IsMemoryGrantFeedbackAdjusted 属性，可以查看实际查询执行计划内语句的内存授予反馈状态。

下面列出了此属性的可取值：

`IsMemoryGrantFeedbackAdjusted` 值	说明
No: First Execution	内存授予反馈不调整用于首次编译和相关执行的内存。
No: Accurate Grant	如果没有溢出到磁盘，且语句使用至少 50% 的授予内存，就不会触发内存授予反馈。
No: Feedback disabled	如果内存授予反馈不断触发，且在内存增加和内存减少操作之间波动，数据库引擎就会对语句禁用内存授予反馈。
Yes: Adjusting	内存授予反馈已应用，并且可能会针对下一次执行进行进一步调整。
是：百分位数调整	使用百分位授予算法应用内存授予反馈，该算法不仅查看最近的执行，还有更多的历史记录。
Yes: Stable	内存授予反馈已应用，并且授予内存现在处于稳定状态。也就是说，为上一次执行最后授予的内存是为当前执行授予的内存。

百分位数和持久性模式内存授予反馈

适用于：SQL Server（从 SQL Server 2022 (16.x) 开始）和 Azure SQL 数据库

此功能是在 SQL Server 2022 (16.x) 中引入的，但是此性能增强适用于在数据库兼容性级别 140（在 SQL Server 2017 中引入）或更高版本或 140 及更高版本的 QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n 提示中运行的查询，并且当数据库已启用查询存储并处于“read write”状态时。

默认情况下，在 SQL Server 2022 (16.x) 中启用百分位内存授予反馈，但如果未启用查询存储或查询存储未处于“read write”状态，则不会产生任何影响。
默认情况下，内存授予、CE 和 DOP 反馈的持久性在 SQL Server 2022 (16.x) 中处于启用状态，但在未启用查询存储或查询存储未处于“read write”状态时不起作用。
Azure SQL 数据库中提供了内存授予反馈的百分位数和持久性，并在所有数据库（包括现有数据库和新数据库）中默认启用。
Azure SQL 托管实例中当前未提供内存授予反馈的百分位数和持久性。

建议在为数据库启用该功能之前为工作负载设置性能基线。该基线数字将帮助你确定是否从该功能中获得预期好处。

内存授予反馈 (MGF) 是一项现有功能，可根据过去的性能调整为查询分配的内存大小。但是，此项目的初始阶段仅将内存授予调整与计划一起存储在缓存中 – 如果计划从缓存中逐出，反馈过程必须再次启动，导致在逐出后执行查询的前几次性能不佳。新的解决方案是将授予信息与其他查询信息一起保留在查询存储中，以便在缓存逐出期间持续受益。内存授予反馈持久性和百分位数以非侵入性方式解决内存授予反馈的现有限制。

此外，授予大小调整仅负责最近使用的授予。因此，如果参数化查询或工作负载每次执行都需要差异巨大的内存授予大小，则最新的授予信息可能不准确。它可能不符合正在执行的查询的实际需求。在此应用场景中，内存授予反馈对性能没有帮助，因为我们总是根据上次使用的授予值来调整内存。下图显示了在没有百分位数和持久性模式的情况下使用内存授予反馈可能出现的行为。

Graph of granted versus actual needed memory behavior in Memory Grant feedback without percentile and persistence mode memory grant feedback.

正如你所看到的，在这种异常但可能出现的查询行为中，如果查询执行本身在内存量方面发生交替，实际所需和授予的内存量之间的振荡会导致浪费和不足。在此应用场景中，内存授予反馈会自行禁用，因为它意识到这样做弊大于利。

对查询的最近历史记录使用基于百分位数的计算，而不仅仅是最后一次执行，我们可以根据过去的执行使用历史记录平滑授予大小值，并尝试优化以尽量减少溢出。例如，相同的交替工作负载会出现以下内存授予行为：

Graph of granted versus actual needed memory behavior in Memory Grant feedback with percentile and persistence mode memory grant feedback.

查询优化器使用过去内存授予大小要求的高百分位数来执行缓存计划，使用查询存储中保留的数据来计算内存授予大小。执行内存授予调整的百分位数调整基于最近的执行历史记录。随着时间的推移，给定的内存授予可减少溢出和内存浪费。

持久性也适用于 DOP 反馈和 CE 反馈。

启用和禁用内存授予反馈功能

在不更改兼容性级别的情况下禁用行模式内存授予反馈

可在数据库或语句范围内禁用行模式内存授予反馈，同时将数据库兼容级别维持在 150 或更高。若要对源自数据库的所有查询执行禁用行模式内存授予反馈，请在对应数据库的上下文中执行 SQL 语句：

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF;

若要对源自数据库的所有查询重新启用行模式内存授予反馈，请在对应数据库的上下文中执行以下命令：

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = ON;

此外，将 DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK 指定为 USE HINT 查询提示也可对特定查询禁用行模式内存授予反馈。例如：

SELECT * FROM Person.Address
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT ('DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK'));

USE HINT 查询提示的优先级高于数据库范围的配置或跟踪标志设置。

启用内存授予反馈持久性和百分位数

Azure SQL 数据库和 SQL Server 2022 (16.x) 中默认启用持久性和百分位数反馈。

为执行查询时连接到的数据库使用数据库兼容性级别 140 或更高级别。可以通过 ALTER DATABASE 对此进行更改：

ALTER DATABASE <DATABASE NAME> SET COMPATIBILITY LEVEL = 140; -- OR HIGHER

必须为使用此功能持久性部分的每个数据库启用查询存储。

禁用百分位数

若要对源自数据库的所有查询执行禁用内存授予反馈百分位数，请在对应数据库的上下文中执行以下命令：

ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT = OFF;

MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT 的默认设置为 ON。

禁用持久性

为源自数据库的所有查询执行禁用内存授予反馈持久性。

在适用数据库的上下文中执行以下操作：

ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERSISTENCE = OFF;

禁用内存授予反馈持久性也会删除现有的收集反馈。

MEMORY_GRANT_FEEDBACK_PERSISTENCE 的默认设置为 ON。

内存授予反馈注意事项

可通过查询 sys.database_scoped_configurations 来查看当前设置。

注意

如果同时将 BATCH_MODE_MEMORY_GRANT_FEEDBACK 和 ROW_MODE_MEMORY_GRANT_FEEDBACK 设置为 OFF，此功能将不起作用。

鉴于反馈数据现在保存在查询存储中，查询存储的使用要求有所提高。

基于百分位数的内存授予在减少溢写上出错。由于它不再仅基于上次执行，而是基于对过去几次执行的观察，这可能会针对忽高忽低的工作负荷增加内存使用量，这些工作负荷在各执行之间的内存授予要求差异较大。

从 SQL Server 2022 (16.x) 开始，当启用次要副本的查询存储时，可用性组中的次要副本的内存授予反馈是副本感知的。内存授予反馈可以在主要副本和次要副本上以不同的方式应用反馈。但是，内存授予反馈不会保留在次要副本上，故障转移时，来自旧的主要副本的内存授予反馈将应用于新的主要副本。当次要副本变为主要副本时，应用于次要副本的任何反馈都将丢失。有关详细信息，请参阅次要副本的查询存储。