智能查询处理功能详解

项目
02/01/2024

适用于： SQL Server Azure SQL 数据库 Azure SQL 托管实例

本文详细介绍了各种智能查询处理 (IQP) 功能、发行说明和更多详细信息。智能查询处理 (IQP) 功能系列包含有广泛影响的功能，既能提升现有工作负荷的性能，还能最大限度地减少实现工作量。

可以通过对数据库启用适当的数据库兼容性级别使工作负荷自动符合只能查询处理条件。可使用 Transact-SQL 进行此设置。例如，要将数据库的兼容性级别设置为 SQL Server 2022 (16.x)：

ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 160;

有关新版本引入的更改的详细信息，请参阅：

批处理模式自适应联接

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

利用一个缓存计划，批处理模式自适应联接功能，可延迟选择哈希联接或嵌套循环联接方法，直到扫描第一个输入后。自适应联接运算符可定义用于决定何时切换到嵌套循环计划的阈值。因此，计划可在执行期间动态切换到较好的联接策略。

有关详细信息，包括如何在不更改兼容性级别的情况下禁用自适应连接，请参阅了解自适应连接。

适用于 MSTVF 的交错执行

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

多语句表值函数 (MSTVF) 是一种用户定义函数，可以接受参数、执行多个 T-SQL 语句和 RETURN 表。

交错执行有助于解决由于与 MSTVF 关联的固定基数估算而导致的工作负载性能问题。通过交错执行，函数中的实际行计数可用于做出更明智的下游查询计划决策。

自 SQL Server 2014 (12.x) 起，MSTVF 的固定基数猜测为“100”，而早期 SQL Server 版本为“1”。

交错执行可更改单一查询执行的优化和执行阶段之间的单向边界，并使计划能够根据修订后的基数估值进行调整。在优化期间，如果数据库引擎遇到使用“多语句表值函数 (MSTVF)”的交错执行候选对象，优化将暂停，执行适用的子树，捕获准确的基数估算，然后恢复下游操作优化。

下图描绘了实时查询统计信息输出，一个显示 MSTVF 的固定基数估值影响的整体执行计划的子集

可查看实际行流与估计行数。有三个值得注意的计划区域（从右到左显示）：

MSTVF 表扫描具有 100 行的固定估值。然而，对于此示例，有 527,597 行流经此 MSTVF 表扫描，如通过“527597 行，共 100 行”的实际与估值的实时查询统计信息中所示，因此固定估值偏差显著。
对于嵌套循环操作，仅假定联接的外侧将返回 100 行。如果 MSTVF 实际返回惊人的行数，最好将另一联接算法一起使用。
对于哈希匹配操作，请注意小型警告符号，在本示例中指示到磁盘的溢出。

将之前的计划与通过启用交替执行生成的实际计划进行对比：

请注意，MSTVF 表扫描现可反映准确的基数估值。另请注意此表扫描的重新排序和其他操作。
而关于联接算法，我们已改为从嵌套循环操作切换到哈希匹配操作，后者在涉及大量行时更优。
另请注意，我们不再发出溢出警告，因为我们将基于从 MSTVF 表扫描流出的真实行数授予更多内存。

交错执行符合条件的语句

交错执行中的 MSTVF 引用语句当前必须处于只读，且不为数据修改操作的一部分。此外，如果 MSTVF 不使用运行时常数，则其不适合用于交错执行。

交错执行的优点

一般情况下，估计行数与实际行数之间的偏差越大，下游计划操作数翻倍，则性能影响越大。

一般来说，交错执行有益于以下情况中的查询：

对于中间结果集（本例中为 MSTVF），估计行数和实际行数之间存在巨大偏差。
整体查询对中间结果的大小更改十分敏感。这通常发生在查询计划中的该子树上存在复杂树的情况。 MSTVF 中简单的 SELECT * 不会获益于交错执行。

交错执行的开销

开销应为最少-到-无。 MSTVF 已在引入交错执行前具体化，但区别是现在已允许延迟优化，并可使用具体化行集的基数估算。与任何影响计划的更改一样，某些计划更改后虽可获得更好的子树基数，但同时也会使整体查询计划变得更糟。缓解可包含还原兼容级别或使用查询存储来强制计划的非回归版本。

交错执行和连续执行

缓存交错执行计划后，包含首次执行的修订后的估值的计划将用于连续执行，无需重新实例化交错执行。

跟踪交错执行活动

可在实际查询执行计划中查看使用情况属性：

执行计划属性	说明
ContainsInterleavedExecutionCandidates	适用于 QueryPlan 节点。如果为“true”，表示计划包含交错执行候选项。
IsInterleavedExecuted	TVF 节点的 RelOp 下的 RuntimeInformation 元素的属性。如果为“true”，表示该操作已具体化为交错执行操作的一部分。

还可以通过以下扩展事件跟踪交错执行匹配项：

XEvent	说明
`interleaved_exec_status`	发生交错执行时将引发此事件。
`interleaved_exec_stats_update`	此事件介绍交错执行更新的基数估值。
`Interleaved_exec_disabled_reason`	包含交错执行可能的候选项的查询实际未获取交错执行时，将引发此事件。

必须执行查询才能允许交错执行修改 MSTVF 基数估值。但是，如果通过 ContainsInterleavedExecutionCandidates 显示计划属性存在交错执行候选项，则估计的执行计划仍将显示。

交错执行缓存

如果已从缓存中逐出或清除计划，则在执行查询时将出现使用交错执行的全新编译。使用 OPTION (RECOMPILE) 的语句使用交错执行创建新计划，而不是将其缓存。

交错执行和查询存储互操作性

无法强制使用交错执行的计划。该计划是具有基于初始执行的正确基数估值的版本。

在不更改兼容级别的情况下禁用交错执行

可在数据库或语句范围内禁用交错执行，同时将数据库兼容性级别维持在 140 或更高。若要对源自数据库的所有查询执行禁用交错执行，请在对应数据库的上下文中执行以下命令：

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

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

启用后，此设置在 sys.database_scoped_configurations 中将显示为已启用。若要对源自数据库的所有查询执行重新启用交错执行，请在适用的数据库的上下文中执行以下命令：

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

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

此外，将 DISABLE_INTERLEAVED_EXECUTION_TVF 指定为 USE HINT 查询提示也可对特定查询禁用交错执行。例如：

SELECT [fo].[Order Key], [fo].[Quantity], [foo].[OutlierEventQuantity]
FROM [Fact].[Order] AS [fo]
INNER JOIN [Fact].[WhatIfOutlierEventQuantity]('Mild Recession',
                            '1-01-2013',
                            '10-15-2014') AS [foo] ON [fo].[Order Key] = [foo].[Order Key]
                            AND [fo].[City Key] = [foo].[City Key]
                            AND [fo].[Customer Key] = [foo].[Customer Key]
                            AND [fo].[Stock Item Key] = [foo].[Stock Item Key]
                            AND [fo].[Order Date Key] = [foo].[Order Date Key]
                            AND [fo].[Picked Date Key] = [foo].[Picked Date Key]
                            AND [fo].[Salesperson Key] = [foo].[Salesperson Key]
                            AND [fo].[Picker Key] = [foo].[Picker Key]
OPTION (USE HINT('DISABLE_INTERLEAVED_EXECUTION_TVF'));

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

标量 UDF 内联

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

标量 UDF 内联会自动将标量 UDF 转换为关系表达式，并将它们嵌入正在调用的 SQL 查询中。此转换提升了利用标量 UDF 的工作负载的性能。标量 UDF 内联可便于实现 UDF 内部基于成本的操作优化。生成的是高效、面向集、并行的（而不是低效、迭代、串行的）执行计划。该功能在数据库兼容性级别为 150 或更高时默认启用。

有关详细信息，请参阅标量 UDF 内联。

表变量延迟编译

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

表变量延迟编译功能提升了计划质量和引用表变量的查询的整体性能。在优化和初始计划编译期间，此功能传播基于实际表变量行计数的基数估算。然后，这种准确的行计数信息将用于优化下游计划操作。

使用“表变量延迟编译”，引用表变量的语句会延迟编译，直到首次实际执行语句后。此延迟编译行为与临时表的行为相同。此更改导致使用实际基数，而不是原始单行猜测。

要启用表变量延迟编译，请为查询运行时连接到的数据库启用数据库兼容性级别 150 或更高级别。

表变量延迟编译不会更改表变量的任何其他特性。例如，此功能不会向表变量添加列统计信息。

表变量延迟编译不会增加重新编译频率。相反，它将转移初始编译出现的位置。生成的缓存计划是基于初始延迟编译表变量行计数生成的。缓存计划由连续查询重复使用。会对计划重复使用，直至此计划已逐出或进行重新编译。

用于初始计划编译的表变量行计数表示典型值可能不同于固定的猜测行计数。如果不同，下游操作会有优势。如果表变量行计数在整个执行过程中差别很大，则可能无法通过此功能来提升性能。

在不更改兼容性级别的情况下禁用表变量延迟编译

可在数据库或语句范围内禁用表变量延迟编译，同时将数据库兼容性级别维持在 150 或更高。若要对源自数据库的所有查询禁用表变量延迟编译，请在对应数据库的上下文中执行以下示例：

ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = OFF;

若要对源自数据库的所有查询重新启用表变量延迟编译，请在对应数据库的上下文中执行以下示例：

ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = ON;

此外，还可以通过将 DISABLE_DEFERRED_COMPILATION_TV 分配为 USE HINT 查询提示，为特定查询禁用表变量延迟编译。例如：

DECLARE @LINEITEMS TABLE 
    (L_OrderKey INT NOT NULL,
     L_Quantity INT NOT NULL
    );

INSERT @LINEITEMS
SELECT L_OrderKey, L_Quantity
FROM dbo.lineitem
WHERE L_Quantity = 5;

SELECT O_OrderKey,
    O_CustKey,
    O_OrderStatus,
    L_QUANTITY
FROM    
    ORDERS,
    @LINEITEMS
WHERE    O_ORDERKEY    =    L_ORDERKEY
    AND O_OrderStatus = 'O'
OPTION (USE HINT('DISABLE_DEFERRED_COMPILATION_TV'));

参数敏感度计划优化

适用于：SQL Server 2022 (16.x) Azure SQL 数据库 Azure SQL 托管实例

参数敏感度计划 (PSP) 优化是智能查询处理系列功能的一部分。它解决了参数化查询的单个缓存计划对于所有可能的传入参数值都不是最佳方案的情况。这是数据分布不均匀的情况。

有关 PSP 优化的详细信息，请参阅参数敏感计划优化。
有关参数化和参数敏感性的详细信息，请参阅参数敏感性和参数和执行计划的重复使用。

近似查询处理

近似查询处理是新的功能系列。它可以跨响应速度比绝对精度更为关键的大型数据集进行聚合。例如，要跨 100 亿行计算 COUNT(DISTINCT())，以供显示在仪表板上。在这种情况下，绝对精度并不重要，而响应速度则至关重要。

非重复近似计数

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

新增的 APPROX_COUNT_DISTINCT 聚合函数返回组中唯一非 NULL 值的近似数。

从 SQL Server 2019 (15.x) 开始，无论兼容性级别如何，此功能均可用。

有关详细信息，请参阅 APPROX_COUNT_DISTINCT (Transact-SQL)。

近似百分位数

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

这些聚合函数可计算具有基于排名的可接受误差范围的大型数据集的百分位数，以帮助使用近似百分位数聚合函数快速做出决策。

有关详细信息，请参阅 APPROX_PERCENTILE_DISC (Transact-SQL) 和 APPROX_PERCENTILE_CONT (Transact-SQL)

行存储上的批处理模式

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

行存储上的批处理模式可实现分析工作负载的批处理模式执行，而无需列存储索引。此功能支持用于磁盘上堆和 B 树索引的批处理模式执行和位图筛选器。行存储上的批处理模式可实现对所有现有支持批处理模式的运算符的支持。

注意

文档在提到索引时一般使用 B 树这个术语。在行存储索引中，数据库引擎实现了 B+ 树。这不适用于列存储索引或内存优化表上的索引。有关详细信息，请参阅 SQL Server 以及 Azure SQL 索引体系结构和设计指南。

批模式执行概述

SQL Server 2012 (11.x) 引入了一项可加速分析工作负载的新功能，即列存储索引。在 SQL Server 的每个后续版本中，列存储索引的用例和性能都有所提高。在表上创建列存储索引可以提高分析工作负载的性能。不过，有两套相关但不同的技术：

使用列存储索引，分析查询仅访问所需列中的数据。与传统行存储索引中的压缩相比，列存储格式的页压缩更高效。
使用批处理模式处理，查询运算符可以更高效地处理数据。它们一次处理一批行，而不是一行。许多其他可伸缩性改进都与批处理模式处理相关。若要详细了解批处理模式，请参阅执行模式。

