SQL Server – How to Get Actual Execution Plan for Cancelled Query

execution-plansql serversql server 2014ssms

Sometimes a query will be taking ages and you don't want to have to run the whole thing. This is exactly the kind of time you may want to see the Actual Execution Plan to figure out why the query is inefficient. However I've noticed that when you tell SSMS to include the Actual Execution Plan in the results, it only does so when the query finishes successfully and is not cancelled! Why is this? Presumably the plan has to be generated first, so it could be sent first by SQL Server and be displayed even for a cancelled query. I know about the estimated execution plan but it's not the same.

Best Answer

In SQL Server 2016 Management studio you can see the execution plan while executing, using the include live execution plan. This works for SQL Server 2014+

enter image description here

For SQL Server 2014 I usually use this query to get the execution plan of execution query's

SELECT
    r.session_id
,   r.start_time
,   TotalElapsedTime_ms = r.total_elapsed_time
,   r.[status]
,   r.command
,   DatabaseName = DB_Name(r.database_id)
,   r.wait_type
,   r.last_wait_type
,   r.wait_resource
,   r.cpu_time
,   r.reads
,   r.writes
,   r.logical_reads
,   t.[text] AS [executing batch]
,   SUBSTRING(
                t.[text], r.statement_start_offset / 2, 
                (   CASE WHEN r.statement_end_offset = -1 THEN DATALENGTH (t.[text]) 
                         ELSE r.statement_end_offset 
                    END - r.statement_start_offset ) / 2 
             ) AS [executing statement] 
,   p.query_plan
FROM
    sys.dm_exec_requests r
CROSS APPLY
    sys.dm_exec_sql_text(r.sql_handle) AS t
CROSS APPLY 
    sys.dm_exec_query_plan(r.plan_handle) AS p
ORDER BY 
    r.total_elapsed_time DESC;

This gets the statement executing with their corresponding estimated query plan.

Related Question