SQL Server查询执行计划–查看计划

In the SQL Server query execution plans – Basics, we described the query execution plans in SQL Server and why they are important for performance analysis. In this article, we will focus on the methods for opening the plans, both actual and estimated ones

在“ SQL Server查询执行计划-基础知识”中 ，我们描述了SQL Server中的查询执行计划以及为什么它们对性能分析很重要。 在本文中，我们将重点介绍开放计划的方法，包括实际的和估计的方法。

If you look at the Query item in the SQL Server Management Studio menu, you’ll see two options related to query plans – Display Estimated Execution plan and Include Actual Execution plan

如果您查看SQL Server Management Studio菜单中的“ 查询”项，您将看到与查询计划相关的两个选项-“ 显示估计的执行计划”和“ 包括实际的执行计划”

An estimated execution plan is a SQL Server query plan that is generated without actually running the query (or stored procedure) the plan is created for. It’s based on estimation of expected behavior. It’s useful for analyzing how a query would behave, without actually running it. This is very useful for testing purposes in environments where performance should not be affected by running actual code (e.g. running a SELECT statement with complex joins against huge tables), or when running code is not possible due to data changes it makes (e.g. executing an UPDATE). Its downside is that it might be inaccurate in some scenarios

估计执行计划是一种SQL Server查询计划，该计划是在不实际运行为其创建查询的查询（或存储过程）的情况下生成的。 它基于对预期行为的估计。 这对于分析查询的行为很有用，而无需实际运行它。 这对于在以下环境中进行测试的目的非常有用：在这些环境中，运行实际代码不会影响性能（例如，对大型表运行带有复杂联接的SELECT语句），或者由于数据更改而无法运行代码（例如，执行更新）。 它的缺点是在某些情况下可能不准确

An actual execution plan is the SQL Server query plan that is generated after a query was executed. It’s more reliable, as it’s based on the actual execution, not estimates. It also provides more information and statistics, therefore it’s much useful in troubleshooting

实际的执行计划是在执行查询后生成SQL Server查询计划。 它更可靠，因为它基于实际执行，而不是估计。 它还提供更多的信息和统计信息，因此在故障排除中非常有用

There are several methods available for viewing a query plan in SQL Server

有几种方法可用于查看SQL Server中的查询计划

使用ApexSQL计划查看实际执行计划 (View Actual execution plan using ApexSQL Plan)

The Actual execution plan option is available in the in the Home tab of ApexSQL Plan

ApexSQL Plan的“主页”选项卡中的“ 实际执行计划”选项可用

1. Query in ApexSQL Plan查询
2. Actual execution plan option实际执行计划选项

SELECT *
  FROM person.PersonPhone
  WHERE PhoneNumber LIKE '%697%'

If multiple SQL queries are executed, their plans will be listed in the same tab, one below another separated as statements.

如果执行了多个SQL查询，则它们的计划将列在同一选项卡中，一个在另一个选项中以语句分开。

Each item in the query plan shows a tooltip with additional information

查询计划中的每个项目都会显示一个工具提示，其中包含其他信息

SQL Server execution plans can be saved as SQL or sqlplan files for later analysis.

可以将SQL Server执行计划另存为SQL或sqlplan文件，以供以后分析。

The steps are similar for using the Estimated execution Plan option, except the query doesn’t have to be executed.

步骤与使用“ 估计执行计划”选项相似，不同之处在于不必执行查询。

SQL Server query plans can also be shown in Query Editor using some of the following options:

还可以使用以下某些选项在查询编辑器中显示SQL Server查询计划：

使用ApexSQL计划查看估计的执行计划 (View Estimated execution plan using ApexSQL Plan)

The Estimated execution plan option is also available in the in the Home ribbon bar of ApexSQL Plan

ApexSQL Plan的主页功能区栏中的“ 估计执行计划”选项也可用

1. Query in ApexSQL Plan查询
2. Estimated execution plan option估计执行计划”选项

SHOWPLAN_XML (SHOWPLAN_XML)

The SHOWPLAN option in SQL Server Management Studio has to be set using T-SQL and it shows the estimated execution plan. This is the same plan as shown when the Estimated Execution Plan option is selected, when the query is not actually executed

必须使用T-SQL设置SQL Server Management Studio中的SHOWPLAN选项，它显示估计的执行计划。 与实际上未执行查询时选择“ 估计执行计划”选项时显示的计划相同。

1. Execute

   执行

   SET SHOWPLAN_XML ON            
   

   Note that this is the only statement in the batch that can be executed

   请注意，这是批处理中唯一可以执行的语句

2. Execute a query. The Results tab will show a link to the query plan. Note that the query results are not shown, as the query is not really executed

   执行查询。 结果选项卡将显示查询计划的链接。 请注意，由于未真正执行查询，因此未显示查询结果

   

3. Click the link is the grid

   单击链接是网格

   A new query tab will be opened showing the query plan

   将打开一个新的查询选项卡，显示查询计划

   

4. To stop the query plan from showing, run

   要停止显示查询计划，请运行

   SET SHOWPLAN_XML OFF
   

使用查询缓存 (Use the query cache)

As mentioned in the SQL Server query execution plans – Basics article, query plans in SQL Server are saved in the query plan cache, so they can be reused later in order to execute queries faster. One of the options to see query plans is to query the content of the plan cache using Dynamic Management Views (DMVs)

