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.5" Build="13.0.4446.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
- <BatchSequence>
- <Batch>
- <Statements>
- <StmtSimple StatementCompId="8" StatementEstRows="128.734" StatementId="1" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="120" StatementSubTreeCost="14.9567" StatementText="SELECT wd.SourceCol1
 , vp.Col2
 , vp.Col3 
 FROM AppDB.MySchema.View_VP vp WITH (FORCESEEK)
 INNER JOIN #MyTemp wd ON wd.SourceCol1 = vp.Col1
 WHERE vp.Col3 > DATEADD(MONTH, @Months * -1, GETDATE())" StatementType="SELECT" QueryHash="0x46E5D8BB3AD5F421" QueryPlanHash="0xE12B0691D89E7BB0" RetrievedFromCache="true" StatementSqlHandle="0x09003E95EBFF5425141BC293F77C0C55D8590000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="1454628225" BatchSqlHandle="0x0300070081DDB35600C4AB0003A7000001000000000000000000000000000000000000000000000000000000" StatementParameterizationType="0" SecurityPolicyApplied="false">
- <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="false" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
- <QueryPlan CachedPlanSize="72" CompileTime="13" CompileCPU="13" CompileMemory="976">
- <MemoryGrantInfo SerialRequiredMemory="2048" SerialDesiredMemory="2992" />
- <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="705604" EstimatedPagesCached="2116812" EstimatedAvailableDegreeOfParallelism="8" MaxCompileMemory="213618464" />
- <TraceFlags IsCompileTime="true">
- <TraceFlag Value="3226" Scope="Global" />
- <TraceFlag Value="7412" Scope="Global" />
- <TraceFlag Value="7745" Scope="Global" />
- <TraceFlag Value="7752" Scope="Global" />
- </TraceFlags>
- <RelOp AvgRowSize="37" EstimateCPU="1.08036E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="128.734" LogicalOp="Filter" NodeId="0" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="14.9567">
- <OutputList>
- <ColumnReference Database="[P_DB]" Schema="[dbo]" Table="[SmallTable]" Alias="[pd]" Column="Col2" />
- <ColumnReference Column="Expr1003" />
- <ColumnReference Table="#MyTemp" Alias="[wd]" Column="SourceCol1" />
- </OutputList>
- <Filter StartupExpression="false">
- <RelOp AvgRowSize="37" EstimateCPU="0.0294563" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="22.5075" LogicalOp="Aggregate" NodeId="1" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="14.9567">
- <OutputList>
- <ColumnReference Database="[P_DB]" Schema="[dbo]" Table="[SmallTable]" Alias="[pd]" Column="Col2" />
- <ColumnReference Column="Expr1003" />
- <ColumnReference Table="#MyTemp" Alias="[wd]" Column="SourceCol1" />
- </OutputList>
- <MemoryFractions Input="0.0169492" Output="1" />
- <Hash>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Expr1003" />
- <ScalarOperator ScalarString="MAX([P_DB].[dbo].[LargeTable].[Col4] as [pp].[Col4])">
- <Aggregate AggType="MAX" Distinct="false">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[P_DB]" Schema="[dbo]" Table="[LargeTable]" Alias="[pp]" Column="Col4" />
- </Identifier>
- </ScalarOperator>
- </Aggregate>
- </ScalarOperator>
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Table="#MyTemp" Alias="[wd]" Column="SourceCol1" />
- <ScalarOperator ScalarString="ANY(#MyTemp.[SourceCol1] as [wd].[SourceCol1])">
- <Aggregate AggType="ANY" Distinct="false">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Table="#MyTemp" Alias="[wd]" Column="SourceCol1" />
- </Identifier>
- </ScalarOperator>
- </Aggregate>
- </ScalarOperator>
- </DefinedValue>
- </DefinedValues>
- <HashKeysBuild>
- <ColumnReference Column="Bmk1004" />
- <ColumnReference Database="[P_DB]" Schema="[dbo]" Table="[SmallTable]" Alias="[pd]" Column="Col2" />
- </HashKeysBuild>
- <BuildResidual>
- <ScalarOperator ScalarString="[Bmk1004] = [Bmk1004] AND [P_DB].[dbo].[SmallTable].[Col2] as [pd].[Col2] = [P_DB].[dbo].[SmallTable].[Col2] as [pd].[Col2]">
- <Logical Operation="AND">
- <ScalarOperator>
- <Compare CompareOp="IS">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Bmk1004" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Bmk1004" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="IS">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[P_DB]" Schema="[dbo]" Table="[SmallTable]" Alias="[pd]" Column="Col2" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[P_DB]" Schema="[dbo]" Table="[SmallTable]" Alias="[pd]" Column="Col2" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </BuildResidual>
- <RelOp AvgRowSize="45" EstimateCPU="0.0176312" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1603.93" LogicalOp="Inner Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="14.9272">
- <OutputList>
- <ColumnReference Database="[P_DB]" Schema="[dbo]" Table="[LargeTable]" Alias="[pp]" Column="Col4" />
- <ColumnReference Database="[P_DB]" Schema="[dbo]" Table="[SmallTable]" Alias="[pd]" Column="Col2" />
- <ColumnReference Column="Bmk1004" />
- <ColumnReference Table="#MyTemp" Alias="[wd]" Column="SourceCol1" />
- </OutputList>
- <NestedLoops Optimized="false" WithUnorderedPrefetch="true">
- <OuterReferences>
- <ColumnReference Database="[P_DB]" Schema="[dbo]" Table="[LargeTable]" Alias="[pp]" Column="P_Id" />
- <ColumnReference Column="Expr1009" />
- </OuterReferences>
- <RelOp AvgRowSize="51" EstimateCPU="0.0178305" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="4265.68" LogicalOp="Inner Join" NodeId="4" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="14.0646">
- <OutputList>
- <ColumnReference Database="[P_DB]" Schema="[dbo]" Table="[LargeTable]" Alias="[pp]" Column="Col4" />
- <ColumnReference Database="[P_DB]" Schema="[dbo]" Table="[LargeTable]" Alias="[pp]" Column="P_Id" />
- <ColumnReference Column="Bmk1004" />
- <ColumnReference Table="#MyTemp" Alias="[wd]" Column="SourceCol1" />
- </OutputList>
- <MemoryFractions Input="0" Output="0.949153" />
- <NestedLoops Optimized="true" WithUnorderedPrefetch="true">
- <OuterReferences>
- <ColumnReference Column="Uniq1001" />
- <ColumnReference Database="[P_DB]" Schema="[dbo]" Table="[LargeTable]" Alias="[pp]" Column="PY_Id" />
- <ColumnReference Column="Expr1008" />
- </OuterReferences>
- <RelOp AvgRowSize="66" EstimateCPU="0.0178305" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="4265.68" LogicalOp="Inner Join" NodeId="7" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0573068">
- <OutputList>
- <ColumnReference Column="Uniq1001" />
- <ColumnReference Database="[P_DB]" Schema="[dbo]" Table="[LargeTable]" Alias="[pp]" Column="PY_Id" />
- <ColumnReference Column="Bmk1004" />
- <ColumnReference Table="#MyTemp" Alias="[wd]" Column="SourceCol1" />
- </OutputList>
- <MemoryFractions Input="0" Output="0.0338983" />
- <NestedLoops Optimized="true">
- <OuterReferences>
- <ColumnReference Table="#MyTemp" Alias="[wd]" Column="SourceCol1" />
- </OuterReferences>
- <RelOp AvgRowSize="35" EstimateCPU="0.0001614" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="4" EstimatedRowsRead="4" LogicalOp="Table Scan" NodeId="9" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032864" TableCardinality="4">
- <OutputList>
- <ColumnReference Column="Bmk1004" />
- <ColumnReference Table="#MyTemp" Alias="[wd]" Column="SourceCol1" />
- </OutputList>
- <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Bmk1004" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Table="#MyTemp" Alias="[wd]" Column="SourceCol1" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[tempdb]" Schema="[dbo]" Table="[#MyTemp]" Alias="[wd]" Storage="RowStore" />
- </TableScan>
- </RelOp>
- <RelOp AvgRowSize="40" EstimateCPU="0.00133006" EstimateIO="0.0105324" EstimateRebinds="3" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1066.42" EstimatedRowsRead="1066.42" LogicalOp="Index Seek" NodeId="10" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0361898" TableCardinality="32268200">
- <OutputList>
- <ColumnReference Column="Uniq1001" />
- <ColumnReference Database="[P_DB]" Schema="[dbo]" Table="[LargeTable]" Alias="[pp]" Column="PY_Id" />
- </OutputList>
- <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="true" ForceScan="false" NoExpandHint="false" Storage="RowStore">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Uniq1001" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[P_DB]" Schema="[dbo]" Table="[LargeTable]" Alias="[pp]" Column="PY_Id" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[P_DB]" Schema="[dbo]" Table="[LargeTable]" Index="[ix_Col1]" Alias="[pp]" IndexKind="NonClustered" Storage="RowStore" />
- <SeekPredicates>
- <SeekPredicateNew>
- <SeekKeys>
- <Prefix ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[P_DB]" Schema="[dbo]" Table="[LargeTable]" Alias="[pp]" Column="Col1" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="#MyTemp.[SourceCol1] as [wd].[SourceCol1]">
- <Identifier>
- <ColumnReference Table="#MyTemp" Alias="[wd]" Column="SourceCol1" />
- </Identifier>
- </ScalarOperator>
- </RangeExpressions>
- </Prefix>
- </SeekKeys>
- </SeekPredicateNew>
- </SeekPredicates>
- </IndexScan>
- </RelOp>
- </NestedLoops>
- </RelOp>
- <RelOp AvgRowSize="23" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="4264.68" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="4218" LogicalOp="Clustered Index Seek" NodeId="12" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="13.9894" TableCardinality="32268200">
- <OutputList>
- <ColumnReference Database="[P_DB]" Schema="[dbo]" Table="[LargeTable]" Alias="[pp]" Column="Col4" />
- <ColumnReference Database="[P_DB]" Schema="[dbo]" Table="[LargeTable]" Alias="[pp]" Column="P_Id" />
- </OutputList>
- <IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="true" ForceScan="false" NoExpandHint="false" Storage="RowStore">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[P_DB]" Schema="[dbo]" Table="[LargeTable]" Alias="[pp]" Column="Col4" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[P_DB]" Schema="[dbo]" Table="[LargeTable]" Alias="[pp]" Column="P_Id" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[P_DB]" Schema="[dbo]" Table="[LargeTable]" Index="[ix_PY_Id]" Alias="[pp]" TableReferenceId="-1" IndexKind="Clustered" Storage="RowStore" />
- <SeekPredicates>
- <SeekPredicateNew>
- <SeekKeys>
- <Prefix ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[P_DB]" Schema="[dbo]" Table="[LargeTable]" Alias="[pp]" Column="PY_Id" />
- <ColumnReference Column="Uniq1001" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="[P_DB].[dbo].[LargeTable].[PY_Id] as [pp].[PY_Id]">
- <Identifier>
- <ColumnReference Database="[P_DB]" Schema="[dbo]" Table="[LargeTable]" Alias="[pp]" Column="PY_Id" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator ScalarString="[Uniq1001]">
- <Identifier>
- <ColumnReference Column="Uniq1001" />
- </Identifier>
- </ScalarOperator>
- </RangeExpressions>
- </Prefix>
- </SeekKeys>
- </SeekPredicateNew>
- </SeekPredicates>
- <Predicate>
- <ScalarOperator ScalarString="[P_DB].[dbo].[LargeTable].[Status] as [pp].[Status]=(3) OR [P_DB].[dbo].[LargeTable].[Status] as [pp].[Status]=(4)">
- <Logical Operation="OR">
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[P_DB]" Schema="[dbo]" Table="[LargeTable]" Alias="[pp]" Column="Status" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(3)" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[P_DB]" Schema="[dbo]" Table="[LargeTable]" Alias="[pp]" Column="Status" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(4)" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </Predicate>
- </IndexScan>
- </RelOp>
- </NestedLoops>
- </RelOp>
- <RelOp AvgRowSize="9" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="4070.84" EstimateRewinds="146.162" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Clustered Index Seek" NodeId="13" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.841268" TableCardinality="6349">
- <OutputList>
- <ColumnReference Database="[P_DB]" Schema="[dbo]" Table="[SmallTable]" Alias="[pd]" Column="Col2" />
- </OutputList>
- <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="true" ForceScan="false" NoExpandHint="false" Storage="RowStore">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[P_DB]" Schema="[dbo]" Table="[SmallTable]" Alias="[pd]" Column="Col2" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[P_DB]" Schema="[dbo]" Table="[SmallTable]" Index="[PK_SmallTable]" Alias="[pd]" IndexKind="Clustered" Storage="RowStore" />
- <SeekPredicates>
- <SeekPredicateNew>
- <SeekKeys>
- <Prefix ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[P_DB]" Schema="[dbo]" Table="[SmallTable]" Alias="[pd]" Column="P_Id" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="[P_DB].[dbo].[LargeTable].[P_Id] as [pp].[P_Id]">
- <Identifier>
- <ColumnReference Database="[P_DB]" Schema="[dbo]" Table="[LargeTable]" Alias="[pp]" Column="P_Id" />
- </Identifier>
- </ScalarOperator>
- </RangeExpressions>
- </Prefix>
- </SeekKeys>
- </SeekPredicateNew>
- </SeekPredicates>
- </IndexScan>
- </RelOp>
- </NestedLoops>
- </RelOp>
- </Hash>
- </RelOp>
- <Predicate>
- <ScalarOperator ScalarString="[Expr1003]>dateadd(month,[@Months]*(-1),getdate())">
- <Compare CompareOp="GT">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Expr1003" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="ConstExpr1006">
- <ScalarOperator>
- <Intrinsic FunctionName="dateadd">
- <ScalarOperator>
- <Const ConstValue="(2)" />
- </ScalarOperator>
- <ScalarOperator>
- <Arithmetic Operation="MULT">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="@Months" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(-1)" />
- </ScalarOperator>
- </Arithmetic>
- </ScalarOperator>
- <ScalarOperator>
- <Intrinsic FunctionName="getdate" />
- </ScalarOperator>
- </Intrinsic>
- </ScalarOperator>
- </ColumnReference>
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Predicate>
- </Filter>
- </RelOp>
- <ParameterList>
- <ColumnReference Column="@Months" ParameterDataType="int" ParameterCompiledValue="(6)" />
- </ParameterList>
- </QueryPlan>
- </StmtSimple>
- </Statements>
- </Batch>
- </BatchSequence>
- </ShowPlanXML>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement