T-SQL – To Get Graphical Execution Plan for Active Sessions
If you want to get the graphical execution plan for all active sessions on an instance using T-SQL Script, then this handy script would be a great help. The below query gets the session_id and the query_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.
FROM sys.dm_exec_requests er
CROSS APPLY sys. dm_exec_query_plan( er.plan_handle ) qp
If you click the query_plan link, you will get the execution plan in the graphical format (screenshot below)