如“ SQL Server查询执行计划-基础”一文中提到的，SQL Server中的查询计划保存在查询计划缓存中，因此以后可以重用它们以更快地执行查询。 查看查询计划的选项之一是使用动态管理视图（DMV）查询计划缓存的内容

The sys.dm_exec_cached_plans view shows one row for every query plan stored in the plan cache. The view shows query text, memory used, and how many times the plan was reused

sys.dm_exec_cached_plans视图为计划缓存中存储的每个查询计划显示一行。 该视图显示查询文本，已使用的内存以及计划被重用了多少次

The sys.dm_exec_sql_text view shows the SQL batch text, identified by sql_handle

sys.dm_exec_sql_tex t视图显示SQL批处理文本，由sql_handle标识

To see the plans for ad hoc queries cached in the plan cache:

要查看计划缓存中缓存的临时查询的计划，请执行以下操作：

SELECT qp.query_plan, 
       CP.usecounts, 
       cp.cacheobjtype, 
       cp.size_in_bytes, 
       cp.usecounts, 
       SQLText.text
  FROM sys.dm_exec_cached_plans AS CP
  CROSS APPLY sys.dm_exec_sql_text( plan_handle)AS SQLText
  CROSS APPLY sys.dm_exec_query_plan( plan_handle)AS QP
  WHERE objtype = 'Adhoc' and cp.cacheobjtype = 'Compiled Plan'

To open a plan, click the link in the query_plan results column and the plan will be shown in the new Query window

要打开计划，请单击query_plan结果列中的链接，该计划将显示在新的“查询”窗口中

使用STATISTICS和SHOWPLAN选项 (Use the STATISTICS and SHOWPLAN options)

The STATISTICS XML option shows the same query plan as shown when the Include Actual Execution Plan option is selected. Unlike with the SHOWPLAN options that don’t actually execute the queries, the STATISTICS options execute it and show the results

STATISTICS XML选项显示的查询计划与选择“ 包括实际执行计划”选项时显示的查询计划相同。 与不实际执行查询的SHOWPLAN选项不同，STATISTICS选项执行它并显示结果

SET STATISTICS XML ON

Note that besides the link to the query plan, the query results are also shown

请注意，除了链接到查询计划之外，还显示了查询结果

To turn the option off, execute:

要关闭该选项，请执行：

SET STATISTICS XML OFF

Other useful options are:

其他有用的选项是：

SHOWPLAN_XML – doesn’t execute the query, so no results are shown. Shows the link the same as the STATISTICS XML option

SHOWPLAN_XML –不执行查询，因此不显示结果。 显示链接与STATISTICS XML选项相同

SHOWPLAN_TEXT – doesn’t execute the query, shows the text of the estimated query plan

SHOWPLAN_TEXT –不执行查询，显示估计的查询计划的文本

SHOWPLAN_ALL – doesn’t execute the query, shows the text of the estimated query plan along with the cost estimation

SHOWPLAN_ALL –不执行查询，显示估算查询计划的文本以及成本估算

STATISTICS PROFILE – executes the query, shows the results and text of the actual query plan

统计资料-执行查询，显示结果和实际查询计划的文本

使用SQL Server Profiler (Use SQL Server Profiler)

A query execution plan can also be captured in a SQL Server trace and opened in SQL Server Profiler

查询执行计划也可以在SQL Server跟踪中捕获并在SQL Server Profiler中打开

1. Start SQL Server Profiler

   启动SQL Server Profiler

2. File menu, select 文件”菜单中，选择“ New Trace新建跟踪”
3. Events Section tab, check 事件部分”选项卡中，选中“ Show all events显示所有事件”
4. Performance node性能节点

5. Select Showplan XML

   选择Showplan XML

   

6. Execute the query you want to see the query plan for

   执行要查看其查询计划的查询

7. Stop the trace. This is recommended due to practical reasons – in busy databases, it’s difficult to filter by the event you want to trace

   停止跟踪。 由于实际原因，建议使用此方法–在繁忙的数据库中，很难根据要跟踪的事件进行过滤

8. Select the query plan in the grid

   在网格中选择查询计划

   The SQL Server query plan is shown in the lower pane. It’s the same plan as shown when the Include Actual Execution Plan option is selected. You can see its details in the tooltip that appears on mouse over or save the whole trace as an XML file for later analysis

   SQL Server查询计划显示在下部窗格中。 与选择“ 包括实际执行计划”选项时显示的计划相同。 您可以在鼠标悬停在其上的工具提示中查看其详细信息，或将整个跟踪保存为XML文件以供以后分析

   

This method is not recommended due to several downsides. SQL Server Profiler adds some overhead that affects query performance. Another reason is that filtering the events and finding the specific one among thousands of records in not easy in SQL Server Profiler

由于存在多个缺点，因此不建议使用此方法。 SQL Server Profiler增加了一些影响查询性能的开销。 另一个原因是在SQL Server Profiler中过滤事件并在数千条记录中查找特定事件并不容易

In this article, we showed how to open a SQL Server query execution plan using various methods. In the next article, we will show how to read the plans, what the objects presented with icons represent, and how to use these plans in performance analysis and troubleshooting

在本文中，我们展示了如何使用各种方法打开SQL Server查询执行计划。 在下一篇文章中，我们将展示如何阅读计划，带有图标的对象代表什么以及如何在性能分析和故障排除中使用这些计划。

翻译自: https://www.sqlshack.com/sql-server-query-execution-plans-viewing-plans/