Tuesday, November 30, 2010

Finding execution plans of currently active session sqls

Here is the script to find execution plans of currently running sqls on server. It is in xml format.

SELECT sp.spid,sp.cpu,sp.cmd, qp.query_plan from sys.sysprocesses as sp inner join sys.dm_exec_requests as er ON sp.spid=er.session_id
CROSS APPLY sys.dm_exec_query_plan(er.plan_handle)as qp
where sp.status not in ('sleeping','background') and sp.cmd not in ('DB MIRROR') order by cpu

No comments: