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="12.0.2269.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
- <BatchSequence>
- <Batch>
- <Statements>
- <StmtSimple StatementCompId="1" StatementEstRows="9.66387" StatementId="1" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="70" StatementSubTreeCost="2706.98" StatementText="select c.DEBT_KEY
 ,c.new_value
 ,c.CHANGE_DATE
from changes c with (nolock)
where c.CHANGE_CODE = 3 --status
and c.old_value = 4 --SIF
and c.CHANGE_DATE >= dateadd(dd, -23, getdate())" StatementType="SELECT" QueryHash="0xBD0B028D8B6A7743" QueryPlanHash="0x9C19DDED707BCAEE" 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 CachedPlanSize="32" CompileTime="4" CompileCPU="4" CompileMemory="312">
- <ThreadStat Branches="1" />
- <MissingIndexes>
- <MissingIndexGroup Impact="97.1642">
- <MissingIndex Database="[myDB]" Schema="[dbo]" Table="[CHANGES]">
- <ColumnGroup Usage="EQUALITY">
- <Column Name="[CHANGE_CODE]" ColumnId="2" />
- </ColumnGroup>
- <ColumnGroup Usage="INEQUALITY">
- <Column Name="[CHANGE_DATE]" ColumnId="6" />
- </ColumnGroup>
- <ColumnGroup Usage="INCLUDE">
- <Column Name="[DEBT_KEY]" ColumnId="4" />
- <Column Name="[OLD_VALUE]" ColumnId="7" />
- <Column Name="[NEW_VALUE]" ColumnId="8" />
- </ColumnGroup>
- </MissingIndex>
- </MissingIndexGroup>
- </MissingIndexes>
- <Warnings>
- <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(int,[c].[OLD_VALUE],0)" />
- <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(int,[c].[OLD_VALUE],0)=(4)" />
- </Warnings>
- <MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="64136" />
- <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="340787" EstimatedPagesCached="170393" EstimatedAvailableDegreeOfParallelism="4" />
- <RelOp AvgRowSize="49" EstimateCPU="0.0285132" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="9.66387" LogicalOp="Gather Streams" NodeId="1" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="2706.98">
- <OutputList>
- <ColumnReference Database="[myDB]" Schema="[dbo]" Table="[CHANGES]" Alias="[c]" Column="DEBT_KEY" />
- <ColumnReference Database="[myDB]" Schema="[dbo]" Table="[CHANGES]" Alias="[c]" Column="CHANGE_DATE" />
- <ColumnReference Database="[myDB]" Schema="[dbo]" Table="[CHANGES]" Alias="[c]" Column="NEW_VALUE" />
- </OutputList>
- <Parallelism>
- <RelOp AvgRowSize="54" EstimateCPU="0.905343" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="866357" LogicalOp="Inner Join" NodeId="2" Parallel="true" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="2706.82">
- <OutputList>
- <ColumnReference Database="[myDB]" Schema="[dbo]" Table="[CHANGES]" Alias="[c]" Column="DEBT_KEY" />
- <ColumnReference Database="[myDB]" Schema="[dbo]" Table="[CHANGES]" Alias="[c]" Column="CHANGE_DATE" />
- <ColumnReference Database="[myDB]" Schema="[dbo]" Table="[CHANGES]" Alias="[c]" Column="NEW_VALUE" />
- </OutputList>
- <NestedLoops Optimized="false" WithUnorderedPrefetch="true">
- <OuterReferences>
- <ColumnReference Database="[myDB]" Schema="[dbo]" Table="[CHANGES]" Alias="[c]" Column="CHANGE_KEY" />
- <ColumnReference Column="Expr1003" />
- </OuterReferences>
- <RelOp AvgRowSize="20" EstimateCPU="19.6091" EstimateIO="0.00281532" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="866357" LogicalOp="Sort" NodeId="4" Parallel="true" PhysicalOp="Sort" EstimatedTotalSubtreeCost="21.6237">
- <OutputList>
- <ColumnReference Database="[myDB]" Schema="[dbo]" Table="[CHANGES]" Alias="[c]" Column="CHANGE_KEY" />
- <ColumnReference Database="[myDB]" Schema="[dbo]" Table="[CHANGES]" Alias="[c]" Column="CHANGE_DATE" />
- </OutputList>
- <MemoryFractions Input="1" Output="1" />
- <Sort Distinct="false">
- <OrderBy>
- <OrderByColumn Ascending="true">
- <ColumnReference Database="[myDB]" Schema="[dbo]" Table="[CHANGES]" Alias="[c]" Column="CHANGE_KEY" />
- </OrderByColumn>
- </OrderBy>
- <RelOp AvgRowSize="20" EstimateCPU="0.238287" EstimateIO="1.7735" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="866357" LogicalOp="Index Seek" NodeId="5" Parallel="true" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="2.01178" TableCardinality="1007200000">
- <OutputList>
- <ColumnReference Database="[myDB]" Schema="[dbo]" Table="[CHANGES]" Alias="[c]" Column="CHANGE_KEY" />
- <ColumnReference Database="[myDB]" Schema="[dbo]" Table="[CHANGES]" Alias="[c]" Column="CHANGE_DATE" />
- </OutputList>
- <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[myDB]" Schema="[dbo]" Table="[CHANGES]" Alias="[c]" Column="CHANGE_KEY" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[myDB]" Schema="[dbo]" Table="[CHANGES]" Alias="[c]" Column="CHANGE_DATE" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[myDB]" Schema="[dbo]" Table="[CHANGES]" Index="[IDX_CCODE_CHANGES]" Alias="[c]" IndexKind="NonClustered" Storage="RowStore" />
- <SeekPredicates>
- <SeekPredicateNew>
- <SeekKeys>
- <Prefix ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[myDB]" Schema="[dbo]" Table="[CHANGES]" Alias="[c]" Column="CHANGE_CODE" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="(3.)">
- <Const ConstValue="(3.)" />
- </ScalarOperator>
- </RangeExpressions>
- </Prefix>
- <StartRange ScanType="GE">
- <RangeColumns>
- <ColumnReference Database="[myDB]" Schema="[dbo]" Table="[CHANGES]" Alias="[c]" Column="CHANGE_DATE" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="dateadd(day,(-23),getdate())">
- <Identifier>
- <ColumnReference Column="ConstExpr1001">
- <ScalarOperator>
- <Intrinsic FunctionName="dateadd">
- <ScalarOperator>
- <Const ConstValue="(4)" />
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(-23)" />
- </ScalarOperator>
- <ScalarOperator>
- <Intrinsic FunctionName="getdate" />
- </ScalarOperator>
- </Intrinsic>
- </ScalarOperator>
- </ColumnReference>
- </Identifier>
- </ScalarOperator>
- </RangeExpressions>
- </StartRange>
- </SeekKeys>
- </SeekPredicateNew>
- </SeekPredicates>
- </IndexScan>
- </RelOp>
- </Sort>
- </RelOp>
- <RelOp AvgRowSize="68" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="866356" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="9.66387" LogicalOp="Clustered Index Seek" NodeId="7" Parallel="true" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="2684.3" TableCardinality="1007200000">
- <OutputList>
- <ColumnReference Database="[myDB]" Schema="[dbo]" Table="[CHANGES]" Alias="[c]" Column="DEBT_KEY" />
- <ColumnReference Database="[myDB]" Schema="[dbo]" Table="[CHANGES]" Alias="[c]" Column="NEW_VALUE" />
- </OutputList>
- <IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[myDB]" Schema="[dbo]" Table="[CHANGES]" Alias="[c]" Column="DEBT_KEY" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[myDB]" Schema="[dbo]" Table="[CHANGES]" Alias="[c]" Column="NEW_VALUE" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[myDB]" Schema="[dbo]" Table="[CHANGES]" Index="[PK_CHANGES]" Alias="[c]" TableReferenceId="-1" IndexKind="Clustered" Storage="RowStore" />
- <SeekPredicates>
- <SeekPredicateNew>
- <SeekKeys>
- <Prefix ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[myDB]" Schema="[dbo]" Table="[CHANGES]" Alias="[c]" Column="CHANGE_KEY" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="[myDB].[dbo].[CHANGES].[CHANGE_KEY] as [c].[CHANGE_KEY]">
- <Identifier>
- <ColumnReference Database="[myDB]" Schema="[dbo]" Table="[CHANGES]" Alias="[c]" Column="CHANGE_KEY" />
- </Identifier>
- </ScalarOperator>
- </RangeExpressions>
- </Prefix>
- </SeekKeys>
- </SeekPredicateNew>
- </SeekPredicates>
- <Predicate>
- <ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[myDB].[dbo].[CHANGES].[OLD_VALUE] as [c].[OLD_VALUE],0)=(4)">
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Convert DataType="int" Style="0" Implicit="true">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[myDB]" Schema="[dbo]" Table="[CHANGES]" Alias="[c]" Column="OLD_VALUE" />
- </Identifier>
- </ScalarOperator>
- </Convert>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(4)" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Predicate>
- </IndexScan>
- </RelOp>
- </NestedLoops>
- </RelOp>
- </Parallelism>
- </RelOp>
- </QueryPlan>
- </StmtSimple>
- </Statements>
- </Batch>
- </BatchSequence>
- </ShowPlanXML>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement