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

项目
03/24/2023

适用于： SQL Server

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

什么是优化器超时？

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

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

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

如何检测优化器超时？

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

复杂查询

你有一个复杂的查询，涉及许多联接的表 (例如，) 联接八个或更多个表。
慢速查询

查询的运行速度可能比在另一个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)
- 从 2017 SQL Server (14.x) ) 开始的自适应联接 (
有关详细信息，请参阅联接。
并行或串行执行部分查询？

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

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

子句) 中的 WHERE 查询谓词 (筛选器
约束的存在
设计精良和最新统计信息的组合

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

如何考虑因素的示例

为了说明，我们以三个表 (t1、 t2和 t3) 联接为例，每个表都有一个聚集索引和非聚集索引。

首先，考虑物理联接类型。此处涉及两个联接。而且，由于有三种物理联接可能性 (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 种可能性：

使用 t2 联接t1，然后使用t3
使用 t3 联接t1，然后使用t2
使用 t3 联接t2，然后使用t1

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

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

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

联接订单：P (n，n-2) = n！/2
联接类型：3^n-1
具有 seek 和 scan 方法的不同索引类型：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 示例。针对 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 和派生表类似，联接可以在视图中隐藏。例如，两个视图之间的联接最终可能是涉及八个表的单个查询：

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)

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

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 配置。当SQL Server计算并创建查询计划时，更改 CE 配置可能会导致 QO 选取不同的路径。因此，即使出现优化器超时问题，也可能最终获得比使用备用 CE 配置选择的计划性能更佳的计划。有关详细信息，请参阅如何激活最佳查询计划 (基数估计) 。

步骤 6：启用优化器修复

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

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

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

通过