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" StatementId="1" StatementText="DECLARE @days INT

Set @days = 7

" StatementType="ASSIGN" RetrievedFromCache="false" />
- </Statements>
- <Statements>
- <StmtSimple StatementCompId="2" StatementEstRows="699.164" StatementId="2" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="70" StatementSubTreeCost="5811.4" 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, -@days, getdate())" StatementType="SELECT" QueryHash="0x9E7177E7049BEC75" QueryPlanHash="0x5665D13369C4F238" 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="24" CompileTime="4" CompileCPU="4" CompileMemory="304">
- <ThreadStat Branches="1" />
- <MissingIndexes>
- <MissingIndexGroup Impact="98.6785">
- <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="0" SerialDesiredMemory="0" />
- <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="340787" EstimatedPagesCached="170393" EstimatedAvailableDegreeOfParallelism="4" />
- <RelOp AvgRowSize="49" EstimateCPU="0.0294546" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="699.164" LogicalOp="Gather Streams" NodeId="0" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="5811.4">
- <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="59" EstimateCPU="276.98" EstimateIO="5212.09" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="699.164" LogicalOp="Clustered Index Scan" NodeId="2" Parallel="true" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="5489.07" 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="CHANGE_DATE" />
- <ColumnReference Database="[myDB]" Schema="[dbo]" Table="[CHANGES]" Alias="[c]" Column="NEW_VALUE" />
- </OutputList>
- <IndexScan Ordered="false" ForcedIndex="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="CHANGE_DATE" />
- </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]" IndexKind="Clustered" Storage="RowStore" />
- <Predicate>
- <ScalarOperator ScalarString="[myDB].[dbo].[CHANGES].[CHANGE_DATE] as [c].[CHANGE_DATE]>=dateadd(day, -[@days],getdate()) AND [myDB].[dbo].[CHANGES].[CHANGE_CODE] as [c].[CHANGE_CODE]=(3.) AND CONVERT_IMPLICIT(int,[myDB].[dbo].[CHANGES].[OLD_VALUE] as [c].[OLD_VALUE],0)=(4)">
- <Logical Operation="AND">
- <ScalarOperator>
- <Compare CompareOp="GE">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[myDB]" Schema="[dbo]" Table="[CHANGES]" Alias="[c]" Column="CHANGE_DATE" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="ConstExpr1001">
- <ScalarOperator>
- <Intrinsic FunctionName="dateadd">
- <ScalarOperator>
- <Const ConstValue="(4)" />
- </ScalarOperator>
- <ScalarOperator>
- <Arithmetic Operation="MINUS">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="@days" />
- </Identifier>
- </ScalarOperator>
- </Arithmetic>
- </ScalarOperator>
- <ScalarOperator>
- <Intrinsic FunctionName="getdate" />
- </ScalarOperator>
- </Intrinsic>
- </ScalarOperator>
- </ColumnReference>
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[myDB]" Schema="[dbo]" Table="[CHANGES]" Alias="[c]" Column="CHANGE_CODE" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(3.)" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <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>
- </Logical>
- </ScalarOperator>
- </Predicate>
- </IndexScan>
- </RelOp>
- </Parallelism>
- </RelOp>
- </QueryPlan>
- </StmtSimple>
- </Statements>
- </Batch>
- </BatchSequence>
- </ShowPlanXML>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement