Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <?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.2" Build="11.0.5058.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
- <BatchSequence>
- <Batch>
- <Statements>
- <StmtSimple StatementCompId="1" StatementEstRows="41901.8" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="49898600" StatementText="DELETE [ETLHeaders] WHERE [ETLHeaderID]<@1" StatementType="DELETE" QueryHash="0x309B2BACD973C818" QueryPlanHash="0x55699CDA6320252F" RetrievedFromCache="false">
- <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" MemoryGrant="11806520" CachedPlanSize="80" CompileTime="4" CompileCPU="4" CompileMemory="400">
- <MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="53895282848" RequiredMemory="512" DesiredMemory="53895282848" RequestedMemory="11806520" GrantWaitTime="0" GrantedMemory="11806520" MaxUsedMemory="28136" />
- <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="786432" EstimatedPagesCached="393216" EstimatedAvailableDegreeOfParallelism="4" />
- <RelOp AvgRowSize="9" EstimateCPU="8888.67" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="8888590000" LogicalOp="Sequence" NodeId="1" Parallel="false" PhysicalOp="Sequence" EstimatedTotalSubtreeCost="49898600">
- <OutputList />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="361190" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Sequence>
- <RelOp AvgRowSize="9" EstimateCPU="0.0419018" EstimateIO="2.60807" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="41901.8" LogicalOp="Delete" NodeId="2" Parallel="false" PhysicalOp="Index Delete" EstimatedTotalSubtreeCost="11.653">
- <OutputList />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="39157" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Update WithOrderedPrefetch="true" DMLRequestSort="true">
- <Object Database="[FileSentinel]" Schema="[dbo]" Table="[ETLHeaders]" Index="[IX_ETLHeaders_DateOfSale_ProfitCentreID_incl]" IndexKind="NonClustered" />
- <RelOp AvgRowSize="19" EstimateCPU="2.95326" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="41901.8" LogicalOp="Sort" NodeId="4" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="9.00303">
- <OutputList>
- <ColumnReference Database="[FileSentinel]" Schema="[dbo]" Table="[ETLHeaders]" Column="ETLHeaderID" />
- <ColumnReference Database="[FileSentinel]" Schema="[dbo]" Table="[ETLHeaders]" Column="ProfitCentreID" />
- <ColumnReference Database="[FileSentinel]" Schema="[dbo]" Table="[ETLHeaders]" Column="DateOfSale" />
- </OutputList>
- <MemoryFractions Input="1" Output="1" />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="39157" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Sort Distinct="false">
- <OrderBy>
- <OrderByColumn Ascending="true">
- <ColumnReference Database="[FileSentinel]" Schema="[dbo]" Table="[ETLHeaders]" Column="DateOfSale" />
- </OrderByColumn>
- <OrderByColumn Ascending="true">
- <ColumnReference Database="[FileSentinel]" Schema="[dbo]" Table="[ETLHeaders]" Column="ProfitCentreID" />
- </OrderByColumn>
- <OrderByColumn Ascending="true">
- <ColumnReference Database="[FileSentinel]" Schema="[dbo]" Table="[ETLHeaders]" Column="ETLHeaderID" />
- </OrderByColumn>
- </OrderBy>
- <RelOp AvgRowSize="19" EstimateCPU="0.00506592" EstimateIO="0.00437869" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="41901.8" LogicalOp="Eager Spool" NodeId="5" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="6.03851">
- <OutputList>
- <ColumnReference Database="[FileSentinel]" Schema="[dbo]" Table="[ETLHeaders]" Column="ETLHeaderID" />
- <ColumnReference Database="[FileSentinel]" Schema="[dbo]" Table="[ETLHeaders]" Column="ProfitCentreID" />
- <ColumnReference Database="[FileSentinel]" Schema="[dbo]" Table="[ETLHeaders]" Column="DateOfSale" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="39157" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Spool>
- <RelOp AvgRowSize="19" EstimateCPU="0.125705" EstimateIO="16.323" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="41901.8" LogicalOp="Delete" NodeId="6" Parallel="false" PhysicalOp="Clustered Index Delete" EstimatedTotalSubtreeCost="18.0719">
- <OutputList>
- <ColumnReference Database="[FileSentinel]" Schema="[dbo]" Table="[ETLHeaders]" Column="ETLHeaderID" />
- <ColumnReference Database="[FileSentinel]" Schema="[dbo]" Table="[ETLHeaders]" Column="ProfitCentreID" />
- <ColumnReference Database="[FileSentinel]" Schema="[dbo]" Table="[ETLHeaders]" Column="DateOfSale" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="39157" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <SimpleUpdate DMLRequestSort="true">
- <Object Database="[FileSentinel]" Schema="[dbo]" Table="[ETLHeaders]" Index="[PK_ETLHeaders]" IndexKind="Clustered" />
- <Object Database="[FileSentinel]" Schema="[dbo]" Table="[ETLHeaders]" Index="[IX_ETLHeaders_InstructionSetID_incl]" IndexKind="NonClustered" />
- <Object Database="[FileSentinel]" Schema="[dbo]" Table="[ETLHeaders]" Index="[IX_ETLHeaders_DateAdded]" IndexKind="NonClustered" />
- <SeekPredicateNew>
- <SeekKeys>
- <EndRange ScanType="LT">
- <RangeColumns>
- <ColumnReference Database="[FileSentinel]" Schema="[dbo]" Table="[ETLHeaders]" Column="ETLHeaderID" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="(32465870)">
- <Const ConstValue="(32465870)" />
- </ScalarOperator>
- </RangeExpressions>
- </EndRange>
- </SeekKeys>
- </SeekPredicateNew>
- </SimpleUpdate>
- </RelOp>
- </Spool>
- </RelOp>
- </Sort>
- </RelOp>
- </Update>
- </RelOp>
- <RelOp AvgRowSize="9" EstimateCPU="0.0419018" EstimateIO="2.0352" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="41901.8" LogicalOp="Delete" NodeId="7" Parallel="false" PhysicalOp="Index Delete" EstimatedTotalSubtreeCost="11.0801">
- <OutputList />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="39157" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Update WithOrderedPrefetch="true" DMLRequestSort="true">
- <Object Database="[FileSentinel]" Schema="[dbo]" Table="[ETLHeaders]" Index="[IX_ETLHeaders_ProfitCentreID_DateOfSale_incl]" IndexKind="NonClustered" />
- <RelOp AvgRowSize="19" EstimateCPU="2.95326" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="41901.8" LogicalOp="Sort" NodeId="9" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="9.00303">
- <OutputList>
- <ColumnReference Database="[FileSentinel]" Schema="[dbo]" Table="[ETLHeaders]" Column="ETLHeaderID" />
- <ColumnReference Database="[FileSentinel]" Schema="[dbo]" Table="[ETLHeaders]" Column="ProfitCentreID" />
- <ColumnReference Database="[FileSentinel]" Schema="[dbo]" Table="[ETLHeaders]" Column="DateOfSale" />
- </OutputList>
- <MemoryFractions Input="1" Output="1" />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="39157" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Sort Distinct="false">
- <OrderBy>
- <OrderByColumn Ascending="true">
- <ColumnReference Database="[FileSentinel]" Schema="[dbo]" Table="[ETLHeaders]" Column="ProfitCentreID" />
- </OrderByColumn>
- <OrderByColumn Ascending="true">
- <ColumnReference Database="[FileSentinel]" Schema="[dbo]" Table="[ETLHeaders]" Column="DateOfSale" />
- </OrderByColumn>
- <OrderByColumn Ascending="true">
- <ColumnReference Database="[FileSentinel]" Schema="[dbo]" Table="[ETLHeaders]" Column="ETLHeaderID" />
- </OrderByColumn>
- </OrderBy>
- <RelOp AvgRowSize="19" EstimateCPU="0.00506592" EstimateIO="0.00437869" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="41901.8" LogicalOp="Eager Spool" NodeId="10" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="6.03851">
- <OutputList>
- <ColumnReference Database="[FileSentinel]" Schema="[dbo]" Table="[ETLHeaders]" Column="ETLHeaderID" />
- <ColumnReference Database="[FileSentinel]" Schema="[dbo]" Table="[ETLHeaders]" Column="ProfitCentreID" />
- <ColumnReference Database="[FileSentinel]" Schema="[dbo]" Table="[ETLHeaders]" Column="DateOfSale" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="39157" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Spool PrimaryNodeId="5" />
- </RelOp>
- </Sort>
- </RelOp>
- </Update>
- </RelOp>
- <RelOp AvgRowSize="9" EstimateCPU="8888.59" EstimateIO="293733" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="8888590000" LogicalOp="Delete" NodeId="11" Parallel="false" PhysicalOp="Index Delete" EstimatedTotalSubtreeCost="49889700">
- <OutputList />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="361190" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Update WithOrderedPrefetch="true" DMLRequestSort="true">
- <Object Database="[FileSentinel]" Schema="[sys]" Table="[xml_index_nodes_1077578877_256000]" Index="[XML_IX_ETLHeaders_Property]" IndexKind="SecondaryXML" />
- <RelOp AvgRowSize="4927" EstimateCPU="671723" EstimateIO="48366700" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="8888590000" LogicalOp="Sort" NodeId="13" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="49587100">
- <OutputList>
- <ColumnReference Database="[FileSentinel]" Schema="[sys]" Table="[xml_index_nodes_1077578877_256000]" Column="id" />
- <ColumnReference Database="[FileSentinel]" Schema="[sys]" Table="[xml_index_nodes_1077578877_256000]" Column="value" />
- <ColumnReference Database="[FileSentinel]" Schema="[sys]" Table="[xml_index_nodes_1077578877_256000]" Column="hid" />
- <ColumnReference Database="[FileSentinel]" Schema="[sys]" Table="[xml_index_nodes_1077578877_256000]" Column="pk1" />
- </OutputList>
- <MemoryFractions Input="1" Output="1" />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="361190" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Sort Distinct="false">
- <OrderBy>
- <OrderByColumn Ascending="true">
- <ColumnReference Database="[FileSentinel]" Schema="[sys]" Table="[xml_index_nodes_1077578877_256000]" Column="pk1" />
- </OrderByColumn>
- <OrderByColumn Ascending="true">
- <ColumnReference Database="[FileSentinel]" Schema="[sys]" Table="[xml_index_nodes_1077578877_256000]" Column="hid" />
- </OrderByColumn>
- <OrderByColumn Ascending="true">
- <ColumnReference Database="[FileSentinel]" Schema="[sys]" Table="[xml_index_nodes_1077578877_256000]" Column="value" />
- </OrderByColumn>
- <OrderByColumn Ascending="true">
- <ColumnReference Database="[FileSentinel]" Schema="[sys]" Table="[xml_index_nodes_1077578877_256000]" Column="id" />
- </OrderByColumn>
- </OrderBy>
- <RelOp AvgRowSize="4927" EstimateCPU="8888.59" EstimateIO="452560" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="8888590000" LogicalOp="Delete" NodeId="14" Parallel="false" PhysicalOp="Clustered Index Delete" EstimatedTotalSubtreeCost="548633">
- <OutputList>
- <ColumnReference Database="[FileSentinel]" Schema="[sys]" Table="[xml_index_nodes_1077578877_256000]" Column="id" />
- <ColumnReference Database="[FileSentinel]" Schema="[sys]" Table="[xml_index_nodes_1077578877_256000]" Column="value" />
- <ColumnReference Database="[FileSentinel]" Schema="[sys]" Table="[xml_index_nodes_1077578877_256000]" Column="hid" />
- <ColumnReference Database="[FileSentinel]" Schema="[sys]" Table="[xml_index_nodes_1077578877_256000]" Column="pk1" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="361190" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Update DMLRequestSort="true">
- <Object Database="[FileSentinel]" Schema="[sys]" Table="[xml_index_nodes_1077578877_256000]" Index="[XML_IX_ETLHeaders]" IndexKind="PrimaryXML" />
- <RelOp AvgRowSize="469" EstimateCPU="37154.3" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="8888590000" LogicalOp="Inner Join" NodeId="15" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="87184.6">
- <OutputList>
- <ColumnReference Database="[FileSentinel]" Schema="[dbo]" Table="[ETLHeaders]" Column="ETLHeaderID" />
- <ColumnReference Database="[FileSentinel]" Schema="[sys]" Table="[xml_index_nodes_1077578877_256000]" Column="pk1" />
- <ColumnReference Database="[FileSentinel]" Schema="[sys]" Table="[xml_index_nodes_1077578877_256000]" Column="id" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="361190" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <NestedLoops Optimized="false" WithOrderedPrefetch="true">
- <OuterReferences>
- <ColumnReference Database="[FileSentinel]" Schema="[dbo]" Table="[ETLHeaders]" Column="ETLHeaderID" />
- <ColumnReference Column="Expr1016" />
- </OuterReferences>
- <RelOp AvgRowSize="19" EstimateCPU="0.00506592" EstimateIO="0.00437869" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="41901.8" LogicalOp="Eager Spool" NodeId="18" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="6.03851">
- <OutputList>
- <ColumnReference Database="[FileSentinel]" Schema="[dbo]" Table="[ETLHeaders]" Column="ETLHeaderID" />
- <ColumnReference Database="[FileSentinel]" Schema="[dbo]" Table="[ETLHeaders]" Column="ProfitCentreID" />
- <ColumnReference Database="[FileSentinel]" Schema="[dbo]" Table="[ETLHeaders]" Column="DateOfSale" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="39157" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Spool PrimaryNodeId="5" />
- </RelOp>
- <RelOp AvgRowSize="465" EstimateCPU="0.233499" EstimateIO="1.18683" EstimateRebinds="41900.8" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="212129" LogicalOp="Clustered Index Seek" NodeId="22" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="50024.2" TableCardinality="12651300">
- <OutputList>
- <ColumnReference Database="[FileSentinel]" Schema="[sys]" Table="[xml_index_nodes_1077578877_256000]" Column="pk1" />
- <ColumnReference Database="[FileSentinel]" Schema="[sys]" Table="[xml_index_nodes_1077578877_256000]" Column="id" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="361190" ActualEndOfScans="39157" ActualExecutions="39157" />
- </RunTimeInformation>
- <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[FileSentinel]" Schema="[sys]" Table="[xml_index_nodes_1077578877_256000]" Column="pk1" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[FileSentinel]" Schema="[sys]" Table="[xml_index_nodes_1077578877_256000]" Column="id" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[FileSentinel]" Schema="[sys]" Table="[xml_index_nodes_1077578877_256000]" Index="[XML_IX_ETLHeaders]" IndexKind="PrimaryXML" />
- <SeekPredicates>
- <SeekPredicateNew>
- <SeekKeys>
- <Prefix ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[FileSentinel]" Schema="[sys]" Table="[xml_index_nodes_1077578877_256000]" Column="pk1" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="[FileSentinel].[dbo].[ETLHeaders].[ETLHeaderID]">
- <Identifier>
- <ColumnReference Database="[FileSentinel]" Schema="[dbo]" Table="[ETLHeaders]" Column="ETLHeaderID" />
- </Identifier>
- </ScalarOperator>
- </RangeExpressions>
- </Prefix>
- </SeekKeys>
- </SeekPredicateNew>
- </SeekPredicates>
- </IndexScan>
- </RelOp>
- </NestedLoops>
- </RelOp>
- </Update>
- </RelOp>
- </Sort>
- </RelOp>
- </Update>
- </RelOp>
- </Sequence>
- </RelOp>
- <ParameterList>
- <ColumnReference Column="@1" ParameterCompiledValue="(32465870)" ParameterRuntimeValue="(32465870)" />
- </ParameterList>
- </QueryPlan>
- </StmtSimple>
- </Statements>
- </Batch>
- </BatchSequence>
- </ShowPlanXML>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement