How to view previous query plans?

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:

1. Go to the relevant record which you want to view the query plan.
3. Double click the query plan column. This will take you to the new query window in SQL Server Management Studio which has XML data.
4. 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.