How do you view previous query plans?
You can view previously run queries’execution plan using sys.dm_exec_query_plan dmv and joining it with other two dmvs in SQL Server 2005.
select qs.execution_count
, qs.total_elapsed_time, qs.last_elapsed_time
, qs.min_elapsed_time, qs.max_elapsed_time
, substring(st.text, (qs.statement_start_offset/2)+1
, ((case qs.statement_end_offset
when -1 then datalength(st.text)
else qs.statement_end_offset
end – qs.statement_start_offset)/2) + 1) as statement_text
, qp.query_plan
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp
You will see the following results after running the above query.
Statement_text will give you the t-sql script you ran while query_plan will give you the query plan in xml format.
You can view the execution plan by using these steps:
- Go to the relevant record which you want to view the query plan.
- Double click the query plan column. This will take you to the new query window in SQL Server Management Studio which has XML data.
- Save the file with sqlplan extension and open it again. You will see the query plan.
Note: If using SQL Server 2008, you don’t have to save the file with sqlpan extension. Clicking the query_plan column will show the execution plan in SQL Server management studio.
]]>