SQL 数据库中的智能查询处理Intelligent query processing in SQL databases

适用于:是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

智能查询处理 (QP) 功能系列包含有广泛影响的功能,既能提升现有工作负荷的性能,还能最大限度地减少实现工作量。The intelligent query processing (QP) feature family includes features with broad impact that improve the performance of existing workloads with minimal implementation effort to adopt.

智能查询处理

可以通过对数据库启用适当的数据库兼容性级别使工作负荷自动符合只能查询处理条件。You can make workloads automatically eligible for intelligent query processing by enabling the applicable database compatibility level for the database. 可使用 Transact-SQL 进行此设置。You can set this using Transact-SQL. 例如:For example:

ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 150;

下表详细列出了所有智能查询处理功能,以及针对数据库兼容性级别必须具备的任何要求。The following table details all intelligent query processing features, along with any requirement they have for database compatibility level.

IQP 功能IQP Feature 在 Azure SQL 数据库中是否受支持Supported in Azure SQL Database 在 SQL Server 中是否受支持Supported in SQL Server DescriptionDescription
自适应联接(批处理模式)Adaptive Joins (Batch Mode) 是,兼容性级别为 140Yes, under compatibility level 140 是,自 SQL Server 2017 起,兼容性级别为 140Yes, starting in SQL Server 2017 under compatibility level 140 自适应联接在运行时期间根据实际输入行自动选择联接类型。Adaptive joins dynamically select a join type during runtime based on actual input rows.
非重复近似计数Approximate Count Distinct 是,公共预览版Yes, public preview 是,自 SQL Server 2019 CTP 2.0 起,公共预览版Yes, starting in SQL Server 2019 CTP 2.0, public preview 由于高性能和低内存占用量,可针对大数据方案提供近似的 COUNT DISTINCT。Provide approximate COUNT DISTINCT for big data scenarios with the benefit of high performance and a low memory footprint.
行存储上的批处理模式Batch Mode on Rowstore 是,兼容性级别为 150,公共预览版Yes, under compatibility level 150, public preview 是,自 SQL Server 2019 CTP 2.0 起,兼容性级别为 150,公共预览版Yes, starting in SQL Server 2019 CTP 2.0 under compatibility level 150, public preview 可为 CPU 绑定关系的 DW 工作负载提供批处理模式,无需列存储索引。Provide batch mode for CPU-bound relational DW workloads without requiring columnstore indexes.
交错执行Interleaved Execution 是,兼容性级别为 140Yes, under compatibility level 140 是,自 SQL Server 2017 起,兼容性级别为 140Yes, starting in SQL Server 2017 under compatibility level 140 请使用在首次编译时遇到的多语句表值函数的实际基数,而不是一个固定猜测值。Use the actual cardinality of the multi-statement table valued function encountered on first compilation instead of a fixed guess.
内存授予反馈(批处理模式)Memory Grant Feedback (Batch Mode) 是,兼容性级别为 140Yes, under compatibility level 140 是,自 SQL Server 2017 起,兼容性级别为 140Yes, starting in SQL Server 2017 under compatibility level 140 如果批处理模式查询有溢出到磁盘的操作,则需为以后的执行添加更多内存。If a batch mode query has operations that spill to disk, add more memory for consecutive executions. 如果查询浪费分配给它的超过 50% 内存,请对连续的执行减少内存授予。If a query wastes > 50% of the memory allocated to it, reduce the memory grant side for consecutive executions.
内存授予反馈(行模式)Memory Grant Feedback (Row Mode) 是,兼容性级别为 150,公共预览版Yes, under compatibility level 150, public preview 是,自 SQL Server 2019 CTP 2.0 起,兼容性级别为 150,公共预览版Yes, starting in SQL Server 2019 CTP 2.0 under compatibility level 150, public preview 如果行模式查询有溢出到磁盘的操作,则需为以后的执行添加更多内存。If a row mode query has operations that spill to disk, add more memory for consecutive executions. 如果查询浪费分配给它的超过 50% 内存,请对连续的执行减少内存授予。If a query wastes > 50% of the memory allocated to it, reduce the memory grant side for consecutive executions.
标量 UDF 内联Scalar UDF Inlining No 是,自 SQL Server 2019 CTP 2.1 起,兼容性级别为 150,公共预览版Yes, starting in SQL Server 2019 CTP 2.1 under compatibility level 150, public preview 标量 UDF 将转换为内联在调用查询中的等效关系表达式,这通常会使性能显著提升。Scalar UDFs are transformed into equivalent relational expressions that are “inlined” into the calling query, often resulting in significant performance gains.
表变量延迟编译Table Variable Deferred Compilation 是,兼容性级别为 150,公共预览版Yes, under compatibility level 150, public preview 是,自 SQL Server 2019 CTP 2.0 起,兼容性级别为 150,公共预览版Yes, starting in SQL Server 2019 CTP 2.0 under compatibility level 150, public preview 请使用在首次编译时遇到的表变量的实际基数,而不是一个固定猜测值。Use the actual cardinality of the table variable encountered on first compilation instead of a fixed guess.

批处理模式自适应联接Batch mode adaptive joins

借助此功能,可以在执行期间使用一个缓存计划,将计划动态切换为采用更优质的联接策略。With this feature, your plan can dynamically switch to a better join strategy during execution by using a single cached plan.

通过批处理模式自适应联接功能,可延迟选择哈希联接或嵌套循环联接方法,直到扫描第一个输入后。The batch mode Adaptive Joins feature enables the choice of a Hash Join or Nested Loops Join method to be deferred until after the first input has been scanned. 自适应联接运算符可定义用于决定何时切换到嵌套循环计划的阈值。The Adaptive Join operator defines a threshold that is used to decide when to switch to a Nested Loops plan. 因此,计划可在执行期间动态切换到较好的联接策略。Your plan can therefore dynamically switch to a better join strategy during execution. 工作原理如下:Here's how it works:

  • 如果生成联接输入的行计数足够小,以致于嵌套循环联接优于哈希联接,则计划将切换到嵌套循环算法。If the row count of the build join input is small enough that a nested loop join would be more optimal than a Hash Join, your plan switches to a Nested Loops algorithm.
  • 如果生成联接输入超过特定行计数阈值,则不会进行切换并且计划将通过哈希联接继续。If the build join input exceeds a specific row count threshold, no switch occurs and your plan continues with a Hash Join.

以下查询用于说明自适应联接示例:The following query is used to illustrate an Adaptive Join example:

SELECT [fo].[Order Key], [si].[Lead Time Days], [fo].[Quantity]
FROM [Fact].[Order] AS [fo]
INNER JOIN [Dimension].[Stock Item] AS [si]
       ON [fo].[Stock Item Key] = [si].[Stock Item Key]
WHERE [fo].[Quantity] = 360;

查询将返回 336 行。The query returns 336 rows. 启用实时查询统计信息后,将看到以下计划:Enabling Live Query Statistics, we see the following plan:

查询生成 336 行

在计划中,将看到以下信息:In the plan, we see the following:

  1. 我们具有用于为哈希联接生成阶段提供行的列存储索引扫描。We have a columnstore index scan used to provide rows for the hash join build phase.
  2. 我们拥有新的自适应联接运算符。We have the new Adaptive Join operator. 此运算符可定义用于决定何时切换到嵌套循环计划的阈值。This operator defines a threshold that is used to decide when to switch to a Nested Loops plan. 对于该示例,阈值为 78 行。For our example, the threshold is 78 rows. 包含 >= 78 行的任何示例均将使用哈希联接。Anything with >= 78 rows will use a Hash Join. 如果小于阈值,将使用嵌套循环联接。If less than the threshold, a Nested Loops Join will be used.
  3. 由于我们将返回 336 行,超过了阈值,因此第二个分支表示标准哈希联接操作的探测阶段。Since we return 336 rows, we are exceeding the threshold and so the second branch represents the probe phase of a standard Hash Join operation. 请注意,实时查询统计信息将显示流经运算符的行,在本示例中为“672 行,共 672 行”。Notice that Live Query Statistics shows rows flowing through the operators - in this case "672 of 672".
  4. 并且,最后一个分支是供未超出阈值的嵌套循环联接使用的聚集索引查找。And the last branch is our Clustered Index Seek for use by the nested loop join had the threshold not been exceeded. 请注意,我们将看到显示“0 行,共 336 行”(未使用分支)。Notice that we see "0 of 336" rows displayed (the branch is unused). 现将计划与同一查询进行对比,但此次针对表格中只有一行的的 Quantity 值:Now contrast the plan with the same query, but this time for a Quantity value that only has one row in the table:
SELECT [fo].[Order Key], [si].[Lead Time Days], [fo].[Quantity]
FROM [Fact].[Order] AS [fo]
INNER JOIN [Dimension].[Stock Item] AS [si]
       ON [fo].[Stock Item Key] = [si].[Stock Item Key]
WHERE [fo].[Quantity] = 361;

查询将返回一行。The query returns one row. 启用实时查询统计信息后,将看到以下计划:Enabling Live Query Statistics we see the following plan:

查询生成一行

在计划中,将看到以下信息:In the plan, we see the following:

  • 返回一行后,聚集索引搜索现已有行流经它。With one row returned, the Clustered Index Seek now has rows flowing through it.
  • 并且,由于哈希联接生成阶段未继续进行,因此没有行流经第二个分支。And since the Hash Join build phase did not continue, there are no rows flowing through the second branch.

自适应联接优点Adaptive Join benefits

小型和大型联接输入扫描之间频繁振荡的工作负荷将从此功能获益最大。Workloads with frequent oscillations between small and large join input scans will benefit most from this feature.

自适应联接开销Adaptive Join overhead

自适应联接引入了比索引嵌套循环联接等效计划更高的内存要求。Adaptive joins introduce a higher memory requirement than an indexed Nested Loops Join equivalent plan. 它会请求额外的内存,就像嵌套循环属于哈希联接一样。The additional memory is requested as if the Nested Loops was a Hash Join. 此外还有作为断断续续操作而不是嵌套循环流式处理等效联接的生成阶段的开销。There is also overhead for the build phase as a stop-and-go operation versus a Nested Loops streaming equivalent join. 这笔额外成本产生的同时也实现了行计数可在生成输入中波动的方案灵活性。With that additional cost comes flexibility for scenarios where row counts may fluctuate in the build input.

自适应联接缓存和重复使用Adaptive Join caching and re-use

批处理模式自适应联接适用于语句的初始执行,编译后,根据编译的自适应联结阈值和流经外部输入生成阶段的运行时行,连续执行将保持自适应状态。Batch mode Adaptive Joins work for the initial execution of a statement, and once compiled, consecutive executions will remain adaptive based on the compiled Adaptive Join threshold and the runtime rows flowing through the build phase of the outer input.

跟踪自适应联接活动Tracking Adaptive Join activity

自适应联接运算符具有以下计划运算符属性:The Adaptive Join operator has the following plan operator attributes:

计划属性Plan attribute 描述Description
AdaptiveThresholdRowsAdaptiveThresholdRows 显示用于从哈希联接切换到嵌套循环联接的阈值。Shows the threshold use to switch from a hash join to nested loop join.
EstimatedJoinTypeEstimatedJoinType 可能的联接类型。What the join type is likely to be.
ActualJoinTypeActualJoinType 在实际计划中,显示根据阈值最终选择的联接算法。In an actual plan, shows what join algorithm was ultimately chosen based on the threshold.

估计的计划显示自适应联接计划形状,以及定义的自适应联接阈值和估计的联接类型。The estimated plan shows the Adaptive Join plan shape, along with a defined Adaptive Join threshold and estimated join type.

自适应联接和查询存储互操作性Adaptive join and Query Store interoperability

查询存储可捕获并强制执行批处理模式自适应联接计划。Query Store captures and is able to force a batch mode Adaptive Join plan.

符合自适应联接条件的语句Adaptive join eligible statements

以下多个条件可使逻辑联接符合批处理模式自适应联接的条件:A few conditions make a logical join eligible for a batch mode Adaptive Join:

  • 数据库兼容级别为 140。The database compatibility level is 140.
  • 查询是 SELECT 语句(数据修改语句当前不符合条件)。The query is a SELECT statement (data modification statements are currently ineligible).
  • 联接符合同时由索引嵌套循环联接或哈希联接物理算法执行的条件。The join is eligible to be executed both by an indexed Nested Loops Join or a Hash Join physical algorithm.
  • 哈希联接将通过整体查询中的列存储索引状态或联接正在直接引用的列存储索引表使用批处理模式。The Hash Join uses batch mode - either through the presence of a Columnstore index in the query overall or a Columnstore indexed table being referenced directly by the join.
  • 嵌套循环联接和哈希联接生成的替代解决方案的第一个子级(外部引用)应相同。The generated alternative solutions of the Nested Loops Join and Hash Join should have the same first child (outer reference).

自适应联接和嵌套循环效率Adaptive joins and nested loop efficiency

如果自适应联接切换到嵌套循环操作,它将使用哈希联接生成已经读取的行。If an Adaptive Join switches to a Nested Loops operation, it uses the rows already read by the Hash Join build. 运算符不会再次重新读取外部引用行。The operator does not re-read the outer reference rows again.

自适应阈值行Adaptive threshold rows

下图显示了哈希联接的成本与嵌套循环联接替代的成本之间的示例交集。The following chart shows an example intersection between the cost of a Hash Join versus the cost of a Nested Loops Join alternative.  在这个交点处,确定了阈值,该阈值将反过来确定将实际用于联接操作的算法。  At this intersection point, the threshold is determined that in turn determines the actual algorithm used for the join operation.

联接阈值

在不更改兼容级别的情况下禁用自适应联接Disabling adaptive joins without changing the compatibility level

可在数据库或语句范围内禁用自适应联接,同时将数据库兼容性级别维持在 140 或更高。Adaptive joins can be disabled at the database or statement scope while still maintaining database compatibility level 140 and higher.
若要对源自数据库的所有查询执行禁用自适应联接,请在对应数据库的上下文中执行以下命令:To disable adaptive joins for all query executions originating from the database, execute the following within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_ADAPTIVE_JOINS = ON;

启用后,此设置在 sys.database_scoped_configurations 将显示为已启用。When enabled, this setting will appear as enabled in sys.database_scoped_configurations. 若要对源自数据库的所有查询执行重新启用自适应联接,请在对应数据库的上下文中执行以下命令:To re-enable adaptive joins for all query executions originating from the database, execute the following within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_ADAPTIVE_JOINS = OFF;

此外,将 DISABLE_BATCH_MODE_ADAPTIVE_JOINS 指定为 USE HINT 查询提示也可为特定查询禁用自适应联接。You can also disable adaptive joins for a specific query by designating DISABLE_BATCH_MODE_ADAPTIVE_JOINS as a USE HINT query hint. 例如:For example:

SELECT s.CustomerID,
       s.CustomerName,
       sc.CustomerCategoryName
FROM Sales.Customers AS s
LEFT OUTER JOIN Sales.CustomerCategories AS sc
       ON s.CustomerCategoryID = sc.CustomerCategoryID
OPTION (USE HINT('DISABLE_BATCH_MODE_ADAPTIVE_JOINS')); 

USE HINT 查询提示的优先级高于数据库范围的配置或跟踪标志设置。A USE HINT query hint takes precedence over a database scoped configuration or trace flag setting.

批处理模式内存授予反馈Batch mode memory grant feedback

SQL ServerSQL Server 中查询执行后的计划包括执行所需的最小内存和能将所有行纳入内存的理想内存授予大小。A query's post-execution plan in SQL ServerSQL Server includes the minimum required memory needed for execution and the ideal memory grant size to have all rows fit in memory. 如果内存授予大小不正确,性能将受到影响。Performance suffers when memory grant sizes are incorrectly sized. 如果授予过量,则会导致内存浪费,减少并发执行。Excessive grants result in wasted memory and reduced concurrency. 如果内存授予不足,则会导致到磁盘的昂贵溢出。Insufficient memory grants cause expensive spills to disk. 通过解决重复工作负荷,批处理模式内存授予反馈可重新计算查询所需的实际内存,并更新缓存计划的授予值。By addressing repeating workloads, batch mode memory grant feedback recalculates the actual memory required for a query and then updates the grant value for the cached plan. 执行相同的查询语句时,查询将使用修改后的内存授予大小,减少影响并发的过量内存授予,并修复造成到磁盘的昂贵溢出的估计不足的内存授予。When an identical query statement is executed, the query uses the revised memory grant size, reducing excessive memory grants that impact concurrency and fixing underestimated memory grants that cause expensive spills to disk. 下图是使用批处理模式自适应内存授予反馈的一个示例。The following graph shows one example of using batch mode adaptive memory grant feedback. 对于首次执行查询,由于高溢出,持续时间为 88 秒:For the first execution of the query, duration was 88 seconds due to high spills:

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;

高溢出

启用内存授予反馈后,对于第二次执行,持续时间为 1 秒(从 88 秒减少),完全消除溢出,且授予内存更高:With memory grant feedback enabled, for the second execution, duration is 1 second (down from 88 seconds), spills are removed entirely, and the grant is higher:

无溢出

内存授予反馈大小调整Memory grant feedback sizing

对于内存授予过量的情况,如果授予的内存是实际使用内存大小的两倍,内存授予反馈将重新计算内存授予并更新缓存的计划。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 and update the cached plan. 内存授予不足 1 MB 的计划将不会针对超额重新进行计算。Plans with memory grants under 1 MB will not be recalculated for overages. 对于内存授予大小不足,造成批处理模式运算符溢出到磁盘的情况,内存授予反馈将触发内存授予的重新计算。For an insufficiently sized memory grant condition, that result in a spill to disk for batch mode operators, memory grant feedback will trigger a recalculation of the memory grant. 将向内存授予反馈报告溢出事件,并可通过 spilling_report_to_memory_grant_feedback xEvent 显露。Spill events are reported to memory grant feedback and can be surfaced via the spilling_report_to_memory_grant_feedback xEvent. 此事件将返回计划的节点 ID 和该节点溢出的数据大小。This event returns the node id from the plan and spilled data size of that node.

内存授予反馈和参数敏感型方案Memory grant feedback and parameter sensitive scenarios

为保持最优,不同的参数值可能还需要不同的查询计划。Different parameter values may also require different query plans in order to remain optimal. 此类查询被定义为“参数敏感型”。This type of query is defined as "parameter-sensitive." 对于参数敏感型计划,如果查询具有不稳定的内存要求,则内存授予反馈对该查询禁用。For parameter-sensitive plans, memory grant feedback will disable itself on a query if it has unstable memory requirements. 在重复运行查询后禁用计划,并且可以通过监视 memory_grant_feedback_loop_disabled xEvent 观察到这一切。The plan is disabled after several repeated runs of the query and this can be observed by monitoring the memory_grant_feedback_loop_disabled xEvent. 有关参数截取和参数敏感度的详细信息,请参阅查询处理体系结构指南For more information about parameter sniffing and parameter sensitivity, refer to the Query Processing Architecture Guide.

内存授予反馈缓存Memory grant feedback caching

反馈可以存储在单个执行的缓存计划中。Feedback can be stored in the cached plan for a single execution. 它是该语句的连续执行,但受益于内存授予反馈调整。It is the consecutive executions of that statement, however, that benefit from the memory grant feedback adjustments. 此功能适用于重复执行语句。This feature applies to repeated execution of statements. 内存授予反馈将只更改缓存的计划。Memory grant feedback will change only the cached plan. 当前未在查询存储中捕获更改。Changes are currently not captured in the Query Store. 如果从缓存中逐出计划,则不会保存反馈。Feedback is not persisted if the plan is evicted from cache. 如果存在故障转移,则反馈也将丢失。Feedback will also be lost if there is a failover. 使用 OPTION (RECOMPILE) 的语句可创建新的计划,但不会缓存它。A statement using OPTION (RECOMPILE) creates a new plan and does not cache it. 由于它未被缓存,因此不会产生内存授予反馈,也不会针对编译和执行存储。Since it is not cached, no memory grant feedback is produced and it is not stored for that compilation and execution. 但是,如果已缓存未使用 OPTION (RECOMPILE) 的等效语句(即包含相同的查询哈希)并重新执行,连续语句可从内存授予反馈中受益。However, if an equivalent statement (that is, with the same query hash) that did not use OPTION (RECOMPILE) was cached and then re-executed, the consecutive statement can benefit from memory grant feedback.

跟踪内存授予反馈活动Tracking memory grant feedback activity

可以使用 memory_grant_updated_by_feedback xEvent 跟踪内存授予反馈事件。You can track memory grant feedback events using the memory_grant_updated_by_feedback xEvent. 此事件可跟踪当前执行计数历史记录,内存授予反馈更新计划的次数,修改前理想的额外内存授予,以及内存授予反馈修改缓存计划后理想的额外内存授予。This event tracks the current execution count history, the number of times the plan has been updated by memory grant feedback, the ideal additional memory grant before modification and the ideal additional memory grant after memory grant feedback has modified the cached plan.

内存授予反馈、资源调控器和查询提示Memory grant feedback, resource governor and query hints

实际内存授予服从资源调控器或查询提示确定的查询内存限制。The actual memory granted honors the query memory limit determined by the resource governor or query hint.

在不更改兼容级别的情况下禁用批处理模式内存授予反馈Disabling batch mode memory grant feedback without changing the compatibility level

可在数据库或语句范围内禁用内存授予反馈,同时将数据库兼容级别维持在 140 或更高。Memory grant feedback can be disabled at the database or statement scope while still maintaining database compatibility level 140 and higher. 若要对源自数据库的所有查询执行禁用批处理模式内存授予反馈,请在对应数据库的上下文中执行以下命令:To disable batch mode memory grant feedback for all query executions originating from the database, execute the following within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

启用后,此设置在 sys.database_scoped_configurations 将显示为已启用。When enabled, this setting will appear as enabled in sys.database_scoped_configurations.

若要对源自数据库的所有查询执行重新启用批处理模式内存授予反馈,请在对应数据库的上下文中执行以下命令:To re-enable batch mode memory grant feedback for all query executions originating from the database, execute the following within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;

此外,将 DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK 指定为 USE HINT 查询提示也可为特定查询禁用批处理模式内存授予反馈。You can also disable batch mode memory grant feedback for a specific query by designating DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK as a USE HINT query hint. 例如:For example:

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

USE HINT 查询提示的优先级高于数据库范围的配置或跟踪标志设置。A USE HINT query hint takes precedence over a database scoped configuration or trace flag setting.

行模式内存授予反馈Row mode memory grant feedback

适用对象:Azure SQL DatabaseAzure SQL Database 为公开预览版功能Applies to: Azure SQL DatabaseAzure SQL Database as a public preview feature

备注

行模式内存授予反馈是一项公共预览版功能。Row mode memory grant feedback is a public preview feature.

通过调整批处理模式和行模式运算符的内存授予大小,行模式内存授予反馈扩展了批处理模式内存授予反馈功能。Row mode memory grant feedback expands on the batch mode memory grant feedback feature by adjusting memory grant sizes for both batch and row mode operators.

若要在 Azure SQL DatabaseAzure SQL Database 中启用行模式内存授予反馈的公共预览版,请为执行查询时连接到的数据库启用数据库兼容性级别 150。To enable the public preview of row mode memory grant feedback in Azure SQL DatabaseAzure SQL Database, enable database compatibility level 150 for the database you are connected to when executing the query.

通过 memory_grant_updated_by_feedback XEvent,可以查看行模式内存授予反馈活动。Row mode memory grant feedback activity will be visible via the memory_grant_updated_by_feedback XEvent.

从行模式内存授予反馈开始,将显示两个新的查询计划属性,用于实际执行后计划:IsMemoryGrantFeedbackAdjusted 和 LastRequestedMemory,它们将添加到 MemoryGrantInfo 查询计划 XML 元素。Starting with row mode memory grant feedback, two new query plan attributes will be shown for actual post-execution plans: IsMemoryGrantFeedbackAdjusted and LastRequestedMemory, which are added to the MemoryGrantInfo query plan XML element.

LastRequestedMemory 显示上一次查询执行中的授予内存(以千字节 (KB) 为单位)。LastRequestedMemory shows the granted memory in Kilobytes (KB) from the prior query execution. 使用 IsMemoryGrantFeedbackAdjusted 属性,可以查看实际查询执行计划内语句的内存授予反馈状态。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 值IsMemoryGrantFeedbackAdjusted Value 描述Description
No:First ExecutionNo: First Execution 内存授予反馈不调整用于首次编译和相关执行的内存。Memory grant feedback does not adjust memory for the first compile and associated execution.
No:Accurate GrantNo: Accurate Grant 如果没有溢出到磁盘,且语句使用至少 50% 的授予内存,就不会触发内存授予反馈。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 disabledNo: 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:AdjustingYes: Adjusting 内存授予反馈已应用,并且可能会针对下一次执行进行进一步调整。Memory grant feedback has been applied and may be further adjusted for the next execution.
Yes:StableYes: 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.

备注

公共预览版行模式内存授予反馈计划特性在版本 17.9 及更高版本中的 SQL Server Management StudioSQL Server Management Studio 图形查询执行计划内可见。The public preview row mode memory grant feedback plan attributes are visible in SQL Server Management StudioSQL Server Management Studio graphical query execution plans in versions 17.9 and higher.

在不更改兼容级别的情况下禁用行模式内存授予反馈Disabling row mode memory grant feedback without changing the compatibility level

可在数据库或语句范围内禁用行模式内存授予反馈,同时将数据库兼容级别维持在 150 或更高。Row mode memory grant feedback can be disabled at the database or statement scope while still maintaining database compatibility level 150 and higher. 若要对源自数据库的所有查询执行禁用行模式内存授予反馈,请在对应数据库的上下文中执行以下命令:To disable row mode memory grant feedback for all query executions originating from the database, execute the following within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF;

若要对源自数据库的所有查询重新启用行模式内存授予反馈,请在对应数据库的上下文中执行以下命令:To re-enable row mode memory grant feedback for all query executions originating from the database, execute the following within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = ON;

此外,将 DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK 指定为 USE HINT 查询提示也可对特定查询禁用行模式内存授予反馈。You can also disable row mode memory grant feedback for a specific query by designating DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK as a USE HINT query hint. 例如:For example:

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

USE HINT 查询提示的优先级高于数据库范围的配置或跟踪标志设置。A USE HINT query hint takes precedence over a database scoped configuration or trace flag setting.

适用于 MSTVF 的交错执行Interleaved execution for MSTVFs

通过交错执行,函数中的实际行计数可用于做出更明智的下游查询计划决策。With interleaved execution, the actual row counts from the function are used to make better-informed downstream query plan decisions. 若要详细了解多语句表值函数 (MSTVF),请参阅表值函数For more information on multi-statement table-valued functions (MSTVFs), see Table-valued functions.

交错执行可更改单一查询执行的优化和执行阶段之间的单向边界,并使计划能够根据修订后的基数估值进行调整。Interleaved execution changes the unidirectional boundary between the optimization and execution phases for a single-query execution and enables plans to adapt based on the revised cardinality estimates. 如果遇到交错执行的候选项,该项当前为“多语句表值函数 (MSTVF)”,将暂停优化,执行适用的子树,捕获准确的基数估值,然后针对下游操作继续优化。During optimization if we encounter a candidate for interleaved execution, which is currently multi-statement table-valued functions (MSTVFs), we will pause optimization, execute the applicable subtree, capture accurate cardinality estimates, and then resume optimization for downstream operations.

SQL Server 2014 (12.x)SQL Server 2014 (12.x) 起,MSTVF 的固定基数猜测为“100”,而早期 SQL ServerSQL Server 版本为“1”。MSTVFs have a fixed cardinality guess of 100 starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x), and 1 for earlier SQL ServerSQL Server versions. 交错执行有助于解决由于与 MSTVF 关联的这些固定基数估值而导致的工作负荷性能问题。Interleaved execution helps workload performance issues that are due to these fixed cardinality estimates associated with MSTVFs. 有关 MSTVF 的详细信息,请参阅创建用户定义函数(数据库引擎)For more information on MSTVFs, see Create User-defined Functions (Database Engine).

下图描绘了实时查询统计信息输出,一个显示 MSTVF 的固定基数估值影响的整体执行计划的子集。The following image depicts a Live Query Statistics output, a subset of an overall execution plan that shows the impact of fixed cardinality estimates from MSTVFs. 可查看实际行流与估计行数。You can see the actual row flow vs. estimated rows. 有三个值得注意的计划区域(从右到左显示):There are three noteworthy areas of the plan (flow is from right to left):

  1. MSTVF 表扫描具有 100 行的固定估值。The MSTVF Table Scan has a fixed estimate of 100 rows. 然而,对于此示例,有 527,597 行流经此 MSTVF 表扫描,如通过“527597 行,共 100 行”的实际与估值的实时查询统计信息中所示,因此固定估值偏差显著。For this example, however, there are 527,597 rows flowing through this MSTVF Table Scan, as seen in Live Query Statistics via the 527597 of 100 actual of estimated - so the fixed estimate is significantly skewed.
  2. 对于嵌套循环操作,仅假定联接的外侧将返回 100 行。For the Nested Loops operation, only 100 rows are assumed to be returned by the outer side of the join. 如果 MSTVF 实际返回惊人的行数,最好将另一联接算法一起使用。Given the high number of rows actually being returned by the MSTVF, you are likely better off with a different join algorithm altogether.
  3. 对于哈希匹配操作,请注意小型警告符号,在本示例中指示到磁盘的溢出。For the Hash Match operation, notice the small warning symbol, which in this case is indicating a spill to disk.

行流与估计行数

将之前的计划与通过启用交替执行生成的实际计划进行对比:Contrast the prior plan with the actual plan generated with interleaved execution enabled:

交错的计划

  1. 请注意,MSTVF 表扫描现可反映准确的基数估值。Notice that the MSTVF table scan now reflects an accurate cardinality estimate. 另请注意此表扫描的重新排序和其他操作。Also notice the re-ordering of this table scan and the other operations.
  2. 而关于联接算法,我们已改为从嵌套循环操作切换到哈希匹配操作,后者在涉及大量行时更优。And regarding join algorithms, we have switched from a Nested Loop operation to a Hash Match operation instead, which is more optimal given the large number of rows involved.
  3. 另请注意,我们不再发出溢出警告,因为我们将基于从 MSTVF 表扫描流出的真实行数授予更多内存。Also notice that we no longer have spill-warnings, as we're granting more memory based on the true row count flowing from the MSTVF table scan.

交错执行符合条件的语句Interleaved execution eligible statements

交错执行中的 MSTVF 引用语句当前必须处于只读,且不为数据修改操作的一部分。MSTVF referencing statements in interleaved execution must currently be read-only and not part of a data modification operation. 此外,如果 MSTVF 不使用运行时常数,则其不适合用于交错执行。Also, MSTVFs are not eligible for interleaved execution if they do not use runtime constants.

交错执行的优点Interleaved execution benefits

一般情况下,估计行数与实际行数之间的偏差越大,下游计划操作数翻倍,则性能影响越大。In general, the higher the skew between the estimated vs. actual number of rows, coupled with the number of downstream plan operations, the greater the performance impact. 一般来说,交错执行有益于以下情况中的查询:In general, interleaved execution benefits queries where:

  1. 对于中间结果集(本例中为 MSTVF),估计行数和实际行数之间存在巨大偏差。There is a large skew between the estimated vs. actual number of rows for the intermediate result set (in this case, the MSTVF).
  2. 整体查询对中间结果的大小更改十分敏感。And the overall query is sensitive to a change in the size of the intermediate result. 这通常发生在查询计划中的该子树上存在复杂树的情况。This typically happens when there is a complex tree above that subtree in the query plan. MSTVF 中简单的 SELECT * 不会获益于交错执行。A simple SELECT * from an MSTVF will not benefit from interleaved execution.

交错执行的开销Interleaved execution overhead

开销应为最少-到-无。The overhead should be minimal-to-none. MSTVF 已在引入交错执行前具体化,但区别是现在已允许延迟优化,并可利用具体化行集的基数估值。MSTVFs were already being materialized prior to the introduction of interleaved execution, however the difference is that now we're now allowing deferred optimization and are then leveraging the cardinality estimate of the materialized row set. 与任何影响计划的更改一样,某些计划更改后虽可获得更好的子树基数,但同时也会使整体查询计划变得更糟。As with any plan affecting changes, some plans could change such that with better cardinality for the subtree we get a worse plan for the query overall. 缓解可包括还原兼容级别或使用查询存储来强制计划的非回归版本。Mitigation can include reverting the compatibility level or using Query Store to force the non-regressed version of the plan.

交错执行和连续执行Interleaved execution and consecutive executions

缓存交错执行计划后,包含首次执行的修订后的估值的计划将用于连续执行,无需重新实例化交错执行。Once an interleaved execution plan is cached, the plan with the revised estimates on the first execution is used for consecutive executions without re-instantiating interleaved execution.

跟踪交错执行活动Tracking interleaved execution activity

可在实际查询执行计划中查看使用情况属性:You can see usage attributes in the actual query execution plan:

执行计划属性Execution Plan attribute 描述Description
ContainsInterleavedExecutionCandidatesContainsInterleavedExecutionCandidates 适用于 QueryPlan 节点。Applies to the QueryPlan node. 如果为“true”,表示计划包含交错执行候选项。When true, means the plan contains interleaved execution candidates.
IsInterleavedExecutedIsInterleavedExecuted TVF 节点的 RelOp 下的 RuntimeInformation 元素的属性。Attribute of the RuntimeInformation element under the RelOp for the TVF node. 如果为“true”,表示该操作已具体化为交错执行操作的一部分。When true, means the operation was materialized as part of an interleaved execution operation.

还可以通过以下 xEvent 跟踪交错执行匹配项:You can also track interleaved execution occurrences via the following xEvents:

XEventxEvent 描述Description
interleaved_exec_statusinterleaved_exec_status 发生交错执行时将引发此事件。This event fires when interleaved execution is occurring.
interleaved_exec_stats_updateinterleaved_exec_stats_update 此事件介绍交错执行更新的基数估值。This event describes the cardinality estimates updated by interleaved execution.
Interleaved_exec_disabled_reasonInterleaved_exec_disabled_reason 包含交错执行可能的候选项的查询实际未获取交错执行时,将引发此事件。This event fires when a query with a possible candidate for interleaved execution does not actually get interleaved execution.

必须执行查询才能允许交错执行修改 MSTVF 基数估值。A query must be executed in order to allow interleaved execution to revise MSTVF cardinality estimates. 但是,如果通过 ContainsInterleavedExecutionCandidates 显示计划属性存在交错执行候选项,则估计的执行计划仍将显示。However, the estimated execution plan still shows when there are interleaved execution candidates via the ContainsInterleavedExecutionCandidates showplan attribute.

交错执行缓存Interleaved execution caching

如果已从缓存中逐出或清除计划,则在执行查询时将出现使用交错执行的全新编译。If a plan is cleared or evicted from cache, upon query execution there is a fresh compilation that uses interleaved execution. 使用 OPTION (RECOMPILE) 的语句将创建使用交错执行的新计划,但不会进行缓存。A statement using OPTION (RECOMPILE) will create a new plan using interleaved execution and not cache it.

交错执行和查询存储互操作性Interleaved execution and query store interoperability

无法强制使用交错执行的计划。Plans using interleaved execution can be forced. 该计划是具有基于初始执行的正确基数估值的版本。The plan is the version that has corrected cardinality estimates based on initial execution.

在不更改兼容级别的情况下禁用交错执行Disabling interleaved execution without changing the compatibility level

可在数据库或语句范围内禁用交错执行,同时将数据库兼容性级别维持在 140 或更高。Interleaved execution can be disabled at the database or statement scope while still maintaining database compatibility level 140 and higher. 若要对源自数据库的所有查询执行禁用交错执行,请在对应数据库的上下文中执行以下命令:To disable interleaved execution for all query executions originating from the database, execute the following within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_INTERLEAVED_EXECUTION_TVF = ON;

启用后,此设置在 sys.database_scoped_configurations 将显示为已启用。When enabled, this setting will appear as enabled in sys.database_scoped_configurations. 若要对源自数据库的所有查询执行重新启用交错执行,请在适用的数据库的上下文中执行以下命令:To re-enable interleaved execution for all query executions originating from the database, execute the following within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_INTERLEAVED_EXECUTION_TVF = OFF;

此外,将 DISABLE_INTERLEAVED_EXECUTION_TVF 指定为 USE HINT 查询提示也可对特定查询禁用交错执行。You can also disable interleaved execution for a specific query by designating DISABLE_INTERLEAVED_EXECUTION_TVF as a USE HINT query hint. 例如:For example:

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 查询提示的优先级高于数据库范围的配置或跟踪标志设置。A USE HINT query hint takes precedence over a database scoped configuration or trace flag setting.

表变量延迟编译Table variable deferred compilation

备注

表变量延迟编译是公共预览功能。Table variable deferred compilation is a public preview feature.

表变量延迟编译功能提升了引用表变量的查询的计划质量和整体性能。Table variable deferred compilation improves plan quality and overall performance for queries that reference table variables. 在优化和初始编译期间,此功能传播基于实际表变量行计数的基数估计。During optimization and initial compilation, this feature propagates cardinality estimates that are based on actual table variable row counts. 这种准确的行计数信息可优化下游计划操作。This accurate row count information optimizes downstream plan operations.

表变量延迟编译会延迟编译引用表变量的语句,直到首次实际运行语句为止。Table variable deferred compilation defers compilation of a statement that references a table variable until the first actual run of the statement. 此延迟编译行为与临时表的相同。This deferred compilation behavior is the same as that of temporary tables. 此更改导致使用实际基数,而不是原始单行猜测。This change results in the use of actual cardinality instead of the original one-row guess.

可以在 Azure SQL 数据库中启用表变量延迟编译的公共预览版。You can enable the public preview of table variable deferred compilation in Azure SQL Database. 为此,请为要在运行查询时连接到的数据库启用兼容性级别 150。To do that, enable compatibility level 150 for the database you're connected to when you run the query.

有关详细信息,请参阅表变量延迟编译For more information, see Table variable deferred compilation.

标量 UDF 内联Scalar UDF inlining

备注

标量用户定义函数 (UDF) 内联是一项公共预览版功能。Scalar user-defined function (UDF) inlining is a public preview feature.

