如何在 SQL Server 中获取查询执行计划？

How do I obtain a Query Execution Plan in SQL Server?

提问by Justin

In Microsoft SQL Server how can I get a query execution plan for a query / stored procedure?

在 Microsoft SQL Server 中，如何获取查询/存储过程的查询执行计划？

回答by Justin

There are a number of methods of obtaining an execution plan, which one to use will depend on your circumstances. Usually you can use SQL Server Management Studio to get a plan, however if for some reason you can't run your query in SQL Server Management Studio then you might find it helpful to be able to obtain a plan via SQL Server Profiler or by inspecting the plan cache.

有多种获取执行计划的方法，使用哪种方法取决于您的情况。通常您可以使用 SQL Server Management Studio 来获取计划，但是如果由于某种原因您无法在 SQL Server Management Studio 中运行您的查询，那么您可能会发现能够通过 SQL Server Profiler 或通过检查来获取计划会很有帮助计划缓存。

Method 1 - Using SQL Server Management Studio

方法 1 - 使用 SQL Server Management Studio

SQL Server comes with a couple of neat features that make it very easy to capture an execution plan, simply make sure that the "Include Actual Execution Plan" menu item (found under the "Query" menu) is ticked and run your query as normal.

SQL Server 带有一些简洁的功能，可以很容易地捕获执行计划，只需确保勾选“包括实际执行计划”菜单项（位于“查询”菜单下）并正常运行查询.

If you are trying to obtain the execution plan for statements in a stored procedure then you should execute the stored procedure, like so:

如果您试图获取存储过程中语句的执行计划，那么您应该执行存储过程，如下所示：

exec p_Example 42

When your query completes you should see an extra tab entitled "Execution plan" appear in the results pane. If you ran many statements then you may see many plans displayed in this tab.

查询完成后，您应该会在结果窗格中看到一个名为“执行计划”的额外选项卡。如果您运行了许多语句，那么您可能会看到此选项卡中显示了许多计划。

From here you can inspect the execution plan in SQL Server Management Studio, or right click on the plan and select "Save Execution Plan As ..." to save the plan to a file in XML format.

从这里您可以在 SQL Server Management Studio 中检查执行计划，或右键单击该计划并选择“将执行计划另存为...”以将计划保存为 XML 格式的文件。

Method 2 - Using SHOWPLAN options

方法 2 - 使用 SHOWPLAN 选项

This method is very similar to method 1 (in fact this is what SQL Server Management Studio does internally), however I have included it for completeness or if you don't have SQL Server Management Studio available.

此方法与方法 1 非常相似（实际上这是 SQL Server Management Studio 在内部执行的操作），但是为了完整性或如果您没有可用的 SQL Server Management Studio，我将其包含在内。

Before you run your query, run oneof the following statements. The statement must be the only statement in the batch, i.e. you cannot execute another statement at the same time:

在您运行查询，运行一个下列语句。该语句必须是批处理中唯一的语句，即您不能同时执行另一个语句：

SET SHOWPLAN_TEXT ON
SET SHOWPLAN_ALL ON
SET SHOWPLAN_XML ON
SET STATISTICS PROFILE ON
SET STATISTICS XML ON -- The is the recommended option to use

These are connection options and so you only need to run this once per connection. From this point on all statements run will be acompanied by an additional resultsetcontaining your execution plan in the desired format - simply run your query as you normally would to see the plan.

这些是连接选项，因此每个连接只需运行一次。从这一点开始，所有运行的语句都将伴随一个额外的结果集，其中包含所需格式的执行计划 - 只需像往常一样运行查询即可查看计划。

Once you are done you can turn this option off with the following statement:

完成后，您可以使用以下语句关闭此选项：

SET <<option>> OFF

Comparison of execution plan formats

执行计划格式比较

Unless you have a strong preference my recommendation is to use the STATISTICS XMLoption. This option is equivalent to the "Include Actual Execution Plan" option in SQL Server Management Studio and supplies the most information in the most convenient format.

除非您有强烈的偏好，否则我的建议是使用该STATISTICS XML选项。此选项等效于 SQL Server Management Studio 中的“包括实际执行计划”选项，并以最方便的格式提供最多的信息。

• SHOWPLAN_TEXT- Displays a basic text based estimated execution plan, without executing the query
• SHOWPLAN_ALL- Displays a text based estimated execution plan with cost estimations, without executing the query
• SHOWPLAN_XML- Displays an XML based estimated execution plan with cost estimations, without executing the query. This is equivalent to the "Display Estimated Execution Plan..." option in SQL Server Management Studio.
• STATISTICS PROFILE- Executes the query and displays a text based actual execution plan.
• STATISTICS XML- Executes the query and displays an XML based actual execution plan. This is equivalent to the "Include Actual Execution Plan" option in SQL Server Management Studio.

• SHOWPLAN_TEXT- 显示基于文本的基本估计执行计划，而不执行查询
• SHOWPLAN_ALL- 显示基于文本的估计执行计划和成本估计，而不执行查询
• SHOWPLAN_XML- 显示基于 XML 的估计执行计划和成本估计，而不执行查询。这相当于 SQL Server Management Studio 中的“显示估计的执行计划...”选项。
• STATISTICS PROFILE- 执行查询并显示基于文本的实际执行计划。
• STATISTICS XML- 执行查询并显示基于 XML 的实际执行计划。这相当于 SQL Server Management Studio 中的“包括实际执行计划”选项。

Method 3 - Using SQL Server Profiler

方法 3 - 使用 SQL Server Profiler

If you can't run your query directly (or your query doesn't run slowly when you execute it directly - remember we want a plan of the query performing badly), then you can capture a plan using a SQL Server Profiler trace. The idea is to run your query while a trace that is capturing one of the "Showplan" events is running.

如果您不能直接运行您的查询（或者您的查询在您直接执行时不会运行缓慢 - 请记住，我们想要一个执行不佳的查询计划），那么您可以使用 SQL Server Profiler 跟踪捕获一个计划。这个想法是在捕获“Showplan”事件之一的跟踪正在运行时运行您的查询。

Note that depending on load you canuse this method on a production environment, however you should obviously use caution. The SQL Server profiling mechanisms are designed to minimize impact on the database but this doesn't mean that there won't be anyperformance impact. You may also have problems filtering and identifying the correct plan in your trace if your database is under heavy use. You should obviously check with your DBA to see if they are happy with you doing this on their precious database!

请注意，根据负载，您可以在生产环境中使用此方法，但显然您应该谨慎使用。SQL Server 分析机制旨在最大限度地减少对数据库的影响，但这并不意味着不会对性能产生任何影响。如果您的数据库被大量使用，您也可能在过滤和识别跟踪中的正确计划时遇到问题。您显然应该咨询您的 DBA，看看他们是否对您在他们宝贵的数据库上执行此操作感到满意！

1. Open SQL Server Profiler and create a new trace connecting to the desired database against which you wish to record the trace.
2. Under the "Events Selection" tab check "Show all events", check the "Performance" -> "Showplan XML" row and run the trace.
3. While the trace is running, do whatever it is you need to do to get the slow running query to run.
4. Wait for the query to complete and stop the trace.
5. To save the trace right click on the plan xml in SQL Server Profiler and select "Extract event data..." to save the plan to file in XML format.

1. 打开 SQL Server Profiler 并创建一个新的跟踪，该跟踪连接到您希望记录跟踪的所需数据库。
2. 在“事件选择”选项卡下，选中“显示所有事件”，选中“性能”->“显示计划 XML”行并运行跟踪。
3. 在跟踪运行时，执行任何您需要执行的操作以使运行缓慢的查询运行。
4. 等待查询完成并停止跟踪。
5. 要保存跟踪，请右键单击 SQL Server Profiler 中的计划 xml，然后选择“提取事件数据...”以将计划保存为 XML 格式的文件。

The plan you get is equivalent to the "Include Actual Execution Plan" option in SQL Server Management Studio.

您获得的计划相当于 SQL Server Management Studio 中的“包括实际执行计划”选项。

Method 4 - Inspecting the query cache

方法 4 - 检查查询缓存

If you can't run your query directly and you also can't capture a profiler trace then you can still obtain an estimated plan by inspecting the SQL query plan cache.

如果您无法直接运行查询，也无法捕获分析器跟踪，那么您仍然可以通过检查 SQL 查询计划缓存来获取估计计划。

We inspect the plan cache by querying SQL Server DMVs. The following is a basic query which will list all cached query plans (as xml) along with their SQL text. On most database you will also need to add additional filtering clauses to filter the results down to just the plans you are interested in.

