Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ( http://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!

share|improve this question
    
I've seen one on SO somewhere. Trying to find the link. –  Martin Smith Mar 9 '12 at 20:23
    
@MartinSmith - by "seen one" do you mean seen a visual representation of the showplan xml? If so, see my update. –  Jake Feasel Mar 9 '12 at 20:24
2  
Nope it was this. But if I haven't totally misunderstood I think data explorer is open source? Edit: Actually just read the last line of that answer and they are in fact apparently one and the same thing. –  Martin Smith Mar 9 '12 at 20:25
    
Wow @MartinSmith that's exactly what I'm after. Thanks a lot! If you want to put that down as an answer, I'll happily upvote and accept. –  Jake Feasel Mar 9 '12 at 20:28
    
I'll leave a comment on the answer on SO to see if the OP there wants to answer over here first as they do have an account on here. –  Martin Smith Mar 9 '12 at 20:32

1 Answer 1

up vote 5 down vote accepted

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:

Let me know if you have any suggestions for improvements!

share|improve this answer
    
Justin - I cannot sing your praises high enough! Your code was super easy to integrate (a match made in heaven!), and just amazing results (as you can see). It's pretty rare that there is something out there that really is the perfect solution to a problem, but this was. Thanks so much! –  Jake Feasel Mar 12 '12 at 17:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.