标量 UDF 内联会自动将标量 UDF 转换为关系表达式,Scalar UDF inlining automatically transforms scalar UDFs into relational expressions. 并将它们嵌入正在调用的 SQL 查询中。It embeds them in the calling SQL query. 此转换提升了利用标量 UDF 的工作负载的性能。This transformation improves the performance of workloads that take advantage of scalar UDFs. 标量 UDF 内联可便于实现 UDF 内部基于成本的操作优化。Scalar UDF inlining facilitates cost-based optimization of operations inside UDFs. 生成的是高效、面向集、并行的(而不是低效、迭代、串行的)执行计划。The results are efficient, set-oriented, and parallel instead of inefficient, iterative, serial execution plans. 在数据库兼容性级别 150 下默认启用此功能。This feature is enabled by default under database compatibility level 150.

有关详细信息,请参阅标量 UDF 内联For more information, see Scalar UDF inlining.

近似查询处理Approximate query processing

备注

APPROX_COUNT_DISTINCT 是一项公共预览版功能。APPROX_COUNT_DISTINCT is a public preview feature.

近似查询处理是新的功能系列。Approximate query processing is a new feature family. 它可以跨响应速度比绝对精度更为关键的大型数据集进行聚合。It aggregates across large datasets where responsiveness is more critical than absolute precision. 例如,要跨 100 亿行计算 COUNT(DISTINCT()),以供显示在仪表板上。An example is calculating a COUNT(DISTINCT()) across 10 billion rows, for display on a dashboard. 在这种情况下,绝对精度并不重要,而响应速度则至关重要。In this case, absolute precision isn't important, but responsiveness is critical. 新增的 APPROX_COUNT_DISTINCT 聚合函数返回组中唯一非 NULL 值的近似数。The new APPROX_COUNT_DISTINCT aggregate function returns the approximate number of unique non-null values in a group.

有关详细信息,请参阅 APPROX_COUNT_DISTINCT (Transact-SQL)For more information, see APPROX_COUNT_DISTINCT (Transact-SQL).

行存储上的批处理模式Batch mode on rowstore

备注

行存储上的批处理模式是一项公共预览版功能。Batch mode on rowstore is a public preview feature.

行存储上的批处理模式可实现分析工作负载的批处理模式执行,而无需列存储索引。Batch mode on rowstore enables batch mode execution for analytic workloads without requiring columnstore indexes. 此功能支持用于磁盘上堆和 B 树索引的批处理模式执行和位图筛选器。This feature supports batch mode execution and bitmap filters for on-disk heaps and B-tree indexes. 行存储上的批处理模式可实现对所有现有支持批处理模式的运算符的支持。Batch mode on rowstore enables support for all existing batch mode-enabled operators.

背景Background

SQL Server 2012 (11.x)SQL Server 2012 (11.x) 引入了一项可加速分析工作负载的新功能,即列存储索引。introduced a new feature to accelerate analytical workloads: columnstore indexes. 我们扩展了用例,并改进了每个后续版本中列存储索引的性能。We expanded the use cases and improved the performance of columnstore indexes in each subsequent release. 到目前为止,我们已将所有这些功能作为单一功能进行了表述和记录。Until now, we surfaced and documented all these capabilities as a single feature. 可以在表上创建列存储索引。You create columnstore indexes on your tables. 分析工作负载的速度会变快。And your analytical workload goes faster. 不过,有两套相关但不同的技术:However, there are two related but distinct sets of technologies:

  • 使用列存储索引,分析查询仅访问所需列中的数据。With columnstore indexes, analytical queries access only the data in the columns they need. 与传统行存储索引中的压缩相比,列存储格式的页压缩更高效。Page compression in the columnstore format is also more effective than compression in traditional rowstore indexes.
  • 使用批处理模式处理,查询运算符可以更高效地处理数据。With batch mode processing, query operators process data more efficiently. 它们一次处理一批行,而不是一行。They work on a batch of rows instead of one row at a time. 许多其他可伸缩性改进都与批处理模式处理相关。A number of other scalability improvements are tied to batch mode processing. 若要详细了解批处理模式,请参阅执行模式For more information on batch mode, see Execution modes.

两组功能协同工作,以改进输入/输出 (I/O) 和 CPU 利用率:The two sets of features work together to improve input/output (I/O) and CPU use:

  • 通过使用列存储索引,更多数据适合内存。By using columnstore indexes, more of your data fits in memory. 这可以减少 I/O 需求。That reduces the need for I/O.
  • 批处理模式处理可更有效地使用 CPU。Batch mode processing uses CPU more efficiently.

这两种技术尽可能利用彼此。The two technologies take advantage of each other whenever possible. 例如,批处理模式聚合可以计算为列存储索引扫描的一部分。For example, batch mode aggregates can be evaluated as part of a columnstore index scan. 我们还使用批处理模式联接和批处理模式聚合,更高效地处理使用运行长度编码压缩的列存储数据。We also process columnstore data that's compressed by using run-length encoding much more efficiently with batch mode joins and batch mode aggregates.

这两种功能可独立使用:The two features are independently usable:

  • 获取使用列存储索引的行模式计划。You get row mode plans that use columnstore indexes.
  • 获取仅使用行存储索引的批处理模式计划。You get batch mode plans that use only rowstore indexes.

结合使用这两种功能时,通常效果最佳。You usually get the best results when you use the two features together. 因此,到目前为止,SQL Server 查询优化器仅对涉及至少一个有列存储索引的表的查询考虑使用批处理模式处理。So until now, the SQL Server query optimizer considered batch mode processing only for queries that involve at least one table with a columnstore index.

对于一些应用程序,列存储索引并不是理想选择。Columnstore indexes aren't a good option for some applications. 应用程序可能会使用列存储索引不支持的其他一些功能。An application might use some other feature that isn't supported with columnstore indexes. 例如,就地修改与列存储压缩不兼容。For example, in-place modifications aren't compatible with columnstore compression. 因此,有聚集列存储索引的表不支持触发器。So triggers aren't supported on tables with clustered columnstore indexes. 更重要的是,列存储索引会增加 DELETE 和 UPDATE 语句的开销。More important, columnstore indexes add overhead for DELETE and UPDATE statements.

对于一些混合事务分析工作负载,工作负载的事务方面开销远比列存储索引的优势重要。For some hybrid transactional-analytical workloads, the overhead on a workload's transactional aspects outweighs the benefits of columnstore indexes. 此类方案只能通过批处理模式处理来改进 CPU 利用率。Such scenarios can improve CPU use from batch mode processing alone. 正因如此,“行存储上的批处理模式”功能对所有查询考虑使用批处理模式。That's why the batch mode on rowstore feature considers batch mode for all queries. 涉及哪些索引并不重要。It doesn't matter which indexes are involved.

可能会受益于行存储上的批处理模式的工作负载Workloads that might benefit from batch mode on rowstore

