SQL Server 执行计划(9) - 保存和比较执行计划

最新推荐文章于 2023-04-16 10:53:46 发布

albatross76

最新推荐文章于 2023-04-16 10:53:46 发布

阅读量613

点赞数

分类专栏：数据库文章标签： sqlserver 数据库

原文链接：https://www.sqlshack.com/saving-your-sql-execution-plan/

版权

数据库专栏收录该内容

14 篇文章 14 订阅

订阅专栏

在本系列的前几篇文章中（见底部索引），我们从多个方面讨论了 SQL 执行计划的特点，包括 SQL Server 查询优化器内部生成 SQL 执行计划的方式，如何识别和分析执行计划的不同组件和运算符，如何使用不同的工具处理计划，最后，使用执行计划调整简单和复杂 T-SQL 查询的性能。在本系列的最后一篇文章中，我们将讨论执行计划的存储位置以及如何保存以备将来使用。

执行计划缓存

就资源和时间消耗而言，生成 SQL 执行计划的过程不是一个简单或廉价的操作。

对于每个提交的 T-SQL 查询，SQL Server 引擎将生成的计划存储在内存的计划缓存区中，以便在再次执行相同查询时使用它，从而消除了执行同样查询时创建新 SQL 执行计划的开销。

用户提交 T-SQL 查询时，SQL Server 引擎将在计划缓存中为查找是否存在与该查询的估算计划相匹配的实际执行计划。如果找到匹配的计划，SQL Server 引擎将使用它来执行查询，否则，将生成一个新的查询计划，同时将新计划存储到计划缓存中。如果SQL Server 查询优化器发现为该查询使用并行计划比使用缓存的串行计划更快的话，优化器将会为查询生成一个新的并行计划，而不是直接使用缓存的串行计划进行查询。新生成的并行计划也会保存到计划缓存中。

SQL 执行计划对各种类型的更改很敏感，以下这些操作都会导致执行计划的缓存失效。表架构更改、索引更改、统计更新、表中的大量数据修改或插入，使用查询提示或调用 sp_recompile 函数显式重新编译查询。

如果您的大部分查询都是执行一次且不再执行的Ad Hoc 查询，则最好不要保留此类查询的执行计划并浪费计划缓存内存资源。为了提高计划缓存的使用效率，降低此类工作负载类型的内存压力，您可以启用Optimize for Ad hoc Workloads选项。开启该选项后，同一查询在被第二次执行后，关联的SQL执行计划才会被保存到计划缓存中。

SQL Server 为我们提供了许多动态管理视图，允许我们检查计划缓存的内容，例如sys.dm_exec_query_plan，它以 XML 格式返回计划句柄指定的批处理的执行计划，以及sys .dm_exec_cached_plans，它保存了SQL Server执行计划缓存的句柄信息。假设您需要检查需要很长时间才能完成的查询的计划。从计划缓存中检查该查询的计划将允许您分析和调整该查询的计划，而无需运行该查询。例如，以下查询可用于列出存储在计划缓存中的所有计划，以及该计划的使用次数：

SELECT EX_SQLTXT.dbid,
EX_SQLTXT.objectid,
EX_SQLTXT.text, 
EX_QP.query_plan,
EX_CP.usecounts
FROM sys.dm_exec_cached_plans EX_CP
  CROSS APPLY sys.dm_exec_query_plan(EX_CP.plan_handle) EX_QP
  CROSS APPLY sys.dm_exec_sql_text(EX_CP.plan_handle) EX_SQLTXT

本文的案例，执行结果将如下所示：
计划缓存查询

SQL 执行计划不会永远保存在计划缓存中，如果系统需要更多内存或计划缓存过期，SQL Server 引擎将从计划缓存中删除该计划，清除时机取决于计划的成本和计划的被调用次数达到零值。负责清理这些陈旧计划的系统进程称为Lazy Writer进程。

出于测试目的，您可以使用DBCC FREEPROCCACHE T-SQL 命令显式清除计划缓存。但是不建议在生产环境中执行该 DBCC 命令，因为这将导致额外的开销，所有的查询都需要重新生成执行计划。您可以从下面的快照中看到，DBCC FREEPROCCACHE 命令执行后，所有计划缓存都被清除了：

保存查询执行计划

SQL Server 为我们提供了将生成的 SQL 执行计划导出为sqlplan文件的能力，可以将其作为证据发送给相关的开发团队或在将来用于性能比较目的。

要从 SQL Server Management Studio 保存为您的查询生成的 SQL 执行计划，请右键单击图形执行计划上的空白区域，然后选择将**Save Execution Plan As…**选项，如下所示：
保存执行计划

在显示的另存为窗口中，指定保存 sqlplan文件的路径和名称，如下所示：
在这里插入图片描述

图形执行计划也可以使用 SQL Server Management Studio 保存，方法是从File菜单中选择**Save ExecutionPlan1.sqlplan As…**选项，如下图所示：
在这里插入图片描述

您还可以将生成的 SQL 执行计划保存为 XML 格式。首先，您应该以 XML 格式显示执行计划，右键单击图形计划的空白区域并选择Show Execution Plan XML….选项，这将为 XML 格式的 SQL 执行计划打开一个新窗口。显示 XML 计划后，从 File 菜单中选择**Save ExecutionPlan1.xml As…**选项，导出 XML 计划，如下所示：
在这里插入图片描述

现在，您可以将生产环境中导出的 sqlplan 或 XML 文件发送给开发团队中的同事，以便于他调查并调整查询性能。收到计划文件后，开发人员可以使用“File”主菜单中的“Open”->“File”选项打开该计划。并从显示的打开文件窗口中，找到执行计划文件，点击打开按钮。打开后的执行计划如下：
在这里插入图片描述

或者简单地从 Windows 资源管理器中双击导出的执行计划文件，该计划将在 SQL Server Management Studio 中打开。

比较执行计划

保存执行计划的另一个好处是通过比较改善前后的执行计划来检查查询性能的改善情况。例如，假设您需要检查通过在您的表上创建索引将获得的性能改善效果。为此，应在创建索引之前执行查询，获取实际执行计划，然后你可以将 sqlplan 文件保存在本地机器中已便进行比较。操作如下：
在这里插入图片描述

之后，使用下面的 CREATE INDEX T-SQL 语句创建索引：

CREATE NONCLUSTERED INDEX IX_EMP_Salaries_EMP_ID ON EMP_Salaries (EMP_ID) INCLUDE (EMP_HireDate,EMP_Salary )

成功创建索引后，再次执行相同的 SELECT 查询。在新生成的 SQL 执行计划上，右键单击该计划上的空白区域并选择Compare Showplan选项，如下所示：
在这里插入图片描述

在打开的窗口中，指定之前保存的计划的位置，然后会显示一个Showplan比较窗口，比较窗口中将会显示图形执行流程和执行统计数据两个方面的详细比较结果。详细如下图所示：
在这里插入图片描述

系列目录

albatross76

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
SQL Server 执行计划(9) - 保存和比较执行计划

在本系列的前几篇文章中（见底部索引），我们从多个方面讨论了 SQL 执行计划的特点，包括 SQL Server 查询优化器内部生成 SQL 执行计划的方式，如何识别和分析执行计划的不同组件和运算符，如何使用不同的工具处理计划，最后，使用执行计划调整简单和复杂 T-SQL 查询的性能。在本系列的最后一篇文章中，我们将讨论执行计划的存储位置以及如何保存以备将来使用。
复制链接

扫一扫