我们通过查询 SQL Server DMV来检查计划缓存。以下是一个基本查询，它将列出所有缓存的查询计划（作为 xml）及其 SQL 文本。在大多数数据库上，您还需要添加额外的过滤子句，将结果过滤到您感兴趣的计划。

SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)

Execute this query and click on the plan XML to open up the plan in a new window - right click and select "Save execution plan as..." to save the plan to file in XML format.

执行此查询并单击计划 XML 以在新窗口中打开计划 - 右键单击​​并选择“将执行计划另存为...”以将计划以 XML 格式保存到文件中。

Notes:

笔记：

Because there are so many factors involved (ranging from the table and index schema down to the data stored and the table statistics) you should alwaystry to obtain an execution plan from the database you are interested in (normally the one that is experiencing a performance problem).

由于涉及的因素太多（从表和索引模式到存储的数据和表统计信息），您应该始终尝试从您感兴趣的数据库中获取执行计划（通常是正在经历性能问题的数据库）问题）。

You can't capture an execution plan for encrypted stored procedures.

您无法捕获加密存储过程的执行计划。

"actual" vs "estimated" execution plans

“实际”与“估计”执行计划

An actualexecution plan is one where SQL Server actually runs the query, whereas an estimatedexecution plan SQL Server works out what it woulddo without executing the query. Although logically equivalent, an actual execution plan is much more useful as it contains additional details and statistics about what actually happened when executing the query. This is essential when diagnosing problems where SQL Servers estimations are off (such as when statistics are out of date).

一个实际的执行计划是其中的SQL Server实际运行查询，而一个估计的执行计划的SQL Server使用的是哪种它会不执行查询做。尽管在逻辑上是等价的，但实际的执行计划更有用，因为它包含有关执行查询时实际发生的情况的其他详细信息和统计信息。这在诊断 SQL Server 估计值关闭的问题（例如统计数据过时时）时至关重要。

• Estimated and Actual execution plan revisited

• 重新审视估计和实际执行计划

How do I interpret a query execution plan?

如何解释查询执行计划？

This is a topic worthy enough for a (free) bookin its own right.

这是一个足以作为（免费）书本身的主题。

See also:

也可以看看：

• Execution Plan Basics
• SHOWPLAN Permission and Transact-SQL Batches
• SQL Server 2008 – Using Query Hashes and Query Plan Hashes
• Analyzing the SQL Server Plan Cache

• 执行计划基础
• SHOWPLAN 权限和 Transact-SQL 批处理
• SQL Server 2008 – 使用查询哈希和查询计划哈希
• 分析 SQL Server 计划缓存

回答by Martin Smith

In addition to the comprehensive answer already posted sometimes it is useful to be able to access the execution plan programatically to extract information. Example code for this is below.

除了已经发布的综合答案之外，有时能够以编程方式访问执行计划以提取信息也很有用。示例代码如下。

DECLARE @TraceID INT
EXEC StartCapture @@SPID, @TraceID OUTPUT
EXEC sp_help 'sys.objects' /*<-- Call your stored proc of interest here.*/
EXEC StopCapture @TraceID

Example StartCaptureDefinition

示例StartCapture定义

CREATE PROCEDURE StartCapture
@Spid INT,
@TraceID INT OUTPUT
AS
DECLARE @maxfilesize BIGINT = 5
DECLARE @filepath NVARCHAR(200) = N'C:\trace_' + LEFT(NEWID(),36)

EXEC sp_trace_create @TraceID OUTPUT, 0, @filepath, @maxfilesize, NULL 

exec sp_trace_setevent @TraceID, 122, 1, 1
exec sp_trace_setevent @TraceID, 122, 22, 1
exec sp_trace_setevent @TraceID, 122, 34, 1
exec sp_trace_setevent @TraceID, 122, 51, 1
exec sp_trace_setevent @TraceID, 122, 12, 1
-- filter for spid
EXEC sp_trace_setfilter @TraceID, 12, 0, 0, @Spid
-- start the trace
EXEC sp_trace_setstatus @TraceID, 1

Example StopCaptureDefinition

示例StopCapture定义

