In the previous articles of this series (see the index at bottom), we discussed the characteristics of the SQL Execution Plan from multiple aspects, that include the way the SQL Execution Plan is generated by the SQL Server Query Optimizer internally, what are the different types of plans, how to identify and analyze the different components and operators of the Execution Plans, how to work with the plans using different tools and finally, tuning the performance of simple and complex T-SQL queries using the Execution Plans. In this, the last article of this series, but not the least, we will discuss where the Execution plan is stored and how to save it for future use.
在本系列的前几篇文章中（请参阅底部的索引），我们从多个方面讨论了SQL执行计划的特征，包括SQL Server Query Optimizer内部生成SQL执行计划的方式，有什么不同？计划的类型，如何识别和分析执行计划的不同组件和运算符，如何使用不同的工具使用计划以及最终如何使用执行计划来调整简单和复杂的T-SQL查询的性能。 在本系列的最后一篇文章中，但并非最不重要的一点，我们将讨论执行计划的存储位置以及如何保存它以备将来使用。
计划缓存概述 (Plan Cache Overview)
The process of generating the SQL Execution Plan is not an easy or cheap operation, in terms of resources and time consumption.
就资源和时间消耗而言，生成SQL执行计划的过程并非易事或便宜。
For each submitted T-SQL query, the SQL Server Engine stores the generated plans in a special section of the memory called the Plan cache, to use it whenever the same query is executed again, eliminating the overhead of creating a new SQL Execution Plan for the same query.
对于每个提交的T-SQL查询，SQL Server引擎都会将生成的计划存储在内存的一个特殊部分中，称为“ 计划缓存” ，以便在再次执行同一查询时使用它，从而消除了为该数据库创建新SQL执行计划的开销。相同的查询。
The story starts when a T-SQL query is submitted, where the SQL Server Engine will search for any Actual Execution Plan, for this query, that matches the estimated plan for the query, in the Plan cache memory. If a matching plan is found, the SQL Server Engine will use it to execute the query, otherwise, a new plan will be generated, stored in the Plan cache and used to execute the query. In some cases, the SQL Server Query Optimizer finds that using a parallel plan for that query is faster than using the cached serial plan. In this case, the optimizer will generate a new parallel plan will be generated and stored in the Plan cache for the same query.
故事从提交T-SQL查询时开始，在该查询中，SQL Server引擎将在“计划”缓存中为该查询搜索与该查询的估计计划匹配的任何实际执行计划。 如果找到匹配的计划，SQL Server引擎将使用它来执行查询，否则，将生成一个新计划，将其存储在计划缓存中并用于执行查询。 在某些情况下，SQL Server查询优化器发现，对该查询使用并行计划比使用缓存的串行计划更快。 在这种情况下，优化器将生成一个新的并行计划，该计划将针对相同的查询生成并存储在计划缓存中。
The SQL Execution Plan is also sensitive to different type of changes, that leads the query to be recompiled again. These changes include table schema changes, index changes, statistics update, large number of data modification or insertion in the table, or explicitly recompile the query using query hints or calling the sp_recompile function.
SQL执行计划对不同类型的更改也很敏感，这会导致再次重新编译查询。 这些更改包括表架构更改，索引更改，统计信息更新，大量数据修改或在表中的插入，或者使用查询提示或调用sp_recompile函数显式重新编译查询。
If most of your system workload consists of queries that are executed once and never executed again, also known as Ad Hoc queries, it is better not to keep the plans for such queries and waste the Plan Cache memory resources. To improve the efficiency of the Plan Cache usage and reduce the memory pressure in such workload type, you can enable the Optimize for Ad hoc Workloads option, to store the SQL Execution Plan of the query in the Plan Cache at the second execution of the query.
如果您的大部分系统工作负载都包含一次执行且永不执行的查询（也称为临时查询），则最好不要保留此类查询的计划并浪费计划缓存的内存资源。 为了提高计划缓存的使用效率并减轻此类工作负载类型的内存压力，您可以启用“ 优化临时工作负载”选项，以在第二次执行查询时将查询SQL执行计划存储在计划缓存中。
- Saving the Plan Cache storage using the Optimize for Ad hoc Workloads option.使用“针对临时工作负载进行优化”选项保存计划缓存存储 。
SQL Server provides us with number of dynamic management views, that allows us to check the content of the Plan cache memory, such as the sys.dm_exec_query_plan, that returns the Showplan in XML format for the batch specified by the plan handle, and the sys.dm_exec_cached_plans, that returns a row for each query plan that is cached by SQL Server for faster query execution. Assume that you need to check the plan for a query that is taking a long time to complete. Checking the plan for that query from the Plan Cache will allow you analyzing and tuning the plan for that query without the need to run the query. For example, the below query can be used to list all plans stored in the Plan cache, with the number of times this plan is used:
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
And the result in our case will be like below:
在我们的案例中，结果将如下所示：
The SQL Execution Plan will not be kept in the Plan cache forever, where the SQL Server Engine will remove the plan from the Plan Cache if the system requires more memory or the age of the plan, that depends on the cost of the plan and the number of times this plan is called, reached the zero value. The system process that is responsible for cleaning these aged plans is called the Lazy Writer process.
SQL执行计划不会永远保存在计划缓存中，如果系统需要更多内存或计划的期限，则SQL Server引擎将从计划缓存中删除该计划，具体取决于计划的成本和此计划被调用的次数，达到零值。 负责清理这些老化计划的系统过程称为“ 懒惰写入器”过程。
For testing purposes, you can explicitly clear the Plan cache using the DBCC FREEPROCCACHE T-SQL command. Take into consideration that it is not recommended to perform that DBCC command on the production environment, as this will cause an extra overhead generating all the plans for any submitted query from scratch. You can see from the snapshot below that, the DBCC FREEPROCCACHE command will clear the Plan Cache memory, similar to the result of restarting the SQL Server Service on that cache:
为了进行测试，您可以使用DBCC FREEPROCCACHE T-SQL命令明确清除计划缓存。 考虑到不建议在生产环境上执行该DBCC命令，因为这将导致额外的开销，从头开始为任何提交的查询生成所有计划。 您可以从下面的快照中看到，DBCC FREEPROCCACHE命令将清除计划缓存内存，类似于在该缓存上重新启动SQL Server服务的结果：
保存查询执行计划 (Saving Query Execution Plan)
SQL Server provides us with the ability to export the generated SQL Execution Plan as a sqlplan file, to send it as an evidence for the related SQL development team or use it in the future for performance comparison purposes.
SQL Server为我们提供了将生成SQL执行计划导出为sqlplan文件，将其作为相关SQL开发团队的证据发送或将来用于性能比较目的的能力。
To save the generated SQL Execution Plan for your query from the SQL Server Management Studio, right-click on the white space on the graphical Execution Plan and choose the Save Execution Plan As… option, as shown below:
要从SQL Server Management Studio保存为查询生成SQL执行计划，请右键单击图形执行计划上的空白，然后选择“ 将执行计划另存为...”选项，如下所示：
In the displayed Save As window, specify the path where you will save the sqlplan file and a meaningful name for that file, as below:
在显示的“ 另存为”窗口中，指定保存sqlplan文件的路径以及该文件的有意义的名称，如下所示：
The graphical Execution Plan can be also saved using the SQL Server Management Studio, by choosing the Save ExecutionPlan1.sqlplan As… option from the File menu, as below:
也可以使用SQL Server Management Studio，通过从“ 文件”菜单中选择“ 将ExecutionPlan1.sqlplan另存为”选项来保存图形执行计划，如下所示：
You can also save the generated SQL Execution Plan in XML format. First, you should show the Execution Plan in XML format, by right-clicking in the white space of the graphical plan and choose the Show Execution Plan XML… Option, that open a new window for the SQL Execution Plan in XML format. Once the XML plan is displayed, choose the Save ExecutionPlan1.xml As… option from the File menu, to export the XML plan, as shown below:
您还可以将生成SQL执行计划保存为XML格式。 首先，您应该以XML格式显示执行计划，方法是在图形计划的空白处单击鼠标右键，然后选择“ Show Execution Plan XML ...”选项，这将为XML格式SQL Execution Plan打开一个新窗口。 显示XML计划后，从File菜单中选择Save ExecutionPlan1.xml As…选项，以导出XML计划，如下所示：
In the displayed Save As window, specify the path where you will save the XML file and a meaningful name for that file, as below:
在显示的“ 另存为”窗口中，指定保存XML文件的路径以及该文件的有意义的名称，如下所示：
Now, you can attach the exported sqlplan or XML file from the production environment to your colleague in the SQL development team, who does not have permission on the production server to generate the Execution Plan, to review it and tune the query performance. Once received, the developer can open that plan using the Open -> File option from the File main menu. And from the displayed Open File window, locate the execution plan file and click on the Open button, as shown below:
现在，您可以将生产环境中导出的sqlplan或XML文件附加到SQL开发团队中的同事，该同事没有生产服务器上的权限来生成执行计划，以对其进行检查并调整查询性能。 收到后，开发人员可以使用“ 文件”主菜单中的“ 打开”->“文件”选项来打开该计划。 在显示的“ 打开文件”窗口中，找到执行计划文件，然后单击“ 打开”按钮，如下所示：
Or simply, double-click on the exported Execution Plan file from the Windows Explorer, and the plan will be opened in SQL Server Management Studio.
或者简单地，双击Windows资源管理器中导出的执行计划文件，该计划将在SQL Server Management Studio中打开。
比较执行计划 (Compare Execution Plans)
Another benefit that can be gained from saving the Execution Plans is checking the enhancement in the queries performance, by comparing the new execution plan with a previously saved plan before performing the change. For example, assume that you need to check the enhancements that will be gained from creating an index on your table. To do that, the query should be executed before creating the index, including the Actual Execution Plan, then you can save the sqlplan file in your machine for comparing purposes, as below:
保存执行计划可以获得的另一个好处是，通过在执行更改之前将新的执行计划与先前保存的计划进行比较，来检查查询性能的增强。 例如，假设您需要检查通过在表上创建索引将获得的增强。 为此，应在创建索引（包括实际执行计划）之前执行查询，然后可以将sqlplan文件保存在计算机中以进行比较，如下所示：
After that, create the index using the CREATE INDEX T-SQL statement below:
之后，使用下面的CREATE INDEX T-SQL语句创建索引：
CREATE NONCLUSTERED INDEX IX_EMP_Salaries_EMP_ID ON EMP_Salaries (EMP_ID) INCLUDE (EMP_HireDate,EMP_Salary )
Once the index is created successfully, execute the same SELECT query again, including the Actual Execution Plan. On the newly generated SQL Execution Plan, right-click on the white space on that plan and choose the Compare Showplan option, as shown below:
成功创建索引后，请再次执行相同的SELECT查询，包括“实际执行计划”。 在新生成SQL执行计划上，右键单击该计划上的空白，然后选择“ 比较显示计划”选项，如下所示：
From the opened window, specify the location of the previously saved plan, then a Showplan Comparison windows will be displayed, with a full comparison between the saved and new plans, from graphical and execution statistics aspects, as shown clearly below:
在打开的窗口中，指定先前保存的计划的位置，然后将显示“显示计划比较”窗口，从图形和执行统计方面对保存的计划和新计划进行全面比较，如下所示：
At this point, we have reached the end of the SQL Execution Plan articles series. I hope the way these articles were presented made this important and complex subject simpler for you. Feel free to share any feedback or questions in the comments below.
至此，我们已经到达了SQL执行计划文章系列的结尾。 我希望这些文章的呈现方式可以使您轻松了解这一重要而复杂的主题。 随时在下面的评论中分享任何反馈或问题。
目录 (Table of contents)
翻译自: https://www.sqlshack.com/saving-your-sql-execution-plan/
5513
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
