3

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.

5

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.

  • 3
    (1) only SSMS has to be 2016; it can capture live query statistics against 2014+ (2) the latter will only expose the estimated plan. – Aaron Bertrand Dec 13 '16 at 11:44
  • You are absolutely right! Did not know about the live query statistics 2014+. I'll edit the response. – Stijn Wynants Dec 13 '16 at 11:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.