以下工作负载可能会受益于行存储上的批处理模式:The following workloads might benefit from batch mode on rowstore:

  • 工作负载的很大一部分由分析查询组成。A significant part of the workload consists of analytical queries. 通常情况下,这些查询有联接或聚合等运算符,可处理数十万行或更多行。Usually, these queries have operators like joins or aggregates that process hundreds of thousands of rows or more.
  • 工作负载受 CPU 限制。The workload is CPU bound. 如果瓶颈是 I/O,仍建议尽量考虑使用列存储索引。If the bottleneck is I/O, we still recommend that you consider a columnstore index, if possible.
  • 创建列存储索引会增加太多的工作负载事务部分开销。Creating a columnstore index adds too much overhead to the transactional part of your workload. 或者,创建列存储索引不可行,因为应用程序依赖列存储索引尚不支持的功能。Or, creating a columnstore index isn't feasible because your application depends on a feature that's not yet supported with columnstore indexes.

备注

只有通过减少 CPU 利用率,行存储上的批处理模式才能起到帮助作用。Batch mode on rowstore helps only by reducing CPU consumption. 如果瓶颈与 I/O 相关,且数据尚未缓存(“冷”缓存),那么行存储上的批处理模式不会改善运行时间。If your bottleneck is I/O related, and data isn't already cached ("cold" cache), batch mode on rowstore won't improve elapsed time. 同样,如果计算机上没有足够的内存可用来缓存所有数据,性能也不太可能得到提升。Similarly, if there isn't enough memory on the machine to cache all the data, a performance improvement is unlikely.

行存储上的批处理模式带来哪些变化?What changes with batch mode on rowstore?

除了迁移到兼容性级别 150 之外,无需自行更改任何内容,即可为候选工作负载启用行存储上的批处理模式。Other than moving to compatibility level 150, you don't have to change anything on your side to enable batch mode on rowstore for candidate workloads.

即使查询不涉及任何有列存储索引的表,查询处理器现在也使用启发式方法来决定是否考虑使用批处理模式。Even if a query doesn't involve any table with a columnstore index, the query processor now uses heuristics to decide whether to consider batch mode. 启发式方法包括以下检查:The heuristics consist of these checks:

  1. 初始检查输入查询中的表大小、使用的运算符和估计的基数。An initial check of table sizes, operators used, and estimated cardinalities in the input query.
  2. 其他检查点,因为优化器会发现新的、成本更低的查询计划。Additional checkpoints, as the optimizer discovers new, cheaper plans for the query. 如果这些替代计划没有大量使用批处理模式,优化器会停止探索批处理模式替代方案。If these alternative plans don't make significant use of batch mode, the optimizer stops exploring batch mode alternatives.

如果使用行存储上的批处理模式,则会发现在查询计划中实际运行模式为批处理模式。If batch mode on rowstore is used, you see the actual run mode as batch mode in the query plan. 扫描运算符对磁盘堆和 B 树索引使用批处理模式。The scan operator uses batch mode for on-disk heaps and B-tree indexes. 此批处理模式扫描可以评估批处理模式位图筛选器。This batch mode scan can evaluate batch mode bitmap filters. 还可以在计划中看到其他批处理模式运算符。You might also see other batch mode operators in the plan. 例如,哈希联接、基于哈希的聚合、排序、窗口聚合、筛选器、串联和计算标量运算符。Examples are hash joins, hash-based aggregates, sorts, window aggregates, filters, concatenation, and compute scalar operators.

RemarksRemarks

  • 查询计划并不总是使用批处理模式。Query plans don't always use batch mode. 查询优化器可能会认为批处理模式对查询没有益处。The query optimizer might decide that batch mode isn't beneficial for the query.
  • 查询优化器的搜索空间正在发生变化。The query optimizer's search space is changing. 因此,如果获取行模式计划,它可能与在较低兼容性级别获取的计划不同。So if you get a row mode plan, it might not be the same as the plan you get in a lower compatibility level. 另外,如果获取批处理模式计划,它可能与通过列存储索引获取的计划不同。And if you get a batch mode plan, it might not be the same as the plan you get with a columnstore index.
  • 鉴于新增的行存储上的批处理模式扫描,计划也可能会对混合列存储索引和行存储索引的查询发生变化。Plans might also change for queries that mix columnstore and rowstore indexes because of the new batch mode rowstore scan.
  • 新增的行存储上的批处理模式扫描当前存在以下限制:There are current limitations for the new batch mode on rowstore scan:
    • 它不会为内存中 OLTP 表,或除磁盘堆和 B 树以外的任何索引启动。It won't kick in for in-memory OLTP tables or for any index other than on-disk heaps and B-trees.
    • 如果提取或筛选大型对象 (LOB) 列,它也不会启动。It also won't kick in if a large object (LOB) column is fetched or filtered. 此限制包括稀疏列集和 XML 列。This limitation includes sparse column sets and XML columns.
  • 有些查询即使使用列存储索引,也无法使用批处理模式。There are queries that batch mode isn't used for even with columnstore indexes. 例如,涉及游标的查询。Examples are queries that involve cursors. 这些相同的排除项也扩展到行存储上的批处理模式。These same exclusions also extend to batch mode on rowstore.

配置行存储上的批处理模式Configure batch mode on rowstore

BATCH_MODE_ON_ROWSTORE 数据库范围内配置默认处于启用状态。The BATCH_MODE_ON_ROWSTORE database scoped configuration is on by default. 无需更改数据库兼容性级别,即可禁用行存储上的批处理模式:It disables batch mode on rowstore without requiring a change in database compatibility level:

-- 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;

可以通过数据库范围内配置来禁用行存储上的批处理模式。You can disable batch mode on rowstore via database scoped configuration. 但仍可使用 ALLOW_BATCH_MODE 查询提示,在查询一级替代设置。But you can still override the setting at the query level by using the ALLOW_BATCH_MODE query hint. 以下示例启用行存储的批处理模式,即使通过数据库作用域配置禁用了该功能:The following example enables batch mode on rowstore even with the feature disabled via database scoped configuration:

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 查询提示,对特定查询禁用行存储上的批处理模式。You can also disable batch mode on rowstore for a specific query by using the DISALLOW_BATCH_MODE query hint. 请参阅以下示例:See the following example:

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'));

另请参阅See also

SQL Server 数据库引擎和 Azure SQL 数据库的性能中心 Performance Center for SQL Server Database Engine and Azure SQL Database
查询处理体系结构指南 Query processing architecture guide
显示计划逻辑运算符和物理运算符参考 Showplan logical and physical operators reference
联接 Joins
演示自适应查询处理 Demonstrating adaptive query processing
演示智能 QPDemonstrating intelligent QP