说明预计查询计划和实际查询计划

  • 13 分钟

实际执行计划与估计的执行计划可能会令人困惑。 不同之处在于，实际计划包括未在预计计划中捕获的运行时统计信息。 使用的运算符和执行顺序与几乎所有情况下的估计计划相同。 另一个考虑因素是捕获实际执行计划需要执行查询，这一点可能非常耗时或不可行。 例如， UPDATE 语句只能运行一次。 但是，如果需要查看查询结果和计划，则需要使用实际计划选项之一。

在 SQL Server Management Studio 中生成的预计执行计划的屏幕截图。

如图所示，可以通过选择估计查询计划框（或使用键盘命令 Control+L）指示的按钮，在 SSMS 中生成估计计划。 可以通过选择显示的图标（或使用键盘命令 Control+M）并执行查询来生成实际计划。 两个选项按钮的工作方式不同。 “ 包含估计查询计划 ”按钮会立即响应突出显示的任何查询（或整个工作区（如果未突出显示任何查询），而 “包含实际查询计划 ”按钮要求执行查询。

执行查询和生成预计执行计划会产生开销，因此，应在生产环境中仔细查看执行计划。

通常，可以在编写查询时使用估计的执行计划来了解其性能特征、识别缺失索引或检测查询异常。 实际执行计划最适合用于了解查询的运行时性能，最重要的是，统计信息中的差距导致查询优化器根据可用数据做出不理想的选择。

阅读查询计划

执行计划显示检索满足查询所需的数据时，数据库引擎正在执行哪些任务。 我们来深入了解计划。

SELECT [stockItemName]
 ,[UnitPrice] * [QuantityPerOuter] AS CostPerOuterBox
 ,[QuantityonHand]

FROM [Warehouse].[StockItems] s
 JOIN [Warehouse].[StockItemHoldings] sh ON s.StockItemID = sh.StockItemID
ORDER BY CostPerOuterBox;

此查询将 StockItems 表联接到 StockItemHoldings 表，其中 StockItemID 列中的值相等。 数据库引擎必须先标识这些行，然后才能处理查询的其余部分。

查询执行计划的屏幕截图。

计划中的每个图标都表示一个特定作，该作对应于构成执行计划的各种作和决策。 SQL Server 数据库引擎具有 100 多个查询运算符，这些运算符可以是执行计划的一部分。 在每个运算符图标下，与查询的总成本相比，成本百分比是一个百分比。 即使是显示成本为 0% 的作也仍表示一些成本。 事实上，0% 是由于舍入，因为图形计划成本始终显示为整数，而实际百分比小于 0.5%。

执行计划中的执行流从右到左，从上到下，因此在此计划中， StockItemHoldings.PK_Warehouse_StockItemHoldings 聚集索引上的聚集索引扫描作是查询中的第一个作。 连接运算符的线条的宽度取决于预计流向下一个操作符的数据行数。 粗箭头是大型运算符向大型运算符传输的指示器，可能表示有机会优化查询。 还可以将鼠标悬停在作员上，并在工具提示中查看其他信息。

StockItems 表上聚集索引扫描操作的工具提示的屏幕截图。

工具提示突出显示了估计计划的成本和估算值，对于实际计划，它包括与实际行和成本的比较。 每个运算符还具有提供比工具提示更多的详细信息的属性。 通过右键单击特定运算符，可以从上下文菜单中选择“属性”选项以查看完整的属性列表。 此选项将在 SQL Server Management Studio 中打开单独的“属性”窗格，默认情况下位于右侧。 打开“属性”窗格后，选择任何运算符将填充 “属性” 列表，其中包含该运算符的详细信息。 或者，可以通过选择主 SQL Server Management Studio 菜单中的 “视图 ”并选择 “属性”来打开“属性”窗格。

运算符的属性的屏幕截图。

“属性”窗格包含其他信息并显示输出列表，详细说明要传递给下一个运算符的列。 这些列可能表示在使用聚集索引扫描进行分析时需要非聚集索引来提高查询性能。 由于聚集索引扫描作读取整个表，因此在此方案中，每个表中 StockItemID 列的非聚集索引可能更高效。

轻型查询分析

生成实际执行计划（无论是使用 SSMS 还是扩展事件监视基础结构）时，可能会带来巨大的开销。 因此，此过程通常保留为实时站点故障排除工作。 众所周知，观察程序开销就是监视正在运行的应用程序的成本。 在某些情况下，此成本可能只是 CPU 利用率的几个百分点，但在其他情况下（例如捕获实际执行计划）可能会显著降低单个查询性能。 SQL Server 引擎中的旧式分析最多可以产生 75 个% 开销来捕获查询信息，而轻型分析的最大开销约为 2%。

在第一个版本的轻型分析中，它收集行计数和 I/O 利用率信息（数据库引擎为满足给定查询而执行的逻辑和物理读取以及写入操作数）。 此外，引入了名为 query_thread_profile 的新扩展事件，以允许检查查询计划中每个运算符的数据。 在轻型分析的初始版本中，使用该功能要求全局启用跟踪标志 7412。

如果未全局启用轻型分析，则可以使用 USE HINT 查询提示 QUERY_PLAN_PROFILE 在查询级别启用轻型分析。 当具有此提示的查询完成执行时，将生成 query_plan_profile 扩展事件，从而提供实际的执行计划。 下面是具有以下提示的查询示例：

SELECT [stockItemName]
 ,[UnitPrice] * [QuantityPerOuter] AS CostPerOuterBox
 ,[ QuantityonHand]
FROM [Warehouse].[StockItems] s
    JOIN [Warehouse].[StockItems] sh ON s.StockItemID = sh.StockItemID
ORDER BY CostPerOuterBox 
OPTION(USE HINT ('QUERY_PLAN_PROFILE'));

上一个查询计划统计信息

默认情况下，在 SQL Server 2019 和 Azure SQL 数据库和托管实例中启用轻型分析。 轻型分析还可用作数据库范围内的配置选项（名为 LIGHTWEIGHT_QUERY_PROFILING）。 利用“数据库范围内”选项，你可以为任何相互独立的用户数据库禁用该功能。

此外，还有一个调用 sys.dm_exec_query_plan_stats的动态管理功能，它可显示给定计划句柄的最后一个已知实际查询执行计划。 若要通过该函数查看上一个已知实际查询计划，可以在服务器范围内启用跟踪标志 2451。 或者，你可以使用名为 LAST_QUERY_PLAN_STATS 的数据库范围内的配置选项来启用此功能。

可以将此函数与其他对象合并，以获取所有缓存查询的最后一个执行计划：

SELECT *
FROM sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
    CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle) AS qps; 
GO

此功能通过最小的开销，让你能够快速识别系统中任何查询的运行时统计信息。 下图显示了如何检索计划。 如果选择执行计划 XML（这将是结果的第一列），则会显示下图中所示的执行计划。

显示检索查询的实际执行计划的屏幕截图。

从下图中 列存储索引扫描 的属性中可以看到，从缓存中检索的计划在查询中检索到的实际行数。

检索到的执行计划的屏幕截图，其中显示缓存具有在查询中检索到的实际行数。