query execution plan

• humayan_kabir02

  SSC Enthusiast

  Points: 172

  More actions

  September 12, 2019 at 7:00 pm

  #3679643

  hello experts,

  i need help to understand query plan and what i can do to make it better . i am really confused about the missing index message .

  <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.518" Build="13.0.5153.0">

  <BatchSequence>

  <Batch>

  <Statements>

  <StmtCursor StatementText="SELECT TOP 5 D.DocumentID FROM nsTransactionProperties TP JOIN nsDocument D ON TP.DocumentId = D.DocumentID JOIN nsFolderDocument FD ON D.DocumentID = FD.DocId JOIN nsFolder F on FD.FolderId = F.FolderId WHERE F.Title = @P0 AND D.DocumentOwner = @P1 AND TP.ToBePurgedDate &lt;= @P2" StatementId="1" StatementCompId="1" StatementType="DECLARE CURSOR" RetrievedFromCache="true">

  <CursorPlan CursorName="" CursorActualType="FastForward" CursorRequestedType="FastForward" CursorConcurrency="Read Only" ForwardOnly="true">

  <Operation OperationType="PopulateQuery">

  <QueryPlan NonParallelPlanReason="NoParallelFastForwardCursor" CachedPlanSize="128" CompileTime="9571" CompileCPU="682" CompileMemory="2008">

  <MissingIndexes>

  <MissingIndexGroup Impact="62.8533">

  <MissingIndex Database="[NetDB]" Schema="[dbo]" Table="[nsTransactionProperties]">

  <ColumnGroup Usage="INEQUALITY">

  <Column Name="[ToBePurgedDate]" ColumnId="14">

  </Column>

  </ColumnGroup>

  <ColumnGroup Usage="INCLUDE">

  <Column Name="[DocumentId]" ColumnId="1">

  </Column>

  </ColumnGroup>

  </MissingIndex>

  </MissingIndexGroup>

  <MissingIndexGroup Impact="84.2293">

  <MissingIndex Database="[NetDB]" Schema="[dbo]" Table="[nsTransactionProperties]">

  <ColumnGroup Usage="INEQUALITY">

  <Column Name="[ToBePurgedDate]" ColumnId="14">

  </Column>

  </ColumnGroup>

  <ColumnGroup Usage="INCLUDE">

  <Column Name="[DocumentId]" ColumnId="1">

  </Column>

  </ColumnGroup>

  </MissingIndex>

  </MissingIndexGroup>

  </MissingIndexes>

  <Warnings>

  <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(100),[TP].[DocumentId],0)">

  </PlanAffectingConvert>

  <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(100),[FD].[DocId],0)">

  </PlanAffectingConvert>

  <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(100),[TP].[DocumentId],0)=[D].[DocumentID]">

  </PlanAffectingConvert>

  <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(100),[FD].[DocId],0)=[Expr1008]">

  </PlanAffectingConvert>

  <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="[Expr1007]=CONVERT_IMPLICIT(nvarchar(100),[TP].[DocumentId],0)">

  </PlanAffectingConvert>

  <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="[D].[DocumentID]=CONVERT_IMPLICIT(nvarchar(100),[FD].[DocId],0)">

  </PlanAffectingConvert>

  </Warnings>

  <MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="1728">

  </MemoryGrantInfo>

  <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="760217" EstimatedPagesCached="760217" EstimatedAvailableDegreeOfParallelism="8" MaxCompileMemory="80511760">

  </OptimizerHardwareDependentProperties>

  <RelOp NodeId="0" PhysicalOp="Clustered Index Insert" LogicalOp="Insert" EstimateRows="5" EstimateIO="0.01" EstimateCPU="5e-006" AvgRowSize="79" EstimatedTotalSubtreeCost="117.894" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">

  <OutputList>

  <ColumnReference Database="[NetDB]" Schema="[dbo]" Table="[nsDocument]" Alias="[D]" Column="DocumentID">

  </ColumnReference>

  </OutputList>

  <Update DMLRequestSort="0">

  <Object Database="[tempdb]" Index="[CWT_PrimaryKey]" Storage="RowStore">

  </Object>

  <SetPredicate>

  <ScalarOperator ScalarString="[STREAM].[COLUMN0] = [NetDB].[dbo].[nsDocument].[DocumentID] as [D].[DocumentID],[STREAM].[ROWID] = [I4Rank1009]">

  <ScalarExpressionList>

  <ScalarOperator>

  <MultipleAssign>

  <Assign>

  <ColumnReference Table="[STREAM]" Column="COLUMN0">

  </ColumnReference>

  <ScalarOperator>

  <Identifier>

  <ColumnReference Database="[NetDB]" Schema="[dbo]" Table="[nsDocument]" Alias="[D]" Column="DocumentID">

  </ColumnReference>

  </Identifier>

  </ScalarOperator>

  </Assign>

  <Assign>

  <ColumnReference Table="[STREAM]" Column="ROWID">

  </ColumnReference>

  <ScalarOperator>

  <Identifier>

  <ColumnReference Column="I4Rank1009">

  </ColumnReference>

  </Identifier>

  </ScalarOperator>

  </Assign>

  </MultipleAssign>

  </ScalarOperator>

  </ScalarExpressionList>

  </ScalarOperator>

  </SetPredicate>

  <RelOp NodeId="1" PhysicalOp="Sequence Project" LogicalOp="Compute Scalar" EstimateRows="5" EstimateIO="0" EstimateCPU="4e-007" AvgRowSize="83" EstimatedTotalSubtreeCost="117.884" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">

  <OutputList>

  <ColumnReference Database="[NetDB]" Schema="[dbo]" Table="[nsDocument]" Alias="[D]" Column="DocumentID">

  </ColumnReference>

  <ColumnReference Column="I4Rank1009">

  </ColumnReference>

  </OutputList>

  <SequenceProject>

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Column="I4Rank1009">

  </ColumnReference>

  <ScalarOperator ScalarString="i4_row_number">

  <Sequence FunctionName="i4_row_number">

  </Sequence>

  </ScalarOperator>

  </DefinedValue>

  </DefinedValues>

  <RelOp NodeId="2" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="5" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="83" EstimatedTotalSubtreeCost="117.884" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">

  <OutputList>

  <ColumnReference Database="[NetDB]" Schema="[dbo]" Table="[nsDocument]" Alias="[D]" Column="DocumentID">

  </ColumnReference>

  <ColumnReference Column="Segment1021">

  </ColumnReference>

  </OutputList>

  <Segment>

  <GroupBy>

  </GroupBy>

  <SegmentColumn>

  <ColumnReference Column="Segment1021">

  </ColumnReference>

  </SegmentColumn>

  <RelOp NodeId="3" PhysicalOp="Top" LogicalOp="Top" EstimateRows="5" EstimateIO="0" EstimateCPU="5e-007" AvgRowSize="79" EstimatedTotalSubtreeCost="117.884" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">

  <OutputList>

  <ColumnReference Database="[NetDB]" Schema="[dbo]" Table="[nsDocument]" Alias="[D]" Column="DocumentID">

  </ColumnReference>

  </OutputList>

  <Top RowCount="0" IsPercent="0" WithTies="0">

  <TopExpression>

  <ScalarOperator ScalarString="(5)">

  <Const ConstValue="(5)">

  </Const>

  </ScalarOperator>

  </TopExpression>

  <RelOp NodeId="4" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="5" EstimateRowsWithoutRowGoal="2884.58" EstimateIO="0" EstimateCPU="15880.9" AvgRowSize="79" EstimatedTotalSubtreeCost="117.884" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">

  <OutputList>

  <ColumnReference Database="[NetDB]" Schema="[dbo]" Table="[nsDocument]" Alias="[D]" Column="DocumentID">

  </ColumnReference>

  </OutputList>

  <NestedLoops Optimized="0">

  <Predicate>

  <ScalarOperator ScalarString="[Expr1007]=[Expr1008]">

  <Compare CompareOp="EQ">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Column="Expr1007">

  </ColumnReference>

  </Identifier>

  </ScalarOperator>

  <ScalarOperator>

  <Identifier>

  <ColumnReference Column="Expr1008">

  </ColumnReference>

  </Identifier>

  </ScalarOperator>

  </Compare>

  </ScalarOperator>

  </Predicate>

  <RelOp NodeId="5" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="6.5486" EstimateRowsWithoutRowGoal="3777.98" EstimateIO="0" EstimateCPU="0.015792" AvgRowSize="125" EstimatedTotalSubtreeCost="10.6596" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">

  <OutputList>

  <ColumnReference Database="[NetDB]" Schema="[dbo]" Table="[nsDocument]" Alias="[D]" Column="DocumentID">

  </ColumnReference>

  <ColumnReference Column="Expr1007">

  </ColumnReference>

  </OutputList>

  <MemoryFractions Input="0" Output="1">

  </MemoryFractions>

  <NestedLoops Optimized="1" WithUnorderedPrefetch="1">

  <OuterReferences>

  <ColumnReference Column="Bmk1005">

  </ColumnReference>

  <ColumnReference Column="Expr1020">

  </ColumnReference>

  </OuterReferences>

  <RelOp NodeId="8" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="6.5486" EstimateRowsWithoutRowGoal="3777.98" EstimateIO="0" EstimateCPU="0.0158377" AvgRowSize="122" EstimatedTotalSubtreeCost="10.6348" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">

  <OutputList>

  <ColumnReference Database="[NetDB]" Schema="[dbo]" Table="[nsDocument]" Alias="[D]" Column="DocumentID">

  </ColumnReference>

  <ColumnReference Column="Bmk1005">

  </ColumnReference>

  <ColumnReference Column="Expr1007">

  </ColumnReference>

  <ColumnReference Column="Expr1019">

  </ColumnReference>

  </OutputList>

  <ComputeScalar>

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Column="Expr1019">

  </ColumnReference>

  <ScalarOperator ScalarString="BmkToPage([Bmk1005])">

  <Intrinsic FunctionName="BmkToPage">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Column="Bmk1005">

  </ColumnReference>

  </Identifier>

  </ScalarOperator>

  </Intrinsic>

  </ScalarOperator>

  </DefinedValue>

  </DefinedValues>

  <RelOp NodeId="9" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="6.5486" EstimateRowsWithoutRowGoal="3777.98" EstimateIO="0" EstimateCPU="0.0158377" AvgRowSize="122" EstimatedTotalSubtreeCost="10.6348" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">

  <OutputList>

  <ColumnReference Database="[NetDB]" Schema="[dbo]" Table="[nsDocument]" Alias="[D]" Column="DocumentID">

  </ColumnReference>

  <ColumnReference Column="Bmk1005">

  </ColumnReference>

  <ColumnReference Column="Expr1007">

  </ColumnReference>

  </OutputList>

  <NestedLoops Optimized="0" WithUnorderedPrefetch="1">

  <OuterReferences>

  <ColumnReference Database="[NetDB]" Schema="[dbo]" Table="[nsFolderDocument]" Alias="[FD]" Column="FolderId">

  </ColumnReference>

  <ColumnReference Column="Expr1018">

  </ColumnReference>

  </OuterReferences>

  <RelOp NodeId="11" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="10166.6" EstimateRowsWithoutRowGoal="5.86528e+006" EstimateIO="0" EstimateCPU="24.5169" AvgRowSize="132" EstimatedTotalSubtreeCost="10.6235" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">

  <OutputList>

  <ColumnReference Database="[NetDB]" Schema="[dbo]" Table="[nsDocument]" Alias="[D]" Column="DocumentID">

  </ColumnReference>

  <ColumnReference Database="[NetDB]" Schema="[dbo]" Table="[nsFolderDocument]" Alias="[FD]" Column="FolderId">

  </ColumnReference>

  <ColumnReference Column="Expr1007">

  </ColumnReference>

  </OutputList>

  <NestedLoops Optimized="0" WithOrderedPrefetch="1">

  <OuterReferences>

  <ColumnReference Column="Bmk1001">

  </ColumnReference>

  <ColumnReference Column="Expr1017">

  </ColumnReference>

  </OuterReferences>

  <RelOp NodeId="13" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="10166.6" EstimateRowsWithoutRowGoal="5.86528e+006" EstimateIO="0" EstimateCPU="24.5169" AvgRowSize="131" EstimatedTotalSubtreeCost="8.97023" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">

  <OutputList>

  <ColumnReference Column="Bmk1001">

  </ColumnReference>

  <ColumnReference Database="[NetDB]" Schema="[dbo]" Table="[nsDocument]" Alias="[D]" Column="DocumentID">

  </ColumnReference>

  <ColumnReference Database="[NetDB]" Schema="[dbo]" Table="[nsFolderDocument]" Alias="[FD]" Column="FolderId">

  </ColumnReference>

  <ColumnReference Column="Expr1007">

  </ColumnReference>

  </OutputList>

  <NestedLoops Optimized="0" WithOrderedPrefetch="1">

  <OuterReferences>

  <ColumnReference Column="Expr1007">

  </ColumnReference>

  <ColumnReference Column="Expr1016">

  </ColumnReference>

  </OuterReferences>

  <RelOp NodeId="15" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="9832.24" EstimateRowsWithoutRowGoal="5.67237e+006" EstimateIO="0" EstimateCPU="0.567237" AvgRowSize="53" EstimatedTotalSubtreeCost="0.110448" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">

  <OutputList>

  <ColumnReference Database="[NetDB]" Schema="[dbo]" Table="[nsFolderDocument]" Alias="[FD]" Column="FolderId">

  </ColumnReference>

  <ColumnReference Column="Expr1007">

  </ColumnReference>

  </OutputList>

  <ComputeScalar>

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Column="Expr1007">

  </ColumnReference>

  <ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(100),[NetDB].[dbo].[nsFolderDocument].[DocId] as [FD].[DocId],0)">

  <Convert DataType="nvarchar" Length="200" Style="0" Implicit="1">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Database="[NetDB]" Schema="[dbo]" Table="[nsFolderDocument]" Alias="[FD]" Column="DocId">

  </ColumnReference>

  </Identifier>

  </ScalarOperator>

  </Convert>

  </ScalarOperator>

  </DefinedValue>

  </DefinedValues>

  <RelOp NodeId="16" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="9832.24" EstimateRowsWithoutRowGoal="5.67237e+006" EstimatedRowsRead="5.67237e+006" EstimateIO="55.0276" EstimateCPU="6.23976" AvgRowSize="53" EstimatedTotalSubtreeCost="0.109465" TableCardinality="5.67237e+006" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">

  <OutputList>

  <ColumnReference Database="[NetDB]" Schema="[dbo]" Table="[nsFolderDocument]" Alias="[FD]" Column="DocId">

  </ColumnReference>

  <ColumnReference Database="[NetDB]" Schema="[dbo]" Table="[nsFolderDocument]" Alias="[FD]" Column="FolderId">

  </ColumnReference>

  </OutputList>

  <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Database="[NetDB]" Schema="[dbo]" Table="[nsFolderDocument]" Alias="[FD]" Column="DocId">

  </ColumnReference>

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Database="[NetDB]" Schema="[dbo]" Table="[nsFolderDocument]" Alias="[FD]" Column="FolderId">

  </ColumnReference>

  </DefinedValue>

  </DefinedValues>

  <Object Database="[NetDB]" Schema="[dbo]" Table="[nsFolderDocument]" Index="[PK_nsFolderDocument]" Alias="[FD]" IndexKind="NonClustered" Storage="RowStore">

  </Object>

  </IndexScan>

  </RelOp>

  </ComputeScalar>

  </RelOp>

  <RelOp NodeId="17" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1.03401" EstimatedRowsRead="1.03401" EstimateIO="0.003125" EstimateCPU="0.000158137" AvgRowSize="87" EstimatedTotalSubtreeCost="8.81728" TableCardinality="9.34345e+006" Parallel="0" EstimateRebinds="9828.11" EstimateRewinds="4.12712" EstimatedExecutionMode="Row">

  <OutputList>

  <ColumnReference Column="Bmk1001">

  </ColumnReference>

  <ColumnReference Database="[NetDB]" Schema="[dbo]" Table="[nsDocument]" Alias="[D]" Column="DocumentID">

  </ColumnReference>

  </OutputList>

  <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Column="Bmk1001">

  </ColumnReference>

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Database="[NetDB]" Schema="[dbo]" Table="[nsDocument]" Alias="[D]" Column="DocumentID">

  </ColumnReference>

  </DefinedValue>

  </DefinedValues>

  <Object Database="[NetDB]" Schema="[dbo]" Table="[nsDocument]" Index="[IX_nsDocument_1]" Alias="[D]" IndexKind="NonClustered" Storage="RowStore">

  </Object>

  <SeekPredicates>

  <SeekPredicateNew>

  <SeekKeys>

  <Prefix ScanType="EQ">

  <RangeColumns>

  <ColumnReference Database="[NetDB]" Schema="[dbo]" Table="[nsDocument]" Alias="[D]" Column="DocumentID">

  </ColumnReference>

  </RangeColumns>

  <RangeExpressions>

  <ScalarOperator ScalarString="[Expr1007]">

  <Identifier>

  <ColumnReference Column="Expr1007">

  </ColumnReference>

  </Identifier>

  </ScalarOperator>

  </RangeExpressions>

  </Prefix>

  </SeekKeys>

  </SeekPredicateNew>

  </SeekPredicates>

  </IndexScan>

  </RelOp>

  </NestedLoops>

  </RelOp>

  <RelOp NodeId="19" PhysicalOp="RID Lookup" LogicalOp="RID Lookup" EstimateRows="6.56755" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="36" EstimatedTotalSubtreeCost="1.61073" TableCardinality="9.34345e+006" Parallel="0" EstimateRebinds="334.384" EstimateRewinds="9832.23" EstimatedExecutionMode="Row">

  <OutputList>

  </OutputList>

  <IndexScan Lookup="1" Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">

  <DefinedValues>

  </DefinedValues>

  <Object Database="[NetDB]" Schema="[dbo]" Table="[nsDocument]" Alias="[D]" TableReferenceId="-1" IndexKind="Heap" Storage="RowStore">

  </Object>

  <SeekPredicates>

  <SeekPredicateNew>

  <SeekKeys>

  <Prefix ScanType="EQ">

  <RangeColumns>

  <ColumnReference Column="Bmk1001">

  </ColumnReference>

  </RangeColumns>

  <RangeExpressions>

  <ScalarOperator ScalarString="[Bmk1001]">

  <Identifier>

  <ColumnReference Column="Bmk1001">

  </ColumnReference>

  </Identifier>

  </ScalarOperator>

  </RangeExpressions>

  </Prefix>

  </SeekKeys>

  </SeekPredicateNew>

  </SeekPredicates>

  <Predicate>

  <ScalarOperator ScalarString="[NetDB].[dbo].[nsDocument].[DocumentOwner] as [D].[DocumentOwner]=[@P1]">

  <Compare CompareOp="EQ">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Database="[NetDB]" Schema="[dbo]" Table="[nsDocument]" Alias="[D]" Column="DocumentOwner">

  </ColumnReference>

  </Identifier>

  </ScalarOperator>

  <ScalarOperator>

  <Identifier>

  <ColumnReference Column="@P1">

  </ColumnReference>

  </Identifier>

  </ScalarOperator>

  </Compare>

  </ScalarOperator>

  </Predicate>

  </IndexScan>

  </RelOp>

  </NestedLoops>

  </RelOp>

  <RelOp NodeId="20" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="15" EstimatedTotalSubtreeCost="0.00641275" TableCardinality="2.78548e+007" Parallel="0" EstimateRebinds="1.66922" EstimateRewinds="4.89847" EstimatedExecutionMode="Row">

  <OutputList>

  <ColumnReference Column="Bmk1005">

  </ColumnReference>

  </OutputList>

  <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Column="Bmk1005">

  </ColumnReference>

  </DefinedValue>

  </DefinedValues>

  <Object Database="[NetDB]" Schema="[dbo]" Table="[nsFolder]" Index="[PK_nsFolder]" Alias="[F]" IndexKind="NonClustered" Storage="RowStore">

  </Object>

  <SeekPredicates>

  <SeekPredicateNew>

  <SeekKeys>

  <Prefix ScanType="EQ">

  <RangeColumns>

  <ColumnReference Database="[NetDB]" Schema="[dbo]" Table="[nsFolder]" Alias="[F]" Column="FolderId">

  </ColumnReference>

  </RangeColumns>

  <RangeExpressions>

  <ScalarOperator ScalarString="[NetDB].[dbo].[nsFolderDocument].[FolderId] as [FD].[FolderId]">

  <Identifier>

  <ColumnReference Database="[NetDB]" Schema="[dbo]" Table="[nsFolderDocument]" Alias="[FD]" Column="FolderId">

  </ColumnReference>

  </Identifier>

  </ScalarOperator>

  </RangeExpressions>

  </Prefix>

  </SeekKeys>

  </SeekPredicateNew>

  </SeekPredicates>

  </IndexScan>

  </RelOp>

  </NestedLoops>

  </RelOp>

  </ComputeScalar>

  </RelOp>

  <RelOp NodeId="22" PhysicalOp="RID Lookup" LogicalOp="RID Lookup" EstimateRows="6.38361" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="61" EstimatedTotalSubtreeCost="0.0247533" TableCardinality="2.78548e+007" Parallel="0" EstimateRebinds="6.54689" EstimateRewinds="0.00173336" EstimatedExecutionMode="Row">

  <OutputList>

  </OutputList>

  <IndexScan Lookup="1" Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">

  <DefinedValues>

  </DefinedValues>

  <Object Database="[NetDB]" Schema="[dbo]" Table="[nsFolder]" Alias="[F]" TableReferenceId="-1" IndexKind="Heap" Storage="RowStore">

  </Object>

  <SeekPredicates>

  <SeekPredicateNew>

  <SeekKeys>

  <Prefix ScanType="EQ">

  <RangeColumns>

  <ColumnReference Column="Bmk1005">

  </ColumnReference>

  </RangeColumns>

  <RangeExpressions>

  <ScalarOperator ScalarString="[Bmk1005]">

  <Identifier>

  <ColumnReference Column="Bmk1005">

  </ColumnReference>

  </Identifier>

  </ScalarOperator>

  </RangeExpressions>

  </Prefix>

  </SeekKeys>

  </SeekPredicateNew>

  </SeekPredicates>

  <Predicate>

  <ScalarOperator ScalarString="[NetDB].[dbo].[nsFolder].[Title] as [F].[Title]=[@P0]">

  <Compare CompareOp="EQ">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Database="[NetDB]" Schema="[dbo]" Table="[nsFolder]" Alias="[F]" Column="Title">

  </ColumnReference>

  </Identifier>

  </ScalarOperator>

  <ScalarOperator>

  <Identifier>

  <ColumnReference Column="@P0">

  </ColumnReference>

  </Identifier>

  </ScalarOperator>

  </Compare>

  </ScalarOperator>

  </Predicate>

  </IndexScan>

  </RelOp>

  </NestedLoops>

  </RelOp>

  <RelOp NodeId="23" PhysicalOp="Table Spool" LogicalOp="Lazy Spool" EstimateRows="1.03162e+006" EstimateIO="0.01" EstimateCPU="0.185792" AvgRowSize="44" EstimatedTotalSubtreeCost="83.405" Parallel="0" EstimateRebinds="0" EstimateRewinds="6.38222" EstimatedExecutionMode="Row">

  <OutputList>

  <ColumnReference Column="Expr1008">

  </ColumnReference>

  </OutputList>

  <Spool>

  <RelOp NodeId="24" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1.03162e+006" EstimateIO="0" EstimateCPU="0.421086" AvgRowSize="52" EstimatedTotalSubtreeCost="80.0022" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">

  <OutputList>

  <ColumnReference Column="Expr1008">

  </ColumnReference>

  </OutputList>

  <ComputeScalar>

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Column="Expr1008">

  </ColumnReference>

  <ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(100),[NetDB].[dbo].[nsTransactionProperties].[DocumentId] as [TP].[DocumentId],0)">

  <Convert DataType="nvarchar" Length="200" Style="0" Implicit="1">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Database="[NetDB]" Schema="[dbo]" Table="[nsTransactionProperties]" Alias="[TP]" Column="DocumentId">

  </ColumnReference>

  </Identifier>

  </ScalarOperator>

  </Convert>

  </ScalarOperator>

  </DefinedValue>

  </DefinedValues>

  <RelOp NodeId="25" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1.03162e+006" EstimatedRowsRead="4.21086e+006" EstimateIO="74.9491" EstimateCPU="4.63211" AvgRowSize="52" EstimatedTotalSubtreeCost="79.5812" TableCardinality="4.21086e+006" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">

  <OutputList>

  <ColumnReference Database="[NetDB]" Schema="[dbo]" Table="[nsTransactionProperties]" Alias="[TP]" Column="DocumentId">

  </ColumnReference>

  </OutputList>

  <IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Database="[NetDB]" Schema="[dbo]" Table="[nsTransactionProperties]" Alias="[TP]" Column="DocumentId">

  </ColumnReference>

  </DefinedValue>

  </DefinedValues>

  <Object Database="[NetDB]" Schema="[dbo]" Table="[nsTransactionProperties]" Index="[PK__nsTransactionPro__361203C5]" Alias="[TP]" IndexKind="Clustered" Storage="RowStore">

  </Object>

  <Predicate>

  <ScalarOperator ScalarString="[NetDB].[dbo].[nsTransactionProperties].[ToBePurgedDate] as [TP].[ToBePurgedDate]&lt;=[@P2]">

  <Compare CompareOp="LE">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Database="[NetDB]" Schema="[dbo]" Table="[nsTransactionProperties]" Alias="[TP]" Column="ToBePurgedDate">

  </ColumnReference>

  </Identifier>

  </ScalarOperator>

  <ScalarOperator>

  <Identifier>

  <ColumnReference Column="@P2">

  </ColumnReference>

  </Identifier>

  </ScalarOperator>

  </Compare>

  </ScalarOperator>

  </Predicate>

  </IndexScan>

  </RelOp>

  </ComputeScalar>

  </RelOp>

  </Spool>

  </RelOp>

  </NestedLoops>

  </RelOp>

  </Top>

  </RelOp>

  </Segment>

  </RelOp>

  </SequenceProject>

  </RelOp>

  </Update>

  </RelOp>

  <ParameterList>

  <ColumnReference Column="@P2" ParameterDataType="datetime" ParameterCompiledValue="'2019-09-12 09:21:33.197'">

  </ColumnReference>

  <ColumnReference Column="@P1" ParameterDataType="varchar(8000)" ParameterCompiledValue="'advisorgroup'">

  </ColumnReference>

  <ColumnReference Column="@P0" ParameterDataType="varchar(8000)" ParameterCompiledValue="'PendingDeletion'">

  </ColumnReference>

  </ParameterList>

  </QueryPlan>

  </Operation>

  <Operation OperationType="FetchQuery">

  <QueryPlan NonParallelPlanReason="CouldNotGenerateValidParallelPlan" CachedPlanSize="128" CompileTime="0" CompileCPU="0" CompileMemory="192">

  <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0">

  </MemoryGrantInfo>

  <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="760217" EstimatedPagesCached="760217" EstimatedAvailableDegreeOfParallelism="8" MaxCompileMemory="80511760">

  </OptimizerHardwareDependentProperties>

  <RelOp NodeId="0" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="115" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">

  <OutputList>

  <ColumnReference Table="[CWT]" Column="COLUMN0">

  </ColumnReference>

  <ColumnReference Table="[CWT]" Column="ROWID">

  </ColumnReference>

  </OutputList>

  <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Table="[CWT]" Column="COLUMN0">

  </ColumnReference>

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Table="[CWT]" Column="ROWID">

  </ColumnReference>

  </DefinedValue>

  </DefinedValues>

  <Object Database="[tempdb]" Index="[CWT_PrimaryKey]" Storage="RowStore">

  </Object>

  <SeekPredicates>

  <SeekPredicateNew>

  <SeekKeys>

  <StartRange ScanType="GE">

  <RangeColumns>

  <ColumnReference Table="[CWT]" Column="ROWID">

  </ColumnReference>

  </RangeColumns>

  <RangeExpressions>

  <ScalarOperator ScalarString="FETCH_RANGE((0))">

  <Identifier>

  <ColumnReference Column="ConstExpr1002">

  <ScalarOperator>

  <Intrinsic FunctionName="FETCH_RANGE">

  <ScalarOperator>

  <Const ConstValue="(0)">

  </Const>

  </ScalarOperator>

  </Intrinsic>

  </ScalarOperator>

  </ColumnReference>

  </Identifier>

  </ScalarOperator>

  </RangeExpressions>

  </StartRange>

  <EndRange ScanType="LT">

  <RangeColumns>

  <ColumnReference Table="[CWT]" Column="ROWID">

  </ColumnReference>

  </RangeColumns>

  <RangeExpressions>

  <ScalarOperator ScalarString="FETCH_RANGE((1))">

  <Identifier>

  <ColumnReference Column="ConstExpr1003">

  <ScalarOperator>

  <Intrinsic FunctionName="FETCH_RANGE">

  <ScalarOperator>

  <Const ConstValue="(1)">

  </Const>

  </ScalarOperator>

  </Intrinsic>

  </ScalarOperator>

  </ColumnReference>

  </Identifier>

  </ScalarOperator>

  </RangeExpressions>

  </EndRange>

  </SeekKeys>

  </SeekPredicateNew>

  </SeekPredicates>

  </IndexScan>

  </RelOp>

  </QueryPlan>

  </Operation>

  </CursorPlan>

  </StmtCursor>

  </Statements>

  </Batch>

  </BatchSequence>

  </ShowPlanXML>

  Thanks

• Jonathan AC Roberts

  SSCoach

  Points: 18263

  More actions

  September 12, 2019 at 8:44 pm

  #3679659

  SQL Server is suggesting you try the following index:

  USE [NetDB]
  GO
   CREATE NONCLUSTERED INDEX [IX_nsTransactionProperties_1]
       ON [dbo].[nsTransactionProperties]([ToBePurgedDate])
  INCLUDE ([DocumentId])

• MVDBA (Mike Vessey)

  SSC-Insane

  Points: 21797

  More actions

  September 13, 2019 at 2:46 pm

  #3679844

  you might find that after adding the index that you will get a new index suggestion afterwards

  I often have to go through 3 rounds of execution plans to get all of the indexes I need

  MVDBA

• Ken McKelvey

  SSCoach

  Points: 18768

  More actions

  September 16, 2019 at 10:31 am

  #3680263

  Indexes should be designed with the whole system in mind, not just a single query.

• alex.whyte

  Newbie

  Points: 5

  More actions

  September 19, 2019 at 9:00 am

  #3681301

  Before adding the indexes resolve these implicit conversion warnings affecting your cardinality estimates and seeks.

  PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(100),[TP].[DocumentId],0)"

  PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(100),[FD].[DocId],0)"

  PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(100),[TP].[DocumentId],0)=[D].[DocumentID]"

  PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(100),[FD].[DocId],0)=[Expr1008]"

  PlanAffectingConvert ConvertIssue="Seek Plan" Expression="[Expr1007]=CONVERT_IMPLICIT(nvarchar(100),[TP].[DocumentId],0)"

  PlanAffectingConvert ConvertIssue="Seek Plan" Expression="[D].[DocumentID]=CONVERT_IMPLICIT(nvarchar(100),[FD].[DocId],0)"