CREATE  PROCEDURE StopCapture
@TraceID INT
AS
WITH  XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sql), 
      CTE
     as (SELECT CAST(TextData AS VARCHAR(MAX)) AS TextData,
                ObjectID,
                ObjectName,
                EventSequence,
                /*costs accumulate up the tree so the MAX should be the root*/
                MAX(EstimatedTotalSubtreeCost) AS EstimatedTotalSubtreeCost
         FROM   fn_trace_getinfo(@TraceID) fn
                CROSS APPLY fn_trace_gettable(CAST(value AS NVARCHAR(200)), 1)
                CROSS APPLY (SELECT CAST(TextData AS XML) AS xPlan) x
                CROSS APPLY (SELECT T.relop.value('@EstimatedTotalSubtreeCost',
                                            'float') AS EstimatedTotalSubtreeCost
                             FROM   xPlan.nodes('//sql:RelOp') T(relop)) ca
         WHERE  property = 2
                AND TextData IS NOT NULL
                AND ObjectName not in ( 'StopCapture', 'fn_trace_getinfo' )
         GROUP  BY CAST(TextData AS VARCHAR(MAX)),
                   ObjectID,
                   ObjectName,
                   EventSequence)
SELECT ObjectName,
       SUM(EstimatedTotalSubtreeCost) AS EstimatedTotalSubtreeCost
FROM   CTE
GROUP  BY ObjectID,
          ObjectName  

-- Stop the trace
EXEC sp_trace_setstatus @TraceID, 0
-- Close and delete the trace
EXEC sp_trace_setstatus @TraceID, 2
GO

回答by Tigerjz32

Assuming you're using Microsoft SQL Server Management Studio

假设您使用的是 Microsoft SQL Server Management Studio

• For Estimated Query Planyou can press Ctrl + Lor the following button.

• 对于Estimated Query Plan，您可以按Ctrl + L或以下按钮。

• For Actual Query Plan, you can press Ctrl + Mor the following button before executing query.

• 对于Actual Query Plan，您可以在执行查询之前按Ctrl + M或以下按钮。

• For Live Query Plan, (only in SSMS 2016) use the following button before executing query.

• 对于实时查询计划，（仅在 SSMS 2016 中）在执行查询之前使用以下按钮。

回答by Marcin Czyz

