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.571" Build="16.0.5168.212" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
- <BatchSequence>
- <Batch>
- <Statements>
- <StmtSimple StatementCompId="16" StatementEstRows="49.8658" StatementId="1" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="130" StatementSubTreeCost="101.145" StatementText="INSERT INTO #TMPDATA ([Id])
 
 SELECT TOP(@BatchSize) MAX([Id])
 FROM [Posting].[TrackingItems]
 WHERE [IsReadyToSentToMoRe] = 1
 AND [IsSentToMoRe] = 0
 AND COALESCE (ResponseState,PostingState) IN (SELECT value FROM STRING_SPLIT(@EntityStates, ','))
 GROUP BY [ItemId]
 , PostingState" StatementType="INSERT" QueryHash="0xFDD1983BF434267E" QueryPlanHash="0x0E48CBD101A69A22" RetrievedFromCache="true" StatementSqlHandle="0x09006B3325E1E6CE65ED6AA88E8298DAA6630000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="10" ParentObjectId="1607676775" BatchSqlHandle="0x030007006733D35FF3090C01FFAF000001000000000000000000000000000000000000000000000000000000" StatementParameterizationType="0" SecurityPolicyApplied="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="48" CompileTime="85" CompileCPU="27" CompileMemory="1144">
- <ThreadStat Branches="2" />
- <MissingIndexes>
- <MissingIndexGroup Impact="94.1844">
- <MissingIndex Database="[LBG_CPDB]" Schema="[Posting]" Table="[TrackingItems]">
- <ColumnGroup Usage="EQUALITY">
- <Column Name="[IsReadyToSentToMoRe]" ColumnId="43" />
- <Column Name="[IsSentToMoRe]" ColumnId="44" />
- </ColumnGroup>
- <ColumnGroup Usage="INCLUDE">
- <Column Name="[ItemId]" ColumnId="4" />
- <Column Name="[PostingState]" ColumnId="7" />
- <Column Name="[ResponseState]" ColumnId="8" />
- </ColumnGroup>
- </MissingIndex>
- </MissingIndexGroup>
- </MissingIndexes>
- <Warnings>
- <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(int,STRING_SPLIT.[value],0)" />
- </Warnings>
- <MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="544" GrantedMemory="0" MaxUsedMemory="0" />
- <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="7829359" EstimatedPagesCached="2936009" EstimatedAvailableDegreeOfParallelism="3" MaxCompileMemory="23775288" />
- <OptimizerStatsUsage>
- <StatisticsInfo Database="[060bd81c-596a-432c-9544-bb3bca432de8]" Schema="[Posting]" Table="[TrackingItems]" Statistics="[_WA_Sys_00000008_4DB4832C]" ModificationCount="40249" SamplingPercent="3.06762" LastUpdate="2023-05-10T17:34:53.47" />
- <StatisticsInfo Database="[060bd81c-596a-432c-9544-bb3bca432de8]" Schema="[Posting]" Table="[TrackingItems]" Statistics="[_WA_Sys_0000002B_4DB4832C]" ModificationCount="0" SamplingPercent="2.91967" LastUpdate="2023-05-11T16:15:59.56" />
- <StatisticsInfo Database="[060bd81c-596a-432c-9544-bb3bca432de8]" Schema="[Posting]" Table="[TrackingItems]" Statistics="[_WA_Sys_00000007_4DB4832C]" ModificationCount="32017" SamplingPercent="3.03969" LastUpdate="2023-05-10T17:32:53.47" />
- <StatisticsInfo Database="[060bd81c-596a-432c-9544-bb3bca432de8]" Schema="[Posting]" Table="[TrackingItems]" Statistics="[_WA_Sys_0000002C_4DB4832C]" ModificationCount="16820" SamplingPercent="3.03902" LastUpdate="2023-05-10T17:52:53.63" />
- <StatisticsInfo Database="[060bd81c-596a-432c-9544-bb3bca432de8]" Schema="[Posting]" Table="[TrackingItems]" Statistics="[IX_TrackingItems_ItemId_TriggeringState]" ModificationCount="30346" SamplingPercent="2.94453" LastUpdate="2023-05-10T17:25:53.52" />
- </OptimizerStatsUsage>
- <RelOp AvgRowSize="9" EstimateCPU="4.98658E-05" EstimateIO="0.01" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="49.8658" LogicalOp="Insert" NodeId="0" Parallel="false" PhysicalOp="Table Insert" EstimatedTotalSubtreeCost="101.145">
- <OutputList />
- <Update DMLRequestSort="false">
- <Object Database="[tempdb]" Schema="[dbo]" Table="[#TMPDATA]" Storage="RowStore" />
- <SetPredicate>
- <ScalarOperator ScalarString="[#TMPDATA].[Id] = [Expr1006],[#TMPDATA].[RowNumber] = [Expr1007]">
- <ScalarExpressionList>
- <ScalarOperator>
- <MultipleAssign>
- <Assign>
- <ColumnReference Table="[#TMPDATA]" Column="Id" />
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Expr1006" />
- </Identifier>
- </ScalarOperator>
- </Assign>
- <Assign>
- <ColumnReference Table="[#TMPDATA]" Column="RowNumber" />
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Expr1007" />
- </Identifier>
- </ScalarOperator>
- </Assign>
- </MultipleAssign>
- </ScalarOperator>
- </ScalarExpressionList>
- </ScalarOperator>
- </SetPredicate>
- <RelOp AvgRowSize="19" EstimateCPU="4.98658E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="49.8658" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="101.135">
- <OutputList>
- <ColumnReference Column="Expr1006" />
- <ColumnReference Column="Expr1007" />
- </OutputList>
- <ComputeScalar ComputeSequence="true">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Expr1007" />
- <ScalarOperator ScalarString="getidentity((-1165403949),(2),N'#TMPDATA')">
- <Intrinsic FunctionName="getidentity">
- <ScalarOperator>
- <Const ConstValue="(-1165403949)" />
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(2)" />
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="N'#TMPDATA'" />
- </ScalarOperator>
- </Intrinsic>
- </ScalarOperator>
- </DefinedValue>
- </DefinedValues>
- <RelOp AvgRowSize="15" EstimateCPU="4.98658E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="49.8658" LogicalOp="Top" NodeId="2" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="101.135">
- <OutputList>
- <ColumnReference Column="Expr1006" />
- </OutputList>
- <Top RowCount="false" IsPercent="false" WithTies="false">
- <TopExpression>
- <ScalarOperator ScalarString="CONVERT_IMPLICIT(bigint,[@BatchSize],0)">
- <Identifier>
- <ColumnReference Column="ConstExpr1008">
- <ScalarOperator>
- <Convert DataType="bigint" Style="0" Implicit="true">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="@BatchSize" />
- </Identifier>
- </ScalarOperator>
- </Convert>
- </ScalarOperator>
- </ColumnReference>
- </Identifier>
- </ScalarOperator>
- </TopExpression>
- <RelOp AvgRowSize="15" EstimateCPU="9.89114E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="49.8658" LogicalOp="Aggregate" NodeId="3" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="101.135">
- <OutputList>
- <ColumnReference Column="Expr1006" />
- </OutputList>
- <StreamAggregate>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Expr1006" />
- <ScalarOperator ScalarString="MAX([LBG_CPDB].[Posting].[TrackingItems].[Id])">
- <Aggregate AggType="MAX" Distinct="false">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[LBG_CPDB]" Schema="[Posting]" Table="[TrackingItems]" Column="Id" />
- </Identifier>
- </ScalarOperator>
- </Aggregate>
- </ScalarOperator>
- </DefinedValue>
- </DefinedValues>
- <GroupBy>
- <ColumnReference Database="[LBG_CPDB]" Schema="[Posting]" Table="[TrackingItems]" Column="PostingState" />
- <ColumnReference Database="[LBG_CPDB]" Schema="[Posting]" Table="[TrackingItems]" Column="ItemId" />
- </GroupBy>
- <RelOp AvgRowSize="48" EstimateCPU="0.00143607" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="123.297" LogicalOp="Sort" NodeId="4" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="101.135">
- <OutputList>
- <ColumnReference Database="[LBG_CPDB]" Schema="[Posting]" Table="[TrackingItems]" Column="Id" />
- <ColumnReference Database="[LBG_CPDB]" Schema="[Posting]" Table="[TrackingItems]" Column="ItemId" />
- <ColumnReference Database="[LBG_CPDB]" Schema="[Posting]" Table="[TrackingItems]" Column="PostingState" />
- </OutputList>
- <MemoryFractions Input="1" Output="1" />
- <Sort Distinct="false">
- <OrderBy>
- <OrderByColumn Ascending="true">
- <ColumnReference Database="[LBG_CPDB]" Schema="[Posting]" Table="[TrackingItems]" Column="PostingState" />
- </OrderByColumn>
- <OrderByColumn Ascending="true">
- <ColumnReference Database="[LBG_CPDB]" Schema="[Posting]" Table="[TrackingItems]" Column="ItemId" />
- </OrderByColumn>
- </OrderBy>
- <RelOp AvgRowSize="48" EstimateCPU="0.0287216" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="123.297" LogicalOp="Gather Streams" NodeId="5" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="101.122">
- <OutputList>
- <ColumnReference Database="[LBG_CPDB]" Schema="[Posting]" Table="[TrackingItems]" Column="Id" />
- <ColumnReference Database="[LBG_CPDB]" Schema="[Posting]" Table="[TrackingItems]" Column="ItemId" />
- <ColumnReference Database="[LBG_CPDB]" Schema="[Posting]" Table="[TrackingItems]" Column="PostingState" />
- </OutputList>
- <Parallelism>
- <RelOp AvgRowSize="48" EstimateCPU="0.000171794" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="123.297" LogicalOp="Left Semi Join" NodeId="6" Parallel="true" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="101.094">
- <OutputList>
- <ColumnReference Database="[LBG_CPDB]" Schema="[Posting]" Table="[TrackingItems]" Column="Id" />
- <ColumnReference Database="[LBG_CPDB]" Schema="[Posting]" Table="[TrackingItems]" Column="ItemId" />
- <ColumnReference Database="[LBG_CPDB]" Schema="[Posting]" Table="[TrackingItems]" Column="PostingState" />
- </OutputList>
- <NestedLoops Optimized="false">
- <OuterReferences>
- <ColumnReference Column="Expr1009" />
- </OuterReferences>
- <RelOp AvgRowSize="52" EstimateCPU="0.0294042" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="123.297" LogicalOp="Repartition Streams" NodeId="7" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="101.122">
- <OutputList>
- <ColumnReference Database="[LBG_CPDB]" Schema="[Posting]" Table="[TrackingItems]" Column="Id" />
- <ColumnReference Database="[LBG_CPDB]" Schema="[Posting]" Table="[TrackingItems]" Column="ItemId" />
- <ColumnReference Database="[LBG_CPDB]" Schema="[Posting]" Table="[TrackingItems]" Column="PostingState" />
- <ColumnReference Column="Expr1009" />
- </OutputList>
- <Parallelism PartitioningType="RoundRobin">
- <RelOp AvgRowSize="52" EstimateCPU="4.10992E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="123.297" LogicalOp="Compute Scalar" NodeId="8" Parallel="true" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="101.092">
- <OutputList>
- <ColumnReference Database="[LBG_CPDB]" Schema="[Posting]" Table="[TrackingItems]" Column="Id" />
- <ColumnReference Database="[LBG_CPDB]" Schema="[Posting]" Table="[TrackingItems]" Column="ItemId" />
- <ColumnReference Database="[LBG_CPDB]" Schema="[Posting]" Table="[TrackingItems]" Column="PostingState" />
- <ColumnReference Column="Expr1009" />
- </OutputList>
- <ComputeScalar>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Expr1009" />
- <ScalarOperator ScalarString="CASE WHEN [LBG_CPDB].[Posting].[TrackingItems].[ResponseState] IS NOT NULL THEN [LBG_CPDB].[Posting].[TrackingItems].[ResponseState] ELSE [LBG_CPDB].[Posting].[TrackingItems].[PostingState] END">
- <IF>
- <Condition>
- <ScalarOperator>
- <Compare CompareOp="IS NOT">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[LBG_CPDB]" Schema="[Posting]" Table="[TrackingItems]" Column="ResponseState" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="NULL" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Condition>
- <Then>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[LBG_CPDB]" Schema="[Posting]" Table="[TrackingItems]" Column="ResponseState" />
- </Identifier>
- </ScalarOperator>
- </Then>
- <Else>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[LBG_CPDB]" Schema="[Posting]" Table="[TrackingItems]" Column="PostingState" />
- </Identifier>
- </ScalarOperator>
- </Else>
- </IF>
- </ScalarOperator>
- </DefinedValue>
- </DefinedValues>
- <RelOp AvgRowSize="53" EstimateCPU="0.742925" EstimateIO="99.755" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="123.297" EstimatedRowsRead="2026020" LogicalOp="Clustered Index Scan" NodeId="9" Parallel="true" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="100.498" TableCardinality="2026020">
- <OutputList>
- <ColumnReference Database="[LBG_CPDB]" Schema="[Posting]" Table="[TrackingItems]" Column="Id" />
- <ColumnReference Database="[LBG_CPDB]" Schema="[Posting]" Table="[TrackingItems]" Column="ItemId" />
- <ColumnReference Database="[LBG_CPDB]" Schema="[Posting]" Table="[TrackingItems]" Column="PostingState" />
- <ColumnReference Database="[LBG_CPDB]" Schema="[Posting]" Table="[TrackingItems]" Column="ResponseState" />
- </OutputList>
- <IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[LBG_CPDB]" Schema="[Posting]" Table="[TrackingItems]" Column="Id" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[LBG_CPDB]" Schema="[Posting]" Table="[TrackingItems]" Column="ItemId" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[LBG_CPDB]" Schema="[Posting]" Table="[TrackingItems]" Column="PostingState" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[LBG_CPDB]" Schema="[Posting]" Table="[TrackingItems]" Column="ResponseState" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[LBG_CPDB]" Schema="[Posting]" Table="[TrackingItems]" Index="[PK_TrackingItems]" IndexKind="Clustered" Storage="RowStore" />
- <Predicate>
- <ScalarOperator ScalarString="[LBG_CPDB].[Posting].[TrackingItems].[IsReadyToSentToMoRe]=(1) AND [LBG_CPDB].[Posting].[TrackingItems].[IsSentToMoRe]=(0)">
- <Logical Operation="AND">
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[LBG_CPDB]" Schema="[Posting]" Table="[TrackingItems]" Column="IsReadyToSentToMoRe" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(1)" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[LBG_CPDB]" Schema="[Posting]" Table="[TrackingItems]" Column="IsSentToMoRe" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(0)" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </Predicate>
- </IndexScan>
- </RelOp>
- </ComputeScalar>
- </RelOp>
- </Parallelism>
- </RelOp>
- <RelOp AvgRowSize="9" EstimateCPU="2.4E-05" EstimateIO="0" EstimateRebinds="92.6834" EstimateRewinds="29.6141" EstimatedExecutionMode="Row" EstimateRows="1" EstimateRowsWithoutRowGoal="7.07107" LogicalOp="Filter" NodeId="10" Parallel="true" PhysicalOp="Filter" EstimatedTotalSubtreeCost="0.00137767">
- <OutputList />
- <Filter StartupExpression="false">
- <RelOp AvgRowSize="11" EstimateCPU="5E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="122.297" EstimatedExecutionMode="Row" EstimateRows="7.07107" EstimateRowsWithoutRowGoal="50" LogicalOp="Compute Scalar" NodeId="11" Parallel="true" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.000959186">
- <OutputList>
- <ColumnReference Column="Expr1010" />
- </OutputList>
- <ComputeScalar>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Expr1010" />
- <ScalarOperator ScalarString="CONVERT_IMPLICIT(int,STRING_SPLIT.[value],0)">
- <Convert DataType="int" Style="0" Implicit="true">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Table="[STRING_SPLIT]" Column="value" />
- </Identifier>
- </ScalarOperator>
- </Convert>
- </ScalarOperator>
- </DefinedValue>
- </DefinedValues>
- <RelOp AvgRowSize="111" EstimateCPU="5.0157E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="122.297" EstimatedExecutionMode="Row" EstimateRows="7.07107" EstimateRowsWithoutRowGoal="50" LogicalOp="Table-valued function" NodeId="12" Parallel="true" PhysicalOp="Table-valued function" EstimatedTotalSubtreeCost="0.000872002">
- <OutputList>
- <ColumnReference Table="[STRING_SPLIT]" Column="value" />
- </OutputList>
- <TableValuedFunction>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Table="[STRING_SPLIT]" Column="value" />
- </DefinedValue>
- </DefinedValues>
- <Object Table="[STRING_SPLIT]" />
- <ParameterList>
- <ScalarOperator ScalarString="[@EntityStates]">
- <Identifier>
- <ColumnReference Column="@EntityStates" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator ScalarString="','">
- <Const ConstValue="','" />
- </ScalarOperator>
- </ParameterList>
- </TableValuedFunction>
- </RelOp>
- </ComputeScalar>
- </RelOp>
- <Predicate>
- <ScalarOperator ScalarString="[Expr1009]=[Expr1010]">
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Expr1009" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Expr1010" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Predicate>
- </Filter>
- </RelOp>
- </NestedLoops>
- </RelOp>
- </Parallelism>
- </RelOp>
- </Sort>
- </RelOp>
- </StreamAggregate>
- </RelOp>
- </Top>
- </RelOp>
- </ComputeScalar>
- </RelOp>
- </Update>
- </RelOp>
- </QueryPlan>
- </StmtSimple>
- </Statements>
- </Batch>
- </BatchSequence>
- </ShowPlanXML>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement