Sql-server – View the output of Adam Machanic’s sp_WhoIsActive

execution-plansql server

I'm attempting to look at the XML output by Adam Mechanic's sp_WhoIsActive stored procedure.

When I use a typical XML editor, all I see is the raw XML. I'd like to be able to view the graphical execution plan the way it's shown in SQL Server Management Studio when you run a query including the actual execution plan.

I'm looking at the sample actual query plan demonstrated by Brent Ozar and want to see that style of plan on my own queries.

Best Answer

You need to use a product which understands that the XML represents a SQL Server execution plan.

Make sure you're using the latest version of SQL Server Management Studio (v17.5 as of this post) and, when you click the XML for the plan returned by sp_whoisactive, you should get an extra window showing the execution plan in graphical representation.

I'd strongly suggest downloading the latest version of SentryOne Plan Explorer which is now completely free. The latest version has SSMS add-in support such that, when you see the graphical plan in SSMS, you can right-click and choose 'View with SentryOne Plan Explorer' from the context menu and the execution plan will be displayed in SentryOne Plan Explorer. See the SentryOne post Announcing Plan Explorer Add-In Support for SSMS 2016.

Related Solutions

Oracle build_plan_xml visualizer

You can use dbms_xplan.display_plan to get text, xml, html or 'active' output as a clob:

select dbms_xplan.display_plan(format=>'ALL', type=>'HTML') from dual;

select dbms_xplan.display_plan(format=>'ALL', type=>'ACTIVE') from dual;

Html is similar to the text output, but the 'active' type is quite different:

Active reports have a rich, interactive user interface akin to that found in Enterprise Manager while not requiring any EM installation. The report file built is in HTML format, so it can be interpreted by most modern browsers. The code powering the active report is downloaded transparently by the web browser when the report is first viewed, hence viewing it requires outside connectivity.

Sql-server – Capture Complex Actual Execution Plans

I can't say for certain because I've never dealt with that large a stored procedure but you could try this:

SELECT query_plan
FROM sys.dm_exec_procedure_stats
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE object_id = object_id('procedure_name')

Then save the output to a text file.

Best Answer

Related Solutions

Oracle build_plan_xml visualizer

Sql-server – Capture Complex Actual Execution Plans

Related Question