A simple piece of code to help you get the execution plan and the query text for a currently running query. This is especially useful when dynamic sql is being run against your database or if DBCC InputBuffer reports that a stored procedure is being run. You can actually view the execution plan for the batch that it’s running. It works using the 2005 management views and functions.
First of all execute sp_who2 to find out the SessionID that you want to know about.
Then modify the code below to filter for that SessionID.
SELECT T.[text], P.query_plan
FROM sys.dm_exec_requests R
CROSS APPLY master.sys.dm_exec_sql_text(R.sql_handle) T
CROSS APPLY master.sys.dm_exec_query_plan(plan_handle) P
WHERE R.Session_id = 59
Simple yet effective 🙂