了解查询计划

  • 11 分钟

请务必在深入了解执行计划详细信息之前，了解数据库优化器的工作原理。 SQL Server 使用基于成本的查询优化器，将根据有关所使用的列以及查询计划中每个操作的潜在索引的统计信息计算多个可能计划的成本。 此信息可帮助优化器确定每个计划的总成本。 复杂的查询可以有数千个可能的执行计划，但优化器不会对每个计划都进行评估。 相反，它使用启发法来识别可能表现良好的计划，然后从评估的计划中选择成本最低的计划。

由于查询优化器以成本为基准，因此必须为其提供准确的决策输入。 SQL Server 依靠统计信息来跟踪列和索引中数据的分布情况，并且这些统计信息必须是最新的，以避免生成欠佳的执行计划。 尽管 SQL Server 会在表中的数据变化时自动更新其统计信息，但快速变更的数据可能需要更频繁的更新。 优化器会在生成计划时考虑许多因素，包括数据库的兼容性级别、基于统计信息的行估算和可用索引。

用户向数据库引擎提交查询时，将发生以下过程：

  1. 系统将解析查询以获取正确的语法。如果正确，则会生成数据库对象的解析树。
  2. 分析树随后被输入到名为 Algebrizer 的数据库引擎组件，以进行绑定。 此步骤会验证查询中的列和对象是否存在并识别正在处理的数据类型。 输出结果将会是查询处理器树，该树将用作下一步的输入。
  3. 查询优化是 CPU 密集型操作，因此数据库引擎会将执行计划缓存在一个称为“计划缓存”的特殊内存区域中。 如果针对查询的计划已存在，则会从缓存中检索它。 缓存中的每个查询都有一个基于查询中的 T-SQL 生成的哈希值，称为 query_hash。 引擎为当前查询生成 query_hash ，并检查计划缓存中的匹配项。
  4. 如果不存在计划，查询优化器将使用其基于成本的优化器来根据有关查询中使用的列、表和索引的统计信息生成多个执行计划选项。 输出结果将会是查询执行计划。
  5. 查询使用计划缓存中的执行计划或上一步中生成的新计划执行。 输出结果将会是查询的结果。

注意

若要详细了解查询处理器的工作原理，请参阅 查询处理体系结构指南

让我们看一个示例。 请考虑下列查询：

SELECT orderdate,
        AVG(salesAmount)
FROM FactResellerSales
WHERE ShipDate = '2013-07-07'
GROUP BY orderdate;

在此示例中，SQL Server 检查 FactResellerSales 表中是否存在 OrderDate、ShipDate 和 SalesAmount 列。 如果这些列存在，SQL Server 将为查询生成哈希值，并检查计划缓存中是否存在匹配的哈希值。 如果找到匹配的哈希值，引擎将尝试重复使用该计划。 如果未找到匹配的哈希值，SQL Server 将检查 OrderDate 和 ShipDate 列上的可用统计信息。

引用 ShipDate 列的 WHERE 子句在此查询中称为谓词。 如果存在包含 ShipDate 列的非聚集索引，则 SQL Server 可能会将其包含在计划中，前提是成本低于从聚集索引检索数据的成本。 然后，优化器从可用选项中选择成本最低的计划，并执行查询。

查询计划结合了一系列关系运算符来检索数据和捕获信息，例如估计行计数。 执行计划的另一个元素是数据联接或排序等操作所需的内存，称为“内存授予”。 内存授予突显了统计信息的重要性。 如果 SQL Server 估计运算符会返回 10,000,000 行，但实际上返回了 100 行，则向查询分配的内存授予将会偏大。 内存授予过大可能会导致两个问题。 首先，查询可能会遇到 RESOURCE_SEMAPHORE 等待，指示它正在等待 SQL Server 分配大量内存。 在执行查询之间，SQL Server 默认等待的时间为查询耗时（以秒为单位）的 25 倍，最长可达 24 小时。 其次，如果在执行查询时没有足够的内存可用，则会溢出到 tempdb，这比在内存中运行的速度慢。

执行计划还会存储有关查询的其他元数据，例如数据库兼容性级别、并行度，以及如果查询发生参数化所提供的参数。

查询计划支持以图形表示形式或基于文本的格式查看。 基于文本的选项需使用 SET 命令调用，且仅适用于当前连接。 无论是在何处，只要可以运行 T-SQL 查询，就可以查看这些计划。

大多数 DBA 更喜欢图形计划，因为它们允许你以整体形式查看计划，包括计划 的形状 。 查看和保存图形查询计划的方式有多种。 用于此目的的最常见工具是 SQL Server Management Studio。 此外，还有第三方工具支持查看图形执行计划。

有三种不同类型的执行计划。

估计的执行计划

此类型的执行计划由查询优化器生成。 系统将基于编译查询时数据库中存在的统计信息进行估算，以此为基础确定查询内存授予的元数据和大小。 若要查看基于文本的估计计划，请在执行查询之前运行命令 SET SHOWPLAN_ALL ON。 运行查询时，你会看到执行计划的步骤，但查询不会执行，并且你不会看到任何结果。 SET 选项将保持有效状态，直到将其设置为 OFF。

实际执行计划

这种类型的计划与估计计划相同；但是，它还包括查询的执行上下文。 此上下文包含估计和实际的行计数、任何执行警告、实际并行度（使用的处理器数），以及执行期间使用的已用和 CPU 时间。 若要查看基于文本的实际计划，请在执行查询之前运行命令 SET STATISTICS PROFILE ON。 查询将执行，你将同时获得相应计划和结果。

实时查询统计信息

此计划查看选项将估计计划和实际计划合并成一个动画计划，以通过运算符显示执行进度。 它每秒刷新一次，并显示通过运算符流动的实际行数。 实时查询统计信息的另一个好处是，它显示了从运算符到运算符的切换，这有助于排查性能问题。 由于这种类型的计划是动画形式的，因此它仅可作为图形计划提供。