X hits on this document

105 views

0 shares

0 downloads

0 comments

20 / 48

654

CHAPTER 28 PERFORMANCE TUNING

The XMLDocument results returned from SET STATISTICS XML are then copied to the next query. Note that all the single quotes (') in the XML document, have to be escaped with an additional sin- gle quote (except for the quotes used for USE PLAN):

SELECT TOP 10 Rate FROM HumanResources.EmployeePayHistory ORDER BY Rate DESC OPTION (USE PLAN '<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan Version="1.0" Build="9.00.1314.06"> <BatchSequence> <Batch> <Statements> <StmtSimple StatementText="SELECT TOP 10 Rate&#xD;&#xA;FROM HumanResources.EmployeePayHistory&#xD;&#xA;ORDER BY Rate DESC&#xD;&#xA;&#xD;" StatementId="1" StatementCompId="2" StatementType="SELECT" StatementSubTreeCost="0.019825" StatementEstRows="10" StatementOptmLevel="TRIVIAL"> <StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false" /> <QueryPlan DegreeOfParallelism="0" MemoryGrant="64" CachedPlanSize="8"> <RelOp NodeId="0" PhysicalOp="Sort" LogicalOp="TopN Sort" EstimateRows="10" EstimateIO="0.0112613" EstimateCPU="0.00419345" AvgRowSize="15" EstimatedTotalSubtreeCost="0.019825" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"> <OutputList> <ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[EmployeePayHistory]" Column="Rate" /> </OutputList> <MemoryFractions Input="0" Output="1" /> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="10" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation> <TopSort Distinct="0" Rows="10"> <OrderBy> <OrderByColumn Ascending="0"> <ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[EmployeePayHistory]" Column="Rate" /> </OrderByColumn> </OrderBy> <RelOp NodeId="1" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="316" EstimateIO="0.00386574" EstimateCPU="0.0005046" AvgRowSize="15" EstimatedTotalSubtreeCost="0.00437034" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"> <OutputList> <ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[EmployeePayHistory]" Column="Rate" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="316" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation> "

<IndexScan Ordered="0 <DefinedValues> <DefinedValue>

" ForcedIndex="0" NoExpandHint="0">

<ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[EmployeePayHistory]" Column="Rate" />

Document info
Document views105
Page views105
Page last viewedTue Dec 06 18:09:41 UTC 2016
Pages48
Paragraphs987
Words15822

Comments