Converting a SQL Execution Plan from XML to Graphical

March 29th, 2012
by The Grateful DBA

While I’ve been familiar for many years with the ‘sys.dm_exec_query_plan’ dmv that will produce an XML formatted version of an execution plan (using the ‘query_plan’ column), I had to refresh my memory today on just how  to convert it to something a bit more readable.  Well, today I get to refresh your memory as well.

I’m probably not exaggerating when I say 99% of us DBA’s prefer to look at the graphical version of an (actual) execution plan over the XML gobbledygook.  While even the graphical version can get extremely large and hard to deal with, it’s nothing like trying to find what you need from an XML file of the same plan.  Today I wrote a small script to gather performance metrics from joining ‘sys.dm_exec_query_stats’, ‘sys.dm_exec_sql_text’, and ‘sys.dm_exec_query_plan’.  The query_plan column produces a very handy hyperlink to the execution plan currently in cache, but by default it comes out in XML format.  So, how can I change to a graphical version so that I can much more easily iterate through it and see what scans/seeks/etc are being used?

Just like a waltz, it turns out it is indeed a very simple three-step process…

    1. Click the actual hyperlink and let the XML plan load in SSMS
    2. Click File > Save Plan.xml As…
    3. Save it locally with the file extension “.sqlplan”

Really, that’s all there is to it.  Just double-click this new “.sqlplan” file and it should open in SSMS as a graphical plan….ready for you to peruse to your heart’s content.  Told you it was easy.

Have a grateful day…Troy


Tags: , , ,
Posted in DMV's, SSMS, T-SQL | Comments (0)

No comments yet

Leave a Reply

Leave a Reply

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

Get Adobe Flash player