两组功能协同工作，以改进输入/输出 (I/O) 和 CPU 利用率：

通过使用列存储索引，更多数据适合内存。这会减少 I/O 工作负荷。
批处理模式处理可更有效地使用 CPU。

这两种技术尽可能利用彼此。例如，批处理模式聚合可以计算为列存储索引扫描的一部分。此外，通过批处理模式联接和批处理模式聚合，使用运行长度编码可以更有效地处理压缩的列存储数据。

但是，重要的是要了解这两个功能是独立的：

你可以获取使用列存储索引的行模式计划。
你可以获取仅使用行存储索引的批处理模式计划。

结合使用这两种功能时，通常效果最佳。在 SQL Server 2019 (15.x) 之前，SQL Server 查询优化器仅对涉及至少一个有列存储索引的表的查询考虑使用批处理模式处理。

列存储索引可能不适用于某些应用程序。应用程序可能会使用列存储索引不支持的其他一些功能。例如，就地修改与列存储压缩不兼容。因此，带有聚集列存储索引的表不支持触发器。更重要的是，列存储索引会增加 DELETE 和 UPDATE 语句的开销。

对于一些事务与分析混合工作负荷，事务工作负荷的开销超过了使用列存储索引的获益。此类方案可以通过仅使用批处理模式处理来提高 CPU 使用率，以从中获益。这就是为什么无论涉及哪种索引类型，行存储上的批处理模式功能都会考虑所有查询的批处理模式。

可能会受益于行存储上的批处理模式的工作负载

以下工作负载可能会受益于行存储上的批处理模式：

工作负载的很大一部分由分析查询组成。通常情况下，这些查询使用联接或聚合等运算符，可处理数十万行或更多行。
工作负载受 CPU 限制。如果瓶颈是 I/O，仍建议尽可能考虑使用列存储索引。
创建列存储索引会增加太多的工作负载事务部分开销。或者，创建列存储索引不可行，因为应用程序依赖列存储索引尚不支持的功能。

注意

只有通过减少 CPU 利用率，行存储上的批处理模式才能起到帮助作用。如果瓶颈与 I/O 相关，且数据尚未缓存（“冷”缓存），那么行存储上的批处理模式将不会改善查询运行时间。同样，如果计算机上没有足够的内存来缓存所有数据，性能也不可能得到提高。

行存储上的批处理模式带来哪些变化？

行存储上的批处理模式要求数据库的兼容性级别为 150。

即使查询不访问任何具有列存储索引的表，查询处理器也会使用启发式方法来决定是否考虑批处理模式。启发式方法包括以下检查：

初始检查输入查询中的表大小、使用的运算符和估计的基数。
其他检查点，因为优化器会发现新的、成本更低的查询计划。如果这些替代计划没有大量使用批处理模式，优化器会停止探索批处理模式替代方案。

如果使用行存储上的批处理模式，则会发现在查询计划中实际运行模式为批处理模式。扫描运算符对磁盘堆和 B 树索引使用批处理模式。此批处理模式扫描可以评估批处理模式位图筛选器。还可以在计划中看到其他批处理模式运算符。例如，哈希联接、基于哈希的聚合、排序、窗口聚合、筛选器、串联和计算标量运算符。

注解

查询计划并不总是使用批处理模式。查询优化器可能会认为批处理模式对查询没有益处。

查询优化器搜索空间正在发生变化。因此，如果获取行模式计划，它可能与在较低兼容性级别获取的计划不同。另外，如果获取批处理模式计划，它可能与通过列存储索引获取的计划不同。

鉴于新增的行存储上的批处理模式扫描，计划也可能会对混合列存储索引和行存储索引的查询发生变化。

新增的行存储上的批处理模式扫描当前存在以下限制：

它不会为内存中 OLTP 表，或除磁盘堆和 B 树以外的任何索引启动。
如果提取或筛选大型对象 (LOB) 列，它也不会启动。此限制包括稀疏列集和 XML 列。

有些查询即使使用列存储索引，也无法使用批处理模式。例如，涉及游标的查询。这些相同的排除项也扩展到行存储上的批处理模式。

配置行存储上的批处理模式

BATCH_MODE_ON_ROWSTORE 数据库范围的配置默认为 ON。

无需更改数据库兼容性级别，即可禁用行存储上的批处理模式：

-- Disabling batch mode on rowstore
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = OFF;

-- Enabling batch mode on rowstore
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = ON;

可以通过数据库范围的配置来禁用行存储上的批处理模式。但仍可使用 ALLOW_BATCH_MODE 查询提示，在查询一级替代设置。以下示例启用行存储的批处理模式，即使通过数据库作用域配置禁用了该功能：

SELECT [Tax Rate], [Lineage Key], [Salesperson Key], SUM(Quantity) AS SUM_QTY, SUM([Unit Price]) AS SUM_BASE_PRICE, COUNT(*) AS COUNT_ORDER
FROM Fact.OrderHistoryExtended
WHERE [Order Date Key]<=DATEADD(dd, -73, '2015-11-13')
GROUP BY [Tax Rate], [Lineage Key], [Salesperson Key]
ORDER BY [Tax Rate], [Lineage Key], [Salesperson Key]
OPTION(RECOMPILE, USE HINT('ALLOW_BATCH_MODE'));

还可以使用 DISALLOW_BATCH_MODE 查询提示，对特定查询禁用行存储上的批处理模式。请参阅以下示例：

SELECT [Tax Rate], [Lineage Key], [Salesperson Key], SUM(Quantity) AS SUM_QTY, SUM([Unit Price]) AS SUM_BASE_PRICE, COUNT(*) AS COUNT_ORDER
FROM Fact.OrderHistoryExtended
WHERE [Order Date Key]<=DATEADD(dd, -73, '2015-11-13')
GROUP BY [Tax Rate], [Lineage Key], [Salesperson Key]
ORDER BY [Tax Rate], [Lineage Key], [Salesperson Key]
OPTION(RECOMPILE, USE HINT('DISALLOW_BATCH_MODE'));

查询处理反馈功能

查询处理反馈功能是智能查询处理系列功能的一部分。

查询处理反馈是 SQL Server、Azure SQL 数据库和 Azure SQL 托管实例中的查询处理器使用有关查询执行的历史数据来决定查询是否可以从编译和执行方式的一个或多个更改中获得帮助的过程。性能数据在查询存储中收集，并提出了各种改进查询执行的建议。如果成功，我们会将这些修改保存到内存中的磁盘和/或查询存储以供将来使用。如果建议没有带来足够的改进，它们就会被丢弃，查询将在没有反馈的情况下继续执行。

有关 SQL Server 的不同版本或 Azure SQL 数据库或 Azure SQL 托管实例中提供哪些查询处理反馈功能的信息，请参阅 SQL 数据库中的智能查询处理，或有关每个反馈功能的以下文章。

内存授予反馈

过去主要版本的 SQL Server 中分批引入了内存授予反馈。

批处理模式内存授予反馈

有关批处理模式内存授予反馈的信息，请访问批处理模式内存授予反馈。

行模式内存授予反馈

有关行模式内存授予反馈的信息，请访问行模式内存授予反馈。

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

有关百分位数和持久性模式内存授予反馈的信息，请访问百分位数和持久性模式内存授予反馈。

并行度 (DOP) 反馈

有关 DOP 反馈的信息，请访问并行度 (DOP) 反馈。

基数估计 (CE) 反馈

有关 CE 反馈的信息，请访问基数估计 (CE) 反馈。

查询存储的优化计划强制执行

有关查询存储强制优化计划的信息，请访问查询存储的优化计划强制。

通过

智能查询处理功能详解

批处理模式自适应联接

适用于 MSTVF 的交错执行

交错执行符合条件的语句

交错执行的优点

交错执行的开销

交错执行和连续执行

跟踪交错执行活动

交错执行缓存

交错执行和查询存储互操作性

在不更改兼容级别的情况下禁用交错执行

标量 UDF 内联

表变量延迟编译

在不更改兼容性级别的情况下禁用表变量延迟编译

参数敏感度计划优化

近似查询处理

非重复近似计数

近似百分位数

行存储上的批处理模式

批模式执行概述

可能会受益于行存储上的批处理模式的工作负载

行存储上的批处理模式带来哪些变化？

注解

配置行存储上的批处理模式

查询处理反馈功能

内存授予反馈

批处理模式内存授予反馈

行模式内存授予反馈

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

并行度 (DOP) 反馈

基数估计 (CE) 反馈

查询存储的优化计划强制执行

相关内容

反馈

其他资源