Tuning T-SQL - Professional SQL Server 2005 Performance Tuning - page 259

< QueryPlan CachedPlanSize="9" >
< RelOp NodeId="0" PhysicalOp="Index Scan" LogicalOp="Index Scan"
EstimateRows="121317" EstimateIO="0.169792" EstimateCPU="0.133606"
AvgRowSize="11" EstimatedTotalSubtreeCost="0.303397" Parallel="0"
EstimateRebinds="0" EstimateRewinds="0" >
< OutputList >
< ColumnReference Database="[AdventureWorks]" Schema="[Sales]"
Table="[SalesOrderDetail]" Column="ProductID" / >
< /OutputList >
< IndexScan Ordered="0" ForcedIndex="0" NoExpandHint="0" >
< DefinedValues >
< DefinedValue >
< ColumnReference Database="[AdventureWorks]" Schema="[Sales]"
Table="[SalesOrderDetail]" Column="ProductID" / >
< /DefinedValue >
< /DefinedValues >
< Object Database="[AdventureWorks]" Schema="[Sales]"
Table="[SalesOrderDetail]" Index="[IX_SalesOrderDetail
_ProductID]" / >
< /IndexScan >
< /RelOp >
< /QueryPlan >
< /StmtSimple >
< /Statements >
< /Batch >
< /BatchSequence >
< /ShowPlanXML >
Showplan_Text
When this option is enabled, it returns the operation for each step in the query. The previous query will
yield the following output:
|--Index Scan(OBJECT:([AdventureWorks].[Sales].[SalesOrderDetail]
.[IX_SalesOrderDetail_ProductID]))
Note that this only shows the operation of each step. In order to get more information use the
SHOWPLAN_ALL option.
Showplan_All
In addition to the operation step that SHOWPLAN_TEXT produces, this option also produces additional
columns, such as Estimated IO, Estimated CPU, and Estimated Rowcount. The results of this are too large
to include in this text. However, the results from this are easily copied into Excel. There you sum up the
EstimatedIO and EstimatedCPU to quickly see which seems to be problematic. Also, you can easily sort
the results to find the operation that is taking the most I/O or CPU.
Statistics XML
When this option is enabled SQL Server returns the plan information for a query in a well-formed XML
document. The difference between this option and SHOWPLAN_XML is that this option actually exe-
cutes the statements. Using our previous example, the following results are generated when this option
is enabled.