通过

内存授予反馈

适用于： Microsoft Fabric 中的 SQL Server 2017 （14.x） 及更高版本 Azure SQL 数据库Azure SQL 托管实例SQL 数据库

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

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

注意

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

批处理模式内存授予反馈

适用于：SQL Server 2017（14.x）及更高版本、Azure SQL 数据库和 Azure SQL 托管实例（数据库兼容性级别 140 及更高）。

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

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

DECLARE @EndTime AS DATETIME = '2016-09-22 00:00:00.000';
DECLARE @StartTime AS 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;

< c1>已授予内存与溢出内存 MB 对比图的屏幕截图，显示了大量溢出。

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

已授予与已溢出内存的图表的屏幕截图，表明没有溢出。

内存授予反馈大小调整

对于内存授予过量的情况，如果授予的内存是实际使用内存大小的两倍，内存授予反馈将重新计算内存授予并更新缓存的计划。 内存授予不足 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 或更高。 若要禁用来自数据库的所有查询执行的批处理模式内存授予反馈，请在适用数据库的上下文中执行下面的 Transact-SQL 语句。

  • 在 SQL Server 2017（14.x）：

    ALTER DATABASE SCOPED CONFIGURATION
SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

  • 在 SQL Server 2019（15.x）及更高版本中，以及 Azure SQL 数据库中：

    ALTER DATABASE SCOPED CONFIGURATION
SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;

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

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

  • 在 SQL Server 2017（14.x）：

    ALTER DATABASE SCOPED CONFIGURATION
SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;

  • 在 SQL Server 2019（15.x）及更高版本中，以及 Azure SQL 数据库中：

    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 2019（15.x）及更高版本、Azure SQL 数据库和 Azure SQL 托管实例（数据库兼容性级别 150 及更高）。

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

要在 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 值 说明
否：首次执行 内存授予反馈不调整用于首次编译和相关执行的内存。
否：准确授予 如果没有溢出到磁盘，且语句使用至少 50% 的授予内存，就不会触发内存授予反馈。
否：反馈已禁用 如果内存授予反馈不断触发，且在内存增加和内存减少操作之间波动，数据库引擎就会对语句禁用内存授予反馈。
是：调整 内存授予反馈已应用，并且可能会针对下一次执行进行进一步调整。
是：百分位数调整 使用百分位授予算法应用内存授予反馈，该算法不仅查看最近的执行，还有更多的历史记录。
是：稳定 内存授予反馈已应用，且授予的内存量现已稳定。所谓稳定，是指上一次执行时授予的内存量与本次执行所授予的量相同。

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

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

此功能是在 SQL Server 2022（16.x）中引入的。然而，该性能增强功能适用于在数据库兼容性级别 140（在 SQL Server 2017（14.x）中引入）及更高版本运行的查询，或使用 QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n 提示 140 及更高版本，并且在数据库启用了查询存储且该存储为“读写”状态的情况下。

  • 默认情况下，SQL Server 2022（16.x）中启用了百分位内存授予反馈，但如果未启用查询存储，或者查询存储未处于“读取写入”状态，则不起作用。

  • 默认情况下，SQL Server 2022（16.x）中存在内存授予、CE 和 DOP 反馈的持久性，但在未启用查询存储或查询存储未处于“读取写入”状态时不起作用。

  • Azure SQL 数据库中提供了内存授予反馈的百分位数和持久性，并在所有数据库（包括现有数据库和新数据库）中默认启用。

  • 目前，Azure SQL 托管实例不支持内存授予反馈的百分位和持久性。

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

内存授予反馈 (MGF) 是一项现有功能，可根据过去的性能调整为查询分配的内存大小。 然而，该功能的早期版本仅将内存授予的调整值随执行计划一同存储在缓存中。如果计划从缓存中被逐出，反馈过程就必须重新开始。这会导致在计划被逐出后，查询在最初几次执行时性能不佳。 新的解决方案是将授予信息与其他查询信息一起保留在查询存储中，以便在缓存逐出期间持续受益。 内存授予反馈的持久化和百分位模式，以一种非侵入性的方式解决了该功能现有的局限性。

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

图表显示了在没有百分位和持久性模式内存授予反馈的情况下，已授予内存与实际所需内存行为的关系。

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

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

此图显示了内存授予反馈中授予的内存行为与实际所需内存行为的图，其中包含百分位和持久性模式内存授予反馈。

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

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

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

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

可在数据库或语句范围内禁用行模式内存授予反馈，同时将数据库兼容性级别维持在 150 或更高。 若要禁用来自数据库的所有查询执行的行模式内存授予反馈，请在适用数据库的上下文中执行 Transact-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 a higher value

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

禁用百分位数

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

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) 开始，当启用次要副本的查询存储时，可用性组中的次要副本的内存授予反馈是副本感知的。 内存授予反馈可以在主要副本和次要副本上以不同的方式应用反馈。 但是，内存授予反馈不会保留在次要副本上，故障转移时，旧主副本的内存授予反馈将应用于新的主副本。 当次要副本变为主要副本时，应用于次要副本的任何反馈都将丢失。 从 SQL Server 2025（17.x）版本开始，查询存储库可用于辅助可用性组副本。 有关详细信息，请参阅可读辅助副本的查询存储。

相关内容

  • Last updated on