How to Post Execution Plan - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

How to Post Execution Plan

Author

Topic

dbthj
Posting Yak Master

143 Posts

Posted - 2011-08-09 : 11:29:08

A number of forum threads mention posting an execution plan. How is that done?

I see how to include code and insert an image and such, but attaching files does not seem to be possible.

The forum FAQ doesn't seem to say how to do this.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-09 : 11:40:43

For a small plan, you can "Show Executino Plan XML..." in Management Studio and copy and paste it:

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.50.1617.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="19972" StatementId="1" StatementOptmLevel="TRIVIAL" StatementSubTreeCost="0.44451" StatementText="select * from Person.Contact" StatementType="SELECT" QueryHash="0x9F46B07267932475" QueryPlanHash="0xF03D9A6D9E97FE94">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan DegreeOfParallelism="1" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="80">
<RelOp AvgRowSize="4387" EstimateCPU="0.0221262" EstimateIO="0.422384" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="19972" LogicalOp="Clustered Index Scan" NodeId="0" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.44451" TableCardinality="19972">
<OutputList>
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Column="ContactID" />
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Column="NameStyle" />
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Column="Title" />
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Column="FirstName" />
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Column="MiddleName" />
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Column="LastName" />
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Column="Suffix" />
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Column="EmailAddress" />
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Column="EmailPromotion" />
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Column="Phone" />
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Column="PasswordHash" />
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Column="PasswordSalt" />
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Column="AdditionalContactInfo" />
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Column="rowguid" />
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Column="ModifiedDate" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="19972" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Column="ContactID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Column="NameStyle" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Column="Title" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Column="FirstName" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Column="MiddleName" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Column="LastName" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Column="Suffix" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Column="EmailAddress" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Column="EmailPromotion" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Column="Phone" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Column="PasswordHash" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Column="PasswordSalt" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Column="AdditionalContactInfo" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Column="rowguid" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Column="ModifiedDate" />
</DefinedValue>
</DefinedValues>
<Object Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Index="[PK_Contact_ContactID]" IndexKind="Clustered" />
</IndexScan>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>

You can also use Pastebin or a similar site if the plan is significant larger: http://pastebin.com/mVH0TQUW

dbthj
Posting Yak Master

143 Posts

Posted - 2011-08-09 : 11:46:38

Thanks,
I'll try the paste method and see if that flies.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-09 : 11:48:30

Am I the only one that can not read the XML? Or do the forum members save that to a file and then open in SSMS?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-09 : 12:01:30

When I did Show Exec Plan XML it opened a new window for me and I copied and pasted it no problem. SSMS v10.50.1617.0

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-09 : 12:19:05

I can read the xml, but given the choice I'll rather let SSMS show me the GUI

--
Gail Shaw
SQL Server MVP

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-09 : 12:19:44

quote:
Originally posted by robvolk

When I did Show Exec Plan XML it opened a new window for me and I copied and pasted it no problem. SSMS v10.50.1617.0

No I mean, are you able to look through the plan when it's in XML format? Or do you copy/paste the above XML into your SSMS to view it in a more graphical way? I'm just curious. I for one cannot look through it in the XML format.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-09 : 12:38:48

Ahhh, yeah, I look at it in Plan Explorer.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-09 : 12:50:21

Glad I'm not alone. Gail's a super hero.