Retrieve Query Plan From a SPID

Have you ever had the need to view a query plan in SQL Server for a slow running process put weren't sure how to pull it up?  The query below will bring up the query plan for you.  Simply update @spid with the spid of the process you'd like to view the query plan for and execute.  Click on the blue XML and SQL Server Management Studio will bring up a a graphical view of the query plan.

DECLARE @spid AS INT;

SET @spid = 86;   -- UPDATE VARIABLE SPID ID

SELECT session.session_id, sql_text.text as most_recent_requested_query, plan_text.query_plan

FROM sys.dm_exec_sessions as session WITH (NOLOCK)

LEFT OUTER JOIN sys.dm_exec_requests as request WITH (NOLOCK) ON session.session_id = request.session_id

OUTER APPLY sys.dm_exec_sql_text(request.sql_handle) as sql_text

OUTER APPLY sys.dm_exec_query_plan(request.plan_handle) as plan_text

WHERE session.session_id = @spid;

If for some reason when you click on the XML it simply bring up the XML in a new query window you can save the xml in a file with the extension .sqlplan and then open the file.  SSMS should then display the graphical plan.