排查查询优化器超时影响的慢查询问题

项目
12/14/2024

适用范围：SQL Server

本文介绍优化器超时、如何影响查询性能以及如何优化性能。

什么是优化器超时？

SQL Server 使用基于 成本的查询优化器 （QO）。有关 QO 的信息，请参阅查询处理体系结构指南。基于成本的查询优化器在生成和评估多个查询计划后，会选择成本最低的查询执行计划。 SQL Server 查询优化器的目标之一是，与查询执行相比，在查询优化中花费合理的时间。优化查询的速度应该比执行查询快得多。若要实现此目标，QO 具有一个内置的任务阈值，在任务停止优化过程之前需要考虑。当在 QO 考虑所有可能的计划之前达到阈值时，它将达到优化器超时限制。优化器超时事件在查询计划中报告为“提前终止语句优化的原因”下的 TimeOut。请务必了解，此阈值不是基于时钟时间，而是基于优化器考虑的可能性数。在当前的 SQL Server QO 版本中，在达到超时之前，会考虑超过 500 万个任务。

优化器超时设计为 SQL Server，在许多情况下，它并不影响查询性能。但是，在某些情况下，SQL 查询计划选择可能会受到优化器超时的负面影响，并且查询性能可能会变慢。遇到此类问题时，了解优化器超时机制以及复杂查询的影响如何有助于排查和提高查询速度。

达到优化器超时阈值的结果是 SQL Server 未考虑优化的全部可能性集。也就是说，它可能错过了可能导致执行时间较短的计划。 QO 将停止在阈值处，并考虑此时成本最低的查询计划，即使可能有更好的未开发选项。请记住，达到优化器超时后选择的计划可能会为查询生成合理的执行持续时间。但是，在某些情况下，所选计划可能会导致查询执行效果欠佳。

如何检测优化器超时？

下面是指示优化器超时的症状：

复杂查询

你有一个涉及大量联接表的复杂查询（例如，联接了 8 个或更多个表）。
查询速度缓慢

查询的运行速度可能比在另一个 SQL Server 版本或系统上运行的速度慢或慢。

查询计划显示 StatementOptmEarlyAbortReason=Timeout

查询计划显示在 StatementOptmEarlyAbortReason="TimeOut" XML 查询计划中。

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.518" Build="13.0.5201.2" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
  <Batch>
   <Statements>
    <StmtSimple  ..." StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="TimeOut" ......>
    ...
   <Statements>
  <Batch>
<BatchSequence>

检查Microsoft SQL Server Management Studio 中最左侧的计划运算符的属性。可以看到“提前终止语句优化的原因”值是 TimeOut。

导致优化器超时的原因是什么？

没有简单的方法来确定哪些条件会导致优化器阈值达到或超过。以下部分是一些因素，这些因素会影响 QO 在查找最佳计划时浏览的计划数。

应按什么顺序联接表？

下面是三个表联接的执行选项的示例（Table1，， Table2Table3）：
- 联接 Table1 和 Table2 结果与 Table3
- 联接 Table1 和 Table3 结果与 Table2
- 联接 Table2 和 Table3 结果与 Table1
注意： 表数越大，可能性就越大。
用于从表中检索行的堆或二进制树（HoBT）访问结构是什么？
- 聚集索引
- 非聚集索引 1
- 非聚集索引 2
- 表堆
要使用的物理访问方法是什么？
- 索引查找
- 索引扫描
- 表扫描
要使用的物理联接运算符是什么？
- 嵌套循环联接（NJ）
- 哈希联接（HJ）
- 合并联接（MJ）
- 自适应联接（从 SQL Server 2017（14.x）开始）
有关详细信息，请参阅联接。
并行或串行执行查询的各个部分？

有关详细信息，请参阅并行查询处理。

虽然以下因素将减少考虑的访问方法的数量，因此可以考虑以下几点：

查询谓词（子句中的 WHERE 筛选器）
约束的存在
设计良好且最新统计信息的组合

注意： QO 达到阈值并不意味着它最终会出现较慢的查询。在大多数情况下，查询性能良好，但在某些情况下，你可能会看到查询执行速度较慢。

如何考虑因素的示例

为了说明，让我们以三个表（t1和）之间的联接为例，t2t3每个表都有聚集索引和非聚集索引。

首先，考虑物理联接类型。此处涉及两个联接。而且，由于有三种物理联接可能性（NJ、HJ 和 MJ），因此可以通过 3² = 9 种方式执行查询。

NJ - NJ
NJ - HJ
NJ - MJ
HJ - NJ
HJ - HJ
HJ - MJ
MJ - NJ
MJ - HJ
MJ - MJ

然后，考虑使用排列计算的联接顺序：P （n， r）。前两个表的顺序并不重要，因此可以有 P（3,1） = 3 种可能性：

与t1后跟t2t3
与t1后跟t3t2
与t2后跟t3t1

接下来，请考虑可用于数据检索的聚集索引和非聚集索引。此外，对于每个索引，我们有两种访问方法，即查找或扫描。这意味着，对于每个表，有 2^{个 2} = 4 种选择。我们有三个表，因此可以有 4³ = 64 种选择。

最后，考虑到所有这些条件，可以有 9*3*64 = 1728 可能的计划。

现在，假设查询中联接了 n 个表，每个表都有聚集索引和非聚集索引。请考虑下列因素：

联接订单：P（n，n-2） = n！/2
联接类型：3^n-1
具有查找和扫描方法的不同索引类型：4ⁿ

将上述所有计划相乘，我们可以获取可能的计划数：2*n！*12^n-1。当 n = 4 时，数字为 82,944。当 n = 6 时，数字为 358,318,080。因此，随着查询中涉及的表数的增加，可能的计划数以几何方式增加。此外，如果包括并行度和其他因素的可能性，可以想象将考虑多少个可能的计划。因此，具有大量联接的查询更有可能达到优化器超时阈值，而不是具有较少联接的优化器超时阈值。

请注意，上述计算说明了最坏的情况。正如我们指出的，有一些因素将减少可能性，如筛选器谓词、统计信息和约束。例如，筛选器谓词和更新的统计信息将减少物理访问方法的数量，因为使用索引查找比扫描更有效。这也将导致较小的联接选择等。

为何会看到具有简单查询的优化器超时？

查询优化器没有什么简单。有许多可能的方案，复杂性如此之高，难以掌握所有可能性。查询优化器可以根据在特定阶段找到的计划的成本动态设置超时阈值。例如，如果找到相对高效的计划，则搜索更好的计划的任务限制可能会减少。因此，低估基数估计（CE）可能是提前达到优化器超时的一种方案。在这种情况下，调查的重点是 CE。与运行上一部分讨论的复杂查询的情况相比，这种情况比较罕见，但有可能。

解决方法

查询计划中出现的优化器超时并不一定意味着这是导致查询性能不佳的原因。在大多数情况下，可能不需要对此情况执行任何操作。 SQL Server 最终得到的查询计划可能合理，正在运行的查询可能表现良好。你可能永远不知道你遇到了优化器超时。

如果发现需要优化和优化，请尝试以下步骤。

步骤 1：建立基线

检查是否可以在不同的 SQL Server 版本、使用不同的 CE 配置或不同的系统（硬件规范）上使用相同的数据集执行相同的查询。性能优化的指导原则是“没有基线没有性能问题”。因此，为同一查询建立基线非常重要。

步骤 2：查找导致优化器超时的“隐藏”条件

详细检查查询以确定其复杂性。初始检查后，查询可能并不明显，并且涉及许多联接。此处的一个常见方案是涉及视图或表值函数。例如，在表面上，查询可能看起来很简单，因为它联接了两个视图。但是，当你检查视图中的查询时，你可能会发现每个视图联接了七个表。因此，当两个视图联接时，最终会出现一个 14 个表联接。如果查询使用以下对象，请向下钻取到每个对象，以查看其内部的基础查询的外观：

对于所有这些方案，最常见的解决方法是重写查询并将其分解为多个查询。请参阅步骤 7：优化查询以获取更多详细信息。

子查询或派生表

以下查询是一个示例，它在每个查询中联接两组单独的查询（派生表）和 4-5 个联接。但是，在 SQL Server 分析后，它将编译为一个查询，其中包含八个联接的表。

SELECT ...
  FROM 
    ( SELECT ...
        FROM t1 
        JOIN t2 ON ...
        JOIN t3 ON ...
        JOIN t4 ON ...
        WHERE ...
    ) AS derived_table1
INNER JOIN
  ( SELECT ...
      FROM t5 
      JOIN t6 ON ...
      JOIN t7 ON ...
      JOIN t8 ON ...
      WHERE ...
  ) AS derived_table2 
ON derived_table1.Co1 = derived_table2.Co10 
AND derived_table1.Co2 = derived_table2.Co20

常见表表达式（CTE）

使用多个公用表表达式（CTE）并不是简化查询并避免优化器超时的适当解决方案。多个 CTE 只会增加查询的复杂性。因此，解决优化器超时时使用 CTE 会适得其反。 CTE 看起来在逻辑上中断查询，但它们将合并为单个查询，并作为单个表的单个联接进行优化。

下面是一个 CTE 示例，该 CTE 将编译为包含多个联接的单个查询。似乎针对my_cte的查询是一个双对象简单联接，但事实上，CTE 中还联接了另外七个表。

WITH my_cte AS (
  SELECT ...
    FROM t1 
    JOIN t2 ON ...
    JOIN t3 ON ...
    JOIN t4 ON ...
    JOIN t5 ON ...
    JOIN t6 ON ...
    JOIN t7 ON ...
    WHERE ... )

SELECT ...
  FROM my_cte 
  JOIN t8 ON ...

视图

确保已检查视图定义并获取涉及的所有表。与 CTE 和派生表类似，联接可以隐藏在视图中。例如，两个视图之间的联接最终可能是涉及 8 个表的单个查询：

CREATE VIEW V1 AS 
  SELECT ...
    FROM t1 
    JOIN t2 ON ...
    JOIN t3 ON ...
    JOIN t4 ON ...
    WHERE ...
GO

CREATE VIEW V2 AS 
  SELECT ...
    FROM t5 
    JOIN t6 ON ...
    JOIN t7 ON ...
    JOIN t8 ON ...
    WHERE ...
GO

SELECT ...
  FROM V1 
  JOIN V2 ON ...

表值函数（TVF）

某些联接可能隐藏在 TFV 中。以下示例显示了两个 TFV 之间的联接，表可能是一个九表联接。

CREATE FUNCTION tvf1() RETURNS TABLE
AS RETURN
  SELECT ...
    FROM t1 
    JOIN t2 ON ...
    JOIN t3 ON ...
    JOIN t4 ON ...
    WHERE ...
GO 

CREATE FUNCTION tvf2() RETURNS TABLE
AS RETURN
  SELECT ...
    FROM t5
    JOIN t6 ON ...
    JOIN t7 ON ...
    JOIN t8 ON ...
    WHERE ...
GO

SELECT ...
  FROM tvf1() 
  JOIN tvf2() ON ...
  JOIN t9 ON ...

Union

联合运算符将多个查询的结果合并到单个结果集中。它们还将多个查询合并到单个查询中。然后，可能会获得一个复杂的查询。以下示例最终将包含一个涉及 12 个表的查询计划。

SELECT ...
  FROM t1 
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...

UNION ALL

SELECT ...
  FROM t5 
  JOIN t6 ON ...
  JOIN t7 ON ...
  JOIN t8 ON ...

UNION ALL

SELECT ...
  FROM t9
  JOIN t10 ON ...
  JOIN t11 ON ...
  JOIN t12 ON ...

步骤 3：如果你有运行速度更快的基线查询，请使用其查询计划

如果确定从步骤 1 获取的特定基线计划更适合通过测试进行查询，请使用以下选项之一强制 QO 选择该计划：

步骤 4：减少计划选择

若要减少优化器超时的可能性，请尝试降低 QO 在选择计划时需要考虑的可能性。此过程涉及使用不同的提示选项测试查询。与大多数使用 QO 的决策一样，选择并不总是确定性的，因为需要考虑多种因素。因此，没有一个保证成功的策略，所选计划可能会改善或降低所选查询的性能。

强制加入顺序

用于 OPTION (FORCE ORDER) 消除顺序排列：

SELECT ...
  FROM t1
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
OPTION (FORCE ORDER)

减少 JOIN 可能性

如果其他替代项没有帮助，请尝试通过限制物理联接运算符的选择和联接提示来减少查询计划组合。例如： OPTION (HASH JOIN, MERGE JOIN)或 OPTION (HASH JOIN, LOOP JOIN) OPTION (MERGE JOIN)。

注意： 使用这些提示时应小心。

在某些情况下，使用较少的联接选项限制优化器可能会导致最佳联接选项不可用，并且实际上可能会降低查询速度。此外，在某些情况下，优化器（例如行目标）需要特定的联接，如果该联接不是选项，查询可能无法生成计划。因此，针对特定查询的联接提示后，请检查是否找到一个可提供更好性能的组合，并消除优化器超时。

下面是有关如何使用此类提示的两个示例：

用于 OPTION (HASH JOIN, LOOP JOIN) 仅允许哈希和循环联接，并避免在查询中合并联接：

SELECT ...
  FROM t1 
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
  JOIN t5 ON ...
OPTION (HASH JOIN, LOOP JOIN)

在两个表之间强制实施特定联接：

SELECT ...
  FROM t1 
  INNER MERGE JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
  JOIN t5 ON ...

步骤 5：更改 CE 配置

尝试在旧版 CE 和新 CE 之间切换来更改 CE 配置。更改 CE 配置可能会导致当 SQL Server 评估并创建查询计划时，QO 选取不同的路径。因此，即使出现优化器超时问题，你最终也可能得到一个计划，该计划的性能比使用备用 CE 配置选择的计划更理想。有关详细信息，请参阅如何激活最佳查询计划（基数估计）。

步骤 6：启用优化器修复

如果尚未启用查询优化器修复，请考虑使用以下两种方法之一来启用它们：

服务器级别：使用跟踪标志 T4199。
数据库级别：对 SQL Server 2016 及更高版本使用 ALTER DATABASE SCOPED CONFIGURATION ..QUERY_OPTIMIZER_HOTFIXES = ON 或更改数据库兼容性级别。

QO 修复可能会导致优化器在计划探索中采用不同的路径。因此，它可以选择更理想的查询计划。有关详细信息，请参阅 SQL Server 查询优化器修补程序跟踪标志 4199 服务模型。

步骤 7：优化查询

请考虑使用临时表将单个多表查询拆分为多个单独的查询。分解查询只是简化优化器任务的方法之一。请参阅以下示例：

SELECT ...
  FROM t1
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
  JOIN t5 ON ...
  JOIN t6 ON ...
  JOIN t7 ON ...
  JOIN t8 ON ...

若要优化查询，请尝试通过在临时表中插入联接结果的一部分，将单个查询分解为两个查询：

SELECT ...
  INTO #temp1
  FROM t1 
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...

GO

SELECT ...
  FROM #temp1
  JOIN t5 ON ...
  JOIN t6 ON ...
  JOIN t7 ON ...
  JOIN t8 ON ...

通过

排查查询优化器超时影响的慢查询问题

什么是优化器超时？

如何检测优化器超时？

导致优化器超时的原因是什么？

如何考虑因素的示例

为何会看到具有简单查询的优化器超时？

解决方法

步骤 1：建立基线

步骤 2：查找导致优化器超时的“隐藏”条件

子查询或派生表

常见表表达式（CTE）

视图

表值函数（TVF）

Union

步骤 3：如果你有运行速度更快的基线查询，请使用其查询计划

步骤 4：减少计划选择

强制加入顺序

减少 JOIN 可能性

步骤 5：更改 CE 配置

步骤 6：启用优化器修复

步骤 7：优化查询

反馈

其他资源

通过

排查查询优化器超时影响的慢查询问题

什么是优化器超时？

如何检测优化器超时？

导致优化器超时的原因是什么？

如何考虑因素的示例

为何会看到具有简单查询的优化器超时？

解决方法

步骤 1：建立基线

步骤 2：查找导致优化器超时的“隐藏”条件

子查询或派生表

常见表表达式 （CTE）

视图

表值函数 （TVF）

Union

步骤 3：如果你有运行速度更快的基线查询，请使用其查询计划

步骤 4：减少计划选择

强制加入顺序

减少 JOIN 可能性

步骤 5：更改 CE 配置

步骤 6：启用优化器修复

步骤 7：优化查询

反馈

其他资源

常见表表达式（CTE）

表值函数（TVF）