Last week someone wanted to know How to get the execution plans of all the sessions which are active on the instance? Starting SQL Server 2005, there are plenty of DMVs which makes the DBA’s life less complicated. The DMV that answers this question is sys.dm_exec_query_plan. With this DMV, the Execution Plans of currently running sessions or cached sessions can be easily obtained. All this DMV needs is a plan_handle as parameter. The below query gets the session_id and the query_plan (in XML format).
SELECT er.session_id , qp.query_plan FROM sys.dm_exec_requests er CROSS APPLY sys. dm_exec_query_plan ( er.plan_handle ) qp
The output looks like this.
If SSMS version is 2005, then clicking on the query_plan will open the Execution Plan in XML format.
This file in turn needs to be saved as a .sqlplan file to view the Execution Plan in Graphical format. The “Save as” option in SSMS or any text editor can be used for this purpose.
The SSMS in SQL Server 2008 goes one step ahead. Clicking on the query_plan in the query output directly opens the Execution Plan in Graphical format.