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

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

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 StartCapture Definition

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 StopCapture Definition

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

Assuming you're using Microsoft SQL Server Management Studio

• For Estimated Query Plan you can press Ctrl + L or the following button.

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

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

Explaining execution plan can be very detailed and takes up quite a reading time, but in summary if you use 'explain' before the query it should give you a lot of info including which parts were executed first and so. if you wanna read a bit more details about this, I compiled a small blog about this which points you as well to the right refs. https://medium.com/swlh/jetbrains-datagrip-explain-plan-ac406772c470

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

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. 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 tempdb database.
   • 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)

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

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.

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.

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

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).

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

Viewing Estimated execution plans in ApexSQL Plan

1. Click the New Query button 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.

2. 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.

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

Viewing Actual execution plans in 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.

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.

You can also do it via powershell using SET STATISTICS XML ON to get the actual plan. I've written it so that it merges multi-statement plans into one plan;

    ########## BEGIN : SCRIPT VARIABLES #####################
    [string]$server = '.\MySQLServer'
    [string]$database = 'MyDatabase'
    [string]$sqlCommand = 'EXEC sp_ExampleSproc'
    [string]$XMLOutputFileName = 'sp_ExampleSproc'
    [string]$XMLOutputPath = 'C:\SQLDumps\ActualPlans\'
    ########## END   : SCRIPT VARIABLES #####################

    #Set up connection
    $connectionString = "Persist Security Info=False;Integrated Security=true;Connection Timeout=0;Initial Catalog=$database;Server=$server"
    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)

    #Set up commands
    $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
    $command.CommandTimeout = 0
    $commandXMLActPlanOn = new-object system.data.sqlclient.sqlcommand("SET STATISTICS XML ON",$connection)
    $commandXMLActPlanOff = new-object system.data.sqlclient.sqlcommand("SET STATISTICS XML OFF",$connection)

    $connection.Open()

    #Enable session XML plan
    $result = $commandXMLActPlanOn.ExecuteNonQuery()

    #Execute SP and return resultsets into a dataset
    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataSet) | Out-Null

    #Set up output file name and path
    [string]$fileNameDateStamp = get-date -f yyyyMMdd_HHmmss
    [string]$XMLOutputFilePath = "$XMLOutputPath$XMLOutputFileName`_$fileNameDateStamp.sqlplan"

    #Pull XML plans out of dataset and merge into one multi-statement plan
    [int]$cntr = 1
    ForEach($table in $dataset.Tables)
    {
            if($table.Columns[0].ColumnName -eq "Microsoft SQL Server 2005 XML Showplan")
            {

                [string]$fullXMLPlan = $Table.rows[0]."Microsoft SQL Server 2005 XML Showplan"

                if($cntr -eq 1)
                    {

                    [regex]$rx = "\<ShowPlanXML xmlns\=.{1,}\<Statements\>"
                    [string]$startXMLPlan = $rx.Match($fullXMLPlan).Value
                    [regex]$rx = "\<\/Statements\>.{1,}\<\/ShowPlanXML\>"
                    [string]$endXMLPlan = $rx.Match($fullXMLPlan).Value

                    $startXMLPlan | out-file -Append -FilePath $XMLOutputFilePath

                    }

                [regex]$rx = "\<StmtSimple.{1,}\<\/StmtSimple\>"
                [string]$bodyXMLPlan = $rx.Match($fullXMLPlan).Value

                $bodyXMLPlan | out-file -Append -FilePath $XMLOutputFilePath

                $cntr += 1
            } 
    }

    $endXMLPlan | out-file -Append -FilePath $XMLOutputFilePath

    #Disable session XML plan
    $result = $commandXMLActPlanOff.ExecuteNonQuery()

    $connection.Close()

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

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

Estimated execution plan

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

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

SET SHOWPLAN_ALL ON

Now, when executing the following SQL query:

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:

| 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_ALL as, otherwise, the current database session will only generate estimated execution plan instead of executing the provided SQL queries.

SET SHOWPLAN_ALL OFF

SQL Server Management Studio estimated plan

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

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.

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

SET STATISTICS IO, TIME, PROFILE ON

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

| 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 ON settings like this:

SET STATISTICS IO, TIME, PROFILE OFF

SQL Server Management Studio actual plan

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

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 levels of nested elements. That is one of the reasons why sys.dm_exec_query_plan may return NULL or even throw an error in earlier MS SQL versions, so generally it's safer to use sys.dm_exec_text_query_plan instead. The latter also has a useful bonus feature of selecting a plan for a particular statement rather than the whole batch. Here's how you use it to view plans for currently running statements:

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:

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

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

/*
    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".

Next, open a new query window and run one or more queries. Here's one for 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:

EDIT: The XEvent code and the screen shot were generated from SQL/SSMS 2012 w/ SP2. If you're using SQL 2008/R2, you might be 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.