Interpreting SQL Server's Showplan XML

Question

I just rolled out a feature on my site http://sqlfiddle.com that allows users to view the raw execution plans for their queries. In the case of PostgreSQL, MySQL, and (to some extent) Oracle, looking at the raw execution plan output appears comprehendible. However, if you look at the execution plan output for SQL Server (generated with SET SHOWPLAN_XML ON), there is a simply massive amount of XML to wade through, even for relatively simple queries. Here's an example (taken from the last query's execution plan for this 'fiddle': http://sqlfiddle.com/#!3/1fa93/1):

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.50.2500.0">
    <BatchSequence>
        <Batch>
            <Statements>
                <StmtSimple StatementText="&#xa;select * from supportContacts" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="0.0032853" StatementEstRows="3" StatementOptmLevel="TRIVIAL" QueryHash="0x498D13A3874D9B6E" QueryPlanHash="0xD5DDBD3C2D195E96">
                    <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="false" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"/>
                    <QueryPlan CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="72">
                        <RelOp NodeId="0" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="3" EstimateIO="0.003125" EstimateCPU="0.0001603" AvgRowSize="42" EstimatedTotalSubtreeCost="0.0032853" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                            <OutputList>
                                <ColumnReference Database="[db_1fa93]" Schema="[dbo]" Table="[supportContacts]" Column="id"/>
                                <ColumnReference Database="[db_1fa93]" Schema="[dbo]" Table="[supportContacts]" Column="type"/>
                                <ColumnReference Database="[db_1fa93]" Schema="[dbo]" Table="[supportContacts]" Column="details"/>
                            </OutputList>
                            <IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0">
                                <DefinedValues>
                                    <DefinedValue>
                                        <ColumnReference Database="[db_1fa93]" Schema="[dbo]" Table="[supportContacts]" Column="id"/>
                                    </DefinedValue>
                                    <DefinedValue>
                                        <ColumnReference Database="[db_1fa93]" Schema="[dbo]" Table="[supportContacts]" Column="type"/>
                                    </DefinedValue>
                                    <DefinedValue>
                                        <ColumnReference Database="[db_1fa93]" Schema="[dbo]" Table="[supportContacts]" Column="details"/>
                                    </DefinedValue>
                                </DefinedValues>
                                <Object Database="[db_1fa93]" Schema="[dbo]" Table="[supportContacts]" Index="[PK__supportC__3213E83F7F60ED59]" IndexKind="Clustered"/>
                            </IndexScan>
                        </RelOp>
                    </QueryPlan>
                </StmtSimple>
            </Statements>
        </Batch>
    </BatchSequence>
</ShowPlanXML>

My goal with this function was to provide the user with something meaningful for analysis of their query performance (say, to compare against other possible query implementation approaches). However, I am now concerned that I'm providing TOO MUCH data to the user. I need to find a way to make this useful.

One idea I had was to build an easy mechanism for downloading the output as a .sqlplan file, so they could open it up with SSMS and look at it graphically there. I'd rather not have to rely on users having such external tools available, though, if there is another reasonable option available.

Another idea I had was using some kind of XSLT transform that could pull out and nicely present the most important bits. This sounds like a ton of work, though, and there doesn't appear to be any good documentation on how I would even start that. Does anyone know of existing XSLT templates that work with this schema?

Any other thoughts?

Update

Okay, I just took a look at the "Execution Plan" tab for a query on http://data.stackexchange.com/. How do I get that?! That is awesome! I'm hoping that's not some internal-to-stackexchange-only library that they built in-house. Anyone know?

Update 2

I've just rolled out the awesome HTML+CSS+JS view of the showplan XML using the XSLT from this project : http://code.google.com/p/html-query-plan/ (you can see it now if you visit the original link, above).

I will wait and see if the author of this project ( https://dba.stackexchange.com/users/5996/justin ) shows up to answer this question, so I can give him his proper due credit. If I don't see him pop in after a while, then I'll happily give the credit to Martin or, failing that even, I'll just answer it myself. Thank you Justin and Martin!

Answer 1

First let me just say - fantastic looking site :)

This sort of thing is exactly what I wrote that XSLT for - I'm glad that others are finding it useful!

I have to confes its something that I wrote a while ago and then got kind of side-tracked, there are some improvements that I've been planning to make for a while now that I hope to get around to actually implementing soon!

Some links:

• html-query-plan Project homepage
• Tools for visualising execution xml plans as HTML (Stack Overflow Question)

Let me know if you have any suggestions for improvements!