【SQLServer】保存执行计划
2022-10-08 16:15 abce 阅读(327) 评论(0) 编辑 收藏 举报4.【SQLServer】SQLServer执行计划运算符-第1部分
5.【SQLServer】SQLServer执行计划运算符-第2部分
6.【SQLServer】SQLServer执行计划运算符-第3部分
7.【SQLServer】SQLServer执行计划运算符-第4部分
8.【SQLServer】SSMS 18.0中执行计划的加强
缓存的执行计划
生成执行计划的过程在资源和时间消耗方面都不是一件容易或便宜的操作。
对于每个提交的查询，SQL Server引擎将生成的计划存储在称为计划缓存(Plan cach)的内存中，以便在再次执行相同的查询时使用它,排除了为相同查询创建执行计划的开销。
从提交查询开始，SQL Server引擎将在计划缓存内存中搜索与查询的估计计划匹配的任何实际执行计划。如果找到匹配的计划，SQL Server引擎将使用它来执行查询，否则，将生成一个新计划，存储在计划缓存中并用于执行查询。在某些情况下，SQL Server查询优化器发现对该查询使用并行计划比使用缓存串行计划更快。在这种情况下，优化器将生成一个新的并行计划，并将生成并存储在同一查询的计划缓存中。
SQL执行计划对不同类型的更改也很敏感，这会导致再次重新编译查询。这些更改包括表schema更改、索引更改、统计信息更新、表中的大量数据修改或插入，或使用查询hint或调用sp_recompile函数显式重新编译查询。
如果你的大部分系统工作负载包含执行一次且不再执行的查询(也称为 Ad Hoc 查询)，则最好不要保留此类查询的计划并浪费Plan Cache内存资源。为了提高Plan Cache的使用效率并减少此类工作负载类型的内存压力，可以启用Optimize for Ad hoc Workloads选项，以便在第二次执行查询时将查询的SQL执行计划存储在计划缓存中。
SQL Server为我们提供了许多动态管理视图，允许我们检查计划缓存内存的内容，例如sys.dm_exec_query_plan，它以XML格式返回计划句柄指定的批处理的Showplan;以及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命令显式清除计划缓存。不建议在生产环境中执行该DBCC命令，因为这会导致额外的开销，从头开始为任何提交的查询生成所有计划。从下面的快照中可以看到，DBCC FREEPROCCACHE命令将清除计划缓存内存：
保存执行计划
SQL Server为我们提供了将生成的执行计划导出为sqlplan文件的能力，将其作为相关SQL开发团队的证据发送给我们，或者在将来用于性能比较的目的。
要从SQL Server Management Studio为你的查询保存生成的SQL执行计划，请右键单击图形执行计划上的空白处，然后选择将执行计划另存为...选项，如下所示：
图形执行计划也可以使用SQL Server Management Studio保存，方法是从"File"菜单中选择"Save ExecutionPlan1.sqlplan As... "选项，如下所示：
还可以将生成的SQL执行计划保存为XML格式。
首先，应该以XML格式显示执行计划，方法是右键单击图形计划的空白区域并选择Show Execution Plan XML... 选项，这将为XML格式的执行计划打开一个新窗口。
显示XML计划后，从File菜单中选择Save ExecutionPlan1.xml As...选项，以导出XML计划，如下所示：
保存后，可以将生产环境中导出的sqlplan或XML文件附加给SQL开发团队中没有生产服务器生成执行计划权限的同事，以查看它并调整查询性能。
收到后，开发人员可以使用"文件"主菜单中的"打开"->"文件"选项打开该计划。在显示的打开文件窗口中，找到执行计划文件并点击打开按钮，如下图所示：
或者简单从Windows资源管理器中双击导出的执行计划文件，该计划将在SQL Server Management Studio中打开。
比较执行计划
保存执行计划的另一个好处是通过在执行更改之前将新的执行计划与以前保存的计划进行比较来检查查询性能的增强。例如，假设需要检查通过在表上创建索引将获得的收益。为此，应在创建索引之前执行查询，包括实际执行计划，然后可以将sqlplan文件保存以进行比较，如下所示：
然后创建索引：
CREATE NONCLUSTERED INDEX IX_EMP_Salaries_EMP_ID ON EMP_Salaries (EMP_ID) INCLUDE (EMP_HireDate,EMP_Salary )
成功创建索引后，再次执行相同的SELECT查询。在新生成的SQL Execution Plan上，右键单击该计划的空白处，然后选择Compare Showplan选项，如下所示：
在打开的窗口中，指定先前保存的计划的位置，然后将显示一个Showplan Comparison窗口，从图形和执行统计方面对保存的计划和新计划进行全面比较，如下图所示：
