Get the query and execution plan for a session

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 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.