Beside the methods described in previous answers, you can also use a free execution plan viewer and query optimization tool ApexSQL Plan(which I've recently bumped into).

除了前面答案中描述的方法之外，您还可以使用免费的执行计划查看器和查询优化工具ApexSQL Plan（我最近遇到过）。

You can install and integrate ApexSQL Plan into SQL Server Management Studio, so execution plans can be viewed from SSMS directly.

您可以将 ApexSQL 计划安装并集成到 SQL Server Management Studio 中，因此可以直接从 SSMS 查看执行计划。

Viewing Estimated execution plans in ApexSQL Plan

在 ApexSQL Plan 中查看估计的执行计划

1. Click the New Querybutton in SSMS and paste the query text in the query text window. Right click and select the “Display Estimated Execution Plan” option from the context menu.

1. 单击SSMS 中的新建查询按钮，然后将查询文本粘贴到查询文本窗口中。右键单击并从上下文菜单中选择“显示估计的执行计划”选项。

1. The execution plan diagrams will be shown the Execution Plan tab in the results section. Next right-click the execution plan and in the context menu select the “Open in ApexSQL Plan” option.

2. 执行计划图将显示在结果部分的执行计划选项卡中。接下来右键单击执行计划并在上下文菜单中选择“在 ApexSQL 计划中打开”选项。

1. The Estimated execution plan will be opened in ApexSQL Plan and it can be analyzed for query optimization.

3. Estimated execution plan 将在 ApexSQL Plan 中打开，可以进行分析以进行查询优化。

Viewing Actual execution plans in ApexSQL Plan

在 ApexSQL Plan 中查看实际执行计划

To view the Actual execution plan of a query, continue from the 2nd step mentioned previously, but now, once the Estimated plan is shown, click the “Actual” button from the main ribbon bar in ApexSQL Plan.

要查看查询的实际执行计划，请从前面提到的第二步继续，但是现在，一旦显示了估计计划，请单击 ApexSQL 计划中主功能区栏中的“实际”按钮。

Once the “Actual” button is clicked, the Actual execution plan will be shown with detailed preview of the cost parameters along with other execution plan data.

单击“实际”按钮后，将显示实际执行计划，其中包含成本参数的详细预览以及其他执行计划数据。

More information about viewing execution plans can be found by following this link.

点击此链接可以找到有关查看执行计划的更多信息。

回答by Alexander Abakumov

My favourite tool for obtaining and deeply analyzing query execution plans is SQL Sentry Plan Explorer. It's much more user-friendly, convenient and comprehensive for the detail analysis and visualization of execution plans than SSMS.

我最喜欢的用于获取和深入分析查询执行计划的工具是SQL Sentry Plan Explorer。对于执行计划的详细分析和可视化，它比 SSMS 更加人性化、方便和全面。

Here is a sample screen shot for you to have an idea of what functionality is offered by the tool:

这是一个示例屏幕截图，可让您了解该工具提供的功能：

It's only one of the views available in the tool. Notice a set of tabs to the bottom of the app window, which lets you get different types of your execution plan representation and useful additional information as well.

它只是工具中可用的视图之一。请注意应用程序窗口底部的一组选项卡，它们可让您获得不同类型的执行计划表示以及有用的附加信息。

In addition, I haven't noticed any limitations of its free edition that prevents using it on a daily basis or forces you to purchase the Pro version eventually. So, if you prefer to stick with the free edition, nothing forbids you from doing so.

此外，我还没有注意到它的免费版有任何限制，无法每天使用它或迫使您最终购买 Pro 版。因此，如果您更愿意坚持使用免费版，那么没有什么可以禁止您这样做的。

UPDATE:(Thanks to Martin Smith) Plan Explorer now is free! See http://www.sqlsentry.com/products/plan-explorer/sql-server-query-viewfor details.

更新：（感谢Martin Smith）Plan Explorer 现在是免费的！有关详细信息，请参阅http://www.sqlsentry.com/products/plan-explorer/sql-server-query-view。

回答by Dave Mason

Query plans can be obtained from an Extended Events session via the query_post_execution_showplanevent. Here's a sample XEvent session:

可以通过query_post_execution_showplan事件从扩展事件会话中获取查询计划。这是一个示例 XEvent 会话：

/*
    Generated via "Query Detail Tracking" template.
*/
CREATE EVENT SESSION [GetExecutionPlan] ON SERVER 
ADD EVENT sqlserver.query_post_execution_showplan(
    ACTION(package0.event_sequence,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)),

/* Remove any of the following events (or include additional events) as desired. */
ADD EVENT sqlserver.error_reported(
    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.module_end(SET collect_statement=(1)
    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.rpc_completed(
    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.sp_statement_completed(SET collect_object_name=(1)
    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))) 
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO

After you create the session, (in SSMS) go to the Object Explorer and delve down into Management | Extended Events | Sessions. Right-click the "GetExecutionPlan" session and start it. Right-click it again and select "Watch Live Data".

创建会话后，（在 SSMS 中）转到对象资源管理器并深入研究管理 | 扩展活动 | 会议。右键单击“GetExecutionPlan”会话并启动它。再次右键单击它并选择“观看实时数据”。

Next, open a new query window and run one or more queries. Here's one for AdventureWorks:

接下来，打开一个新的查询窗口并运行一个或多个查询。这是 AdventureWorks 的一个：

USE AdventureWorks;
GO

SELECT p.Name AS ProductName, 
    NonDiscountSales = (OrderQty * UnitPrice),
    Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount)
FROM Production.Product AS p 
INNER JOIN Sales.SalesOrderDetail AS sod
    ON p.ProductID = sod.ProductID 
ORDER BY ProductName DESC;
GO

After a moment or two, you should see some results in the "GetExecutionPlan: Live Data" tab. Click one of the query_post_execution_showplan events in the grid, and then click the "Query Plan" tab below the grid. It should look similar to this:

一两分钟后，您应该会在“GetExecutionPlan: Live Data”选项卡中看到一些结果。单击网格中的 query_post_execution_showplan 事件之一，然后单击网格下方的“查询计划”选项卡。它应该类似于：

EDIT: The XEvent code and the screen shot were generated from SQL/SSMS 2012 w/ SP2. If you're using SQL 2008/R2, you mightbe able to tweak the script to make it run. But that version doesn't have a GUI, so you'd have to extract the showplan XML, save it as a *.sqlplan file and open it in SSMS. That's cumbersome. XEvents didn't exist in SQL 2005 or earlier. So, if you're not on SQL 2012 or later, I'd strongly suggest one of the other answers posted here.

编辑：XEvent 代码和屏幕截图是从 SQL/SSMS 2012 w/SP2 生成的。如果您使用SQL 2008 / R2，您可能能够调整的脚本，使其运行。但该版本没有 GUI，因此您必须提取 showplan XML，将其保存为 *.sqlplan 文件并在 SSMS 中打开它。那很麻烦。XEvents 在 SQL 2005 或更早版本中不存在。因此，如果您使用的不是 SQL 2012 或更高版本，我强烈建议您使用此处发布的其他答案之一。

回答by vCillusion

Starting from SQL Server 2016+, Query Store feature was introduced to monitor performance. It provides insight into query plan choice and performance. It's not a complete replacement of trace or extended events, but as it's evolving from version to version, we might get a fully functional query store in future releases from SQL Server. The primary flow of Query Store

从 SQL Server 2016+ 开始，引入了查询存储功能来监控性能。它提供了对查询计划选择和性能的洞察。它不是跟踪或扩展事件的完全替代，但随着它从一个版本到另一个版本的演变，我们可能会在 SQL Server 的未来版本中获得一个功能齐全的查询存储。Query Store 的主要流程

1. SQL Server existing components interact with query store by utilising Query Store Manager.
2. Query Store Manager determines which Store should be used and then passes execution to that store (Plan or Runtime Stats or Query Wait Stats)
   • Plan Store - Persisting the execution plan information
   • Runtime Stats Store - Persisting the execution statistics information
   • Query Wait Stats Store - Persisting wait statistics information.
3. Plan, Runtime Stats and Wait store uses Query Store as an extension to SQL Server.

1. SQL Server 现有组件通过使用查询存储管理器与查询存储交互。
2. 查询存储管理器确定应该使用哪个存储，然后将执行传递给该存储（计划或运行时统计或查询等待统计）
   • 计划存储 - 保留执行计划信息
   • Runtime Stats Store - 保存执行统计信息
   • Query Wait Stats Store - 持久的等待统计信息。
3. 计划、运行时统计和等待存储使用查询存储作为 SQL Server 的扩展。

1. Enabling the Query Store: Query Store works at the database level on the server.

   • Query Store is not active for new databases by default.
   • You cannot enable the query store for the master or tempdbdatabase.
   • Available DMV

     sys.database_query_store_options(Transact-SQL)

2. Collect Information in the Query Store: We collect all the available information from the three stores using Query Store DMV (Data Management Views).

   • Query Plan Store:Persisting the execution plan information and it is accountable for capturing all information that is related to query compilation.

     sys.query_store_query(Transact-SQL) sys.query_store_plan(Transact-SQL) sys.query_store_query_text(Transact-SQL)

   • Runtime Stats Store:Persisting the execution statistics information and it is probably the most frequently updated store. These statistics represent query execution data.

     sys.query_store_runtime_stats(Transact-SQL)

   • Query Wait Stats Store:Persisting and capturing wait statistics information.

     sys.query_store_wait_stats(Transact-SQL)

1. 启用查询存储：查询存储在服务器上的数据库级别工作。

   • 默认情况下，新数据库的查询存储不处于活动状态。
   • 您不能为 master 或tempdb数据库启用查询存储。
   • 可用车管所

     sys.database_query_store_options(事务-SQL)

2. 在查询存储中收集信息：我们使用查询存储 DMV（数据管理视图）从三个存储中收集所有可用信息。

   • 查询计划存储：保存执行计划信息，负责捕获所有与查询编译相关的信息。

     sys.query_store_query(Transact-SQL) sys.query_store_plan(Transact-SQL) sys.query_store_query_text(Transact-SQL)

   • Runtime Stats Store：保存执行统计信息，它可能是更新最频繁的存储。这些统计信息代表查询执行数据。

     sys.query_store_runtime_stats(事务-SQL)

   • Query Wait Stats Store：持久化和捕获等待统计信息。

     sys.query_store_wait_stats(事务-SQL)

NOTE:Query Wait Stats Store is available only in SQL Server 2017+

注意：查询等待统计存储仅在 SQL Server 2017+ 中可用

回答by Daan

Like with SQL Server Management Studio (already explained), it is also possible with Datagrip as explained here.

像SQL Server Management Studio中（已经说明），也可能与Datagrip作为解释在这里。

1. Right-click an SQL statement, and select Explain plan.
2. In the Output pane, click Plan.
3. By default, you see the tree representation of the query. To see the query plan, click the Show Visualization icon, or press Ctrl+Shift+Alt+U

1. 右键单击 SQL 语句，然后选择解释计划。
2. 在“输出”窗格中，单击“计划”。
3. 默认情况下，您会看到查询的树形表示。要查看查询计划，请单击“显示可视化”图标，或按 Ctrl+Shift+Alt+U

回答by alkoln

Here's one important thing to know in addition to everything said before.

除了之前所说的所有内容之外，还有一件重要的事情需要了解。

Query plans are often too complex to be represented by the built-in XML column type which has a limitation of 127 levelsof nested elements. That is one of the reasons why sys.dm_exec_query_planmay return NULLor even throw an error in earlier MS SQL versions, so generally it's safer to use sys.dm_exec_text_query_planinstead. The latter also has a useful bonus feature of selecting a plan for a particular statementrather than the whole batch. Here's how you use it to view plans for currently running statements:

查询计划通常太复杂，无法由内置的 XML 列类型表示，该类型限制为 127 级嵌套元素。这是sys.dm_exec_query_planNULL在早期 MS SQL 版本中可能返回甚至抛出错误的原因之一，因此通常使用sys.dm_exec_text_query_plan更安全。后者还有一个有用的奖励功能，即为特定语句而不是整个批次选择计划。以下是您如何使用它来查看当前正在运行的语句的计划：

SELECT p.query_plan
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_text_query_plan(
                r.plan_handle,
                r.statement_start_offset,
                r.statement_end_offset) AS p

The text column in the resulting table is however not very handy compared to an XML column. To be able to click on the result to be opened in a separate tab as a diagram, without having to save its contents to a file, you can use a little trick (remember you cannot just use CAST(... AS XML)), although this will only work for a single row:

然而，与 XML 列相比，结果表中的文本列不是很方便。为了能够单击要在单独选项卡中作为图表打开的结果，而不必将其内容保存到文件中，您可以使用一个小技巧（请记住，您不能只使用CAST(... AS XML)），尽管这仅适用于单行：

SELECT Tag = 1, Parent = NULL, [ShowPlanXML!1!!XMLTEXT] = query_plan
FROM sys.dm_exec_text_query_plan(
                -- set these variables or copy values
                -- from the results of the above query
                @plan_handle,
                @statement_start_offset,
                @statement_end_offset)
FOR XML EXPLICIT

回答by Vlad Mihalcea

As I explained in this article, there are two execution plan types you can get when using SQL Server.

正如我在本文中所解释的，在使用 SQL Server 时，您可以获得两种执行计划类型。

Estimated execution plan

预计执行计划

The estimated execution plan is generated by the Optimizer without running the SQL query.

估计的执行计划由优化器生成，无需运行 SQL 查询。

In order to get the estimated execution plan, you need to enable the SHOWPLAN_ALLsetting prior to executing the query.

为了获得估计的执行计划，您需要SHOWPLAN_ALL在执行查询之前启用该设置。

SET SHOWPLAN_ALL ON

设置 SHOWPLAN_ALL 开启

Now, when executing the following SQL query:

现在，当执行以下 SQL 查询时：

SELECT p.id
FROM post p
WHERE EXISTS (
  SELECT 1
  FROM post_comment pc
  WHERE
    pc.post_id = p.id AND
    pc.review = 'Bingo'
)
ORDER BY p.title
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY

SQL Server will generate the following estimated execution plan:

SQL Server 将生成以下估计执行计划：

| NodeId | Parent | LogicalOp            | EstimateRows | EstimateIO  | EstimateCPU | AvgRowSize | TotalSubtreeCost | EstimateExecutions |
|--------|--------|----------------------|--------------|-------------|-------------|------------|------------------|--------------------|
| 1      | 0      | NULL                 | 10           | NULL        | NULL        | NULL       | 0.03374284       | NULL               |
| 2      | 1      | Top                  | 10           | 0           | 3.00E-06    | 15         | 0.03374284       | 1                  |
| 4      | 2      | Distinct Sort        | 30           | 0.01126126  | 0.000504114 | 146        | 0.03373984       | 1                  |
| 5      | 4      | Inner Join           | 46.698       | 0           | 0.00017974  | 146        | 0.02197446       | 1                  |
| 6      | 5      | Clustered Index Scan | 43           | 0.004606482 | 0.0007543   | 31         | 0.005360782      | 1                  |
| 7      | 5      | Clustered Index Seek | 1            | 0.003125    | 0.0001581   | 146        | 0.0161733        | 43                 |

After running the query we are interested in getting the estimated execution plan, you need to disable the SHOWPLAN_ALLas, otherwise, the current database session will only generate estimated execution plan instead of executing the provided SQL queries.

运行完我们感兴趣的查询得到估计的执行计划后，需要禁用SHOWPLAN_ALLas，否则当前数据库会话只会生成估计的执行计划，而不会执行提供的SQL查询。

SET SHOWPLAN_ALL OFF

SQL Server Management Studio estimated plan

SQL Server Management Studio 预估计划

In the SQL Server Management Studio application, you can easily get the estimated execution plan for any SQL query by hitting the CTRL+Lkey shortcut.

在 SQL Server Management Studio 应用程序中，您可以通过点击CTRL+L快捷键轻松获取任何 SQL 查询的估计执行计划。

Actual execution plan

实际执行计划

The actual SQL execution plan is generated by the Optimizer when running the SQL query. If the database table statistics are accurate, the actual plan should not differ significantly from the estimated one.

实际的 SQL 执行计划是在运行 SQL 查询时由优化器生成的。如果数据库表统计准确，实际计划应该与估计的计划没有太大差异。

To get the actual execution plan on SQL Server, you need to enable the STATISTICS IO, TIME, PROFILEsettings, as illustrated by the following SQL command:

要获取 SQL Server 上的实际执行计划，您需要启用STATISTICS IO, TIME, PROFILE设置，如以下 SQL 命令所示：

SET STATISTICS IO, TIME, PROFILE ON

Now, when running the previous query, SQL Server is going to generate the following execution plan:

现在，在运行前面的查询时，SQL Server 将生成以下执行计划：

| Rows | Executes | NodeId | Parent | LogicalOp            | EstimateRows | EstimateIO  | EstimateCPU | AvgRowSize | TotalSubtreeCost |
|------|----------|--------|--------|----------------------|--------------|-------------|-------------|------------|------------------|
| 10   | 1        | 1      | 0      | NULL                 | 10           | NULL        | NULL        | NULL       | 0.03338978       |
| 10   | 1        | 2      | 1      | Top                  | 1.00E+01     | 0           | 3.00E-06    | 15         | 0.03338978       |
| 30   | 1        | 4      | 2      | Distinct Sort        | 30           | 0.01126126  | 0.000478783 | 146        | 0.03338679       |
| 41   | 1        | 5      | 4      | Inner Join           | 44.362       | 0           | 0.00017138  | 146        | 0.02164674       |
| 41   | 1        | 6      | 5      | Clustered Index Scan | 41           | 0.004606482 | 0.0007521   | 31         | 0.005358581      |
| 41   | 41       | 7      | 5      | Clustered Index Seek | 1            | 0.003125    | 0.0001581   | 146        | 0.0158571        |

SQL Server parse and compile time:
   CPU time = 8 ms, elapsed time = 8 ms.

(10 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'post'. Scan count 0, logical reads 116, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'post_comment'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(6 row(s) affected)

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

After running the query we are interested in getting the actual execution plan, you need to disable the STATISTICS IO, TIME, PROFILE ONsettings like this:

运行查询后，我们对获取实际执行计划感兴趣，您需要禁用如下STATISTICS IO, TIME, PROFILE ON设置：

SET STATISTICS IO, TIME, PROFILE OFF

SQL Server Management Studio actual plan

SQL Server Management Studio 实际计划

In the SQL Server Management Studio application, you can easily get the estimated execution plan for any SQL query by hitting the CTRL+Mkey shortcut.

在 SQL Server Management Studio 应用程序中，您可以通过点击CTRL+M快捷键轻松获取任何 SQL 查询的估计执行计划。

For more details about getting an execution plan when using SQL Server, check out this article.

有关在使用 SQL Server 时获取执行计划的更多详细信息，请查看这篇文章。