了解查询计划

  • 11 分钟

在深入了解执行计划的详细信息之前，对优化器的工作原理有一个基本的了解很有帮助。 SQL Server 使用称为基于成本的查询优化器。 查询优化器根据所使用列的统计信息，以及每个查询计划中可用于每个操作的可能索引数来计算多个可能计划的成本。 根据此信息，它会得出每个计划的总成本。 某些复杂的查询有成千上万个可能的执行计划。 优化器不评估每个可能的计划，而是使用试探法来确定可能具有良好性能的计划。 然后，优化器将在为给定查询计算的所有计划中选择成本最低的计划。

由于查询优化器是基于成本的，因此为它提供良好的输入对于决策来说非常重要。 SQL Server 用于跟踪列和索引中数据分布的统计信息需要保持最新状态，否则会导致生成不理想的执行计划。 SQL Server 会随着表中的数据更改自动更新其统计信息；但是，对于快速变化的数据，可能需要更频繁地进行更新。 引擎在生成计划时会使用许多因素，包括数据库的兼容性级别、基于统计信息的行估算和可用索引数。

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

  1. 系统将分析查询的语法是否正确，如果语法正确，则会生成数据库对象的分析树。
  2. 步骤 1 中的分析树将作为数据库引擎组件（名为 Algebrizer）的输入，用于进行绑定。 此步骤验证查询中的列和对象是否存在，并确定要为给定查询处理的数据类型。 此步骤会输出查询处理器树，该树将在步骤 3 的输入中使用。
  3. 由于查询优化在 CPU 使用方面是一个成本相对较高的过程，数据库引擎会将执行计划缓存到称为计划缓存的特殊内存区域中。 如果给定查询的计划已存在，系统会从缓存中检索该计划。 计划存储在缓存中的每个查询都有一个根据查询中的 T-SQL 生成的哈希值。 此值称为 query_hash。 引擎将为当前查询生成 query_hash，然后检查它是否与计划缓存中的任何现有查询相匹配。
  4. 如果该计划不存在，查询优化器将使用基于成本的优化器，根据查询中使用的关于列、表和索引的统计信息来生成多个执行计划选项，如上所述。 此步骤的输出是一个查询执行计划。
  5. 然后，系统将使用从计划缓存中拉取的执行计划（或步骤 4 中生成的新计划）来执行查询。 此步骤的输出为查询结果。

注意

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

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

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

在此示例中，SQL Server 将检查表 FactResellerSales 中是否存在“OrderDate”、“ShipDate”和“SalesAmount”列。 如果这些列存在，它将为查询生成哈希值，并检查计划缓存中是否有匹配的哈希值。 如果有哈希值匹配的查询计划，引擎将尝试重新使用该计划。 如果没有哈希值匹配的计划，它将检查 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，但也可以在 Azure Data Studio 中查看预计计划。 还有支持查看图形执行计划的第三方工具。

可以查看三种不同类型的执行计划。

估计的执行计划

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

实际执行计划

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

实时查询统计信息

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