execution-plansql server
In SQL Server, how can we see actual execution plan of a query, after it has been executed?
As per my knowledge, only estimated execution plan (not actual execution plan) are stored in plan cache. is that correct?
Note: We do not have any third party monitoring tools.
Yes.
You need the USE PLAN hint.
In which you supply the XML from the first plan.
SELECT *
FROM T
OPTION (USE PLAN N'<?xml version="1.0" encoding="utf-16"?> ....')
Whilst it doesn't guarantee that the plan will be exactly the same (e.g. compute scalar operators can move around for example) it will likely be pretty close.
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+
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.
Best Answer
If you're running SQL Server 2012 or above, you could consider creating an Extended Events session and configure it to capture the query_post_execution_showplan event to capture the actual execution plan after the query has been executed. But obviously the session would need to be setup and enabled prior to query execution.
However, be aware that enabling this event will impact performance so this is probably something you'd want to switch on for investigative purposes, then switch off again once you've captured the plan you're after.