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…
- Click the actual hyperlink and let the XML plan load in SSMS
- Click File > Save Plan.xml As…
- 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