title: Complex query is slow for new records
body: <p>Hi I have a complex query that runs real quick on existing data, like less than a 100ms</p>
  
  <p>But I inserted a few new records and run the exact same query on them, it takes 4s to return</p>
  
  <p>I tested again with some other old records picked randomly, and it is still less than 100ms.</p>
  
  <p>I'm looking for a trouble shooting list of things that I should check to get to the bottom of this issue and make the query fast again on new records.</p>
  
  <p>e.g. I am expecting answers like</p>
  
  <ol>
  <li>Check execution plan, look for aaa, if yes, do bbb else</li>
  <li>Check fragmentation, look for ccc, if yes, do ddd else...</li>
  </ol>
  
  <p>Database is MSSQL2008R2</p>
  
  <h2>Edit (Execution time)</h2>
  
  <p>Table 'Worktable'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.</p>
  
  <p>Table 'Table_O'. Scan count 0, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.</p>
  
  <p>Table 'Table_O_V'. Scan count 0, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.</p>
  
  <p>Table 'Table_A_O'. Scan count 0, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.</p>
  
  <p>Table 'Table_A_A'. Scan count 1, logical reads 33, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.</p>
  
  <p>Table 'Table_D'. Scan count 5, logical reads 124, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.</p>
  
  <p>Table 'Table_S_D'. Scan count 1, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.</p>
  
  <p>Table 'Table_A_S'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.</p>
  
  <p>Table 'Table_A'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.</p>
  
  <p>(1 row(s) affected)</p>
  
  <p>SQL Server Execution Times:
     CPU time = 16 ms,  elapsed time = 6 ms.
  SQL Server parse and compile time: 
     CPU time = 0 ms, elapsed time = 0 ms.</p>
  
  <p>Table 'Table_O'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.</p>
  
  <p>Table 'Table_O_V'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.</p>
  
  <p>Table 'Table_A_O'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.</p>
  
  <p>Table 'Worktable'. Scan count 1, logical reads 2603677, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.</p>
  
  <p>Table 'Table_A_A'. Scan count 1, logical reads 30832, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.</p>
  
  <p>Table 'Table_D'. Scan count 3, logical reads 72, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.</p>
  
  <p>Table 'Table_S_D'. Scan count 1, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.</p>
  
  <p>Table 'Table_A_S'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.</p>
  
  <p>Table 'Table_A'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.</p>
  
  <p>(1 row(s) affected)</p>
  
  <p>SQL Server Execution Times:
     CPU time = 2574 ms,  elapsed time = 2643 ms.
  SQL Server parse and compile time: 
     CPU time = 31 ms, elapsed time = 55 ms.</p>
  
  <h2>Edit (sqlplan)</h2>
  
  <pre><code>&lt;?xml version="1.0" encoding="utf-16"?&gt;
  &lt;ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.50.4000.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"&gt;
      &lt;BatchSequence&gt;
          &lt;Batch&gt;
              &lt;Statements&gt;
                  &lt;StmtSimple StatementCompId="1" StatementEstRows="17652.3" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="4.05429" StatementText="SELECT *" StatementType="SELECT" QueryHash="0x66605DE9B339CF45" QueryPlanHash="0x3FFB07F2D475248D"&gt;
                      &lt;StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" /&gt;
                      &lt;QueryPlan DegreeOfParallelism="1" MemoryGrant="2496" CachedPlanSize="176" CompileTime="54" CompileCPU="52" CompileMemory="3032"&gt;
                          &lt;RelOp AvgRowSize="23" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="76" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0403057"&gt;
                              &lt;OutputList&gt;
                                  &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_A_O]" Alias="[ao]" Column="o_v_id" /&gt;
                              &lt;/OutputList&gt;
                              &lt;RunTimeInformation&gt;
                                  &lt;RunTimeCountersPerThread Thread="0" ActualRows="5" ActualEndOfScans="1" ActualExecutions="1" /&gt;
                              &lt;/RunTimeInformation&gt;
                              &lt;NestedLoops Optimized="false"&gt;
                                  &lt;OuterReferences&gt;
                                      &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_A_A]" Alias="[asso]" Column="a2_id" /&gt;
                                  &lt;/OuterReferences&gt;
                                  &lt;RelOp AvgRowSize="45" EstimateCPU="6.47743E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="15.4963" LogicalOp="Inner Join" NodeId="77" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0370109"&gt;
                                      &lt;OutputList&gt;
                                          &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_A_A]" Alias="[asso]" Column="a2_id" /&gt;
                                      &lt;/OutputList&gt;
                                      &lt;RunTimeInformation&gt;
                                          &lt;RunTimeCountersPerThread Thread="0" ActualRows="5" ActualEndOfScans="1" ActualExecutions="1" /&gt;
                                      &lt;/RunTimeInformation&gt;
                                      &lt;NestedLoops Optimized="false"&gt;
                                          &lt;OuterReferences&gt;
                                              &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_A_A]" Alias="[asso]" Column="id" /&gt;
                                          &lt;/OuterReferences&gt;
                                          &lt;RelOp AvgRowSize="23" EstimateCPU="0.000174046" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="15.4963" LogicalOp="Index Seek" NodeId="78" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00329905" TableCardinality="861820"&gt;
                                              &lt;OutputList&gt;
                                                  &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_A_A]" Alias="[asso]" Column="id" /&gt;
                                              &lt;/OutputList&gt;
                                              &lt;RunTimeInformation&gt;
                                                  &lt;RunTimeCountersPerThread Thread="0" ActualRows="10" ActualEndOfScans="1" ActualExecutions="1" /&gt;
                                              &lt;/RunTimeInformation&gt;
                                              &lt;IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false"&gt;
                                                  &lt;DefinedValues&gt;
                                                      &lt;DefinedValue&gt;
                                                          &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_A_A]" Alias="[asso]" Column="id" /&gt;
                                                      &lt;/DefinedValue&gt;
                                                  &lt;/DefinedValues&gt;
                                                  &lt;Object Database="[DB]" Schema="[DBO]" Table="[Table_A_A]" Index="[I_A_A_a1_id]" Alias="[asso]" IndexKind="NonClustered" /&gt;
                                                  &lt;SeekPredicates&gt;
                                                      &lt;SeekPredicateNew&gt;
                                                          &lt;SeekKeys&gt;
                                                              &lt;Prefix ScanType="EQ"&gt;
                                                                  &lt;RangeColumns&gt;
                                                                      &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_A_A]" Alias="[asso]" Column="a1_id" /&gt;
                                                                  &lt;/RangeColumns&gt;
                                                                  &lt;RangeExpressions&gt;
                                                                      &lt;ScalarOperator ScalarString="[DB].[DBO].[Table_A].[id] as [a].[id]"&gt;
                                                                          &lt;Identifier&gt;
                                                                              &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_A]" Alias="[a]" Column="id" /&gt;
                                                                          &lt;/Identifier&gt;
                                                                      &lt;/ScalarOperator&gt;
                                                                  &lt;/RangeExpressions&gt;
                                                              &lt;/Prefix&gt;
                                                          &lt;/SeekKeys&gt;
                                                      &lt;/SeekPredicateNew&gt;
                                                  &lt;/SeekPredicates&gt;
                                              &lt;/IndexScan&gt;
                                          &lt;/RelOp&gt;
                                          &lt;RelOp AvgRowSize="45" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="14.4963" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="80" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0336471" TableCardinality="861820"&gt;
                                              &lt;OutputList&gt;
                                                  &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_A_A]" Alias="[asso]" Column="a2_id" /&gt;
                                              &lt;/OutputList&gt;
                                              &lt;RunTimeInformation&gt;
                                                  &lt;RunTimeCountersPerThread Thread="0" ActualRows="5" ActualEndOfScans="5" ActualExecutions="10" /&gt;
                                              &lt;/RunTimeInformation&gt;
                                              &lt;IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false"&gt;
                                                  &lt;DefinedValues&gt;
                                                      &lt;DefinedValue&gt;
                                                          &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_A_A]" Alias="[asso]" Column="a2_id" /&gt;
                                                      &lt;/DefinedValue&gt;
                                                  &lt;/DefinedValues&gt;
                                                  &lt;Object Database="[DB]" Schema="[DBO]" Table="[Table_A_A]" Index="[PK_A_A]" Alias="[asso]" TableReferenceId="-1" IndexKind="Clustered" /&gt;
                                                  &lt;SeekPredicates&gt;
                                                      &lt;SeekPredicateNew&gt;
                                                          &lt;SeekKeys&gt;
                                                              &lt;Prefix ScanType="EQ"&gt;
                                                                  &lt;RangeColumns&gt;
                                                                      &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_A_A]" Alias="[asso]" Column="id" /&gt;
                                                                  &lt;/RangeColumns&gt;
                                                                  &lt;RangeExpressions&gt;
                                                                      &lt;ScalarOperator ScalarString="[DB].[DBO].[Table_A_A].[id] as [asso].[id]"&gt;
                                                                          &lt;Identifier&gt;
                                                                              &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_A_A]" Alias="[asso]" Column="id" /&gt;
                                                                          &lt;/Identifier&gt;
                                                                      &lt;/ScalarOperator&gt;
                                                                  &lt;/RangeExpressions&gt;
                                                              &lt;/Prefix&gt;
                                                          &lt;/SeekKeys&gt;
                                                      &lt;/SeekPredicateNew&gt;
                                                  &lt;/SeekPredicates&gt;
                                                  &lt;Predicate&gt;
                                                      &lt;ScalarOperator ScalarString="[DB].[DBO].[Table_A_A].[a_a_type] as [asso].[a_a_type]='P'"&gt;
                                                          &lt;Compare CompareOp="EQ"&gt;
                                                              &lt;ScalarOperator&gt;
                                                                  &lt;Identifier&gt;
                                                                      &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_A_A]" Alias="[asso]" Column="a_a_type" /&gt;
                                                                  &lt;/Identifier&gt;
                                                              &lt;/ScalarOperator&gt;
                                                              &lt;ScalarOperator&gt;
                                                                  &lt;Const ConstValue="'P'" /&gt;
                                                              &lt;/ScalarOperator&gt;
                                                          &lt;/Compare&gt;
                                                      &lt;/ScalarOperator&gt;
                                                  &lt;/Predicate&gt;
                                              &lt;/IndexScan&gt;
                                          &lt;/RelOp&gt;
                                      &lt;/NestedLoops&gt;
                                  &lt;/RelOp&gt;
                                  &lt;RelOp AvgRowSize="23" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="84" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="215"&gt;
                                      &lt;OutputList&gt;
                                          &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_A_O]" Alias="[ao]" Column="o_v_id" /&gt;
                                      &lt;/OutputList&gt;
                                      &lt;RunTimeInformation&gt;
                                          &lt;RunTimeCountersPerThread Thread="0" ActualRows="5" ActualEndOfScans="0" ActualExecutions="5" /&gt;
                                      &lt;/RunTimeInformation&gt;
                                      &lt;IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false"&gt;
                                          &lt;DefinedValues&gt;
                                              &lt;DefinedValue&gt;
                                                  &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_A_O]" Alias="[ao]" Column="o_v_id" /&gt;
                                              &lt;/DefinedValue&gt;
                                          &lt;/DefinedValues&gt;
                                          &lt;Object Database="[DB]" Schema="[DBO]" Table="[Table_A_O]" Index="[PK_A_O]" Alias="[ao]" IndexKind="Clustered" /&gt;
                                          &lt;SeekPredicates&gt;
                                              &lt;SeekPredicateNew&gt;
                                                  &lt;SeekKeys&gt;
                                                      &lt;Prefix ScanType="EQ"&gt;
                                                          &lt;RangeColumns&gt;
                                                              &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_A_O]" Alias="[ao]" Column="ID" /&gt;
                                                          &lt;/RangeColumns&gt;
                                                          &lt;RangeExpressions&gt;
                                                              &lt;ScalarOperator ScalarString="[DB].[DBO].[Table_A_A].[a2_id] as [asso].[a2_id]"&gt;
                                                                  &lt;Identifier&gt;
                                                                      &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_A_A]" Alias="[asso]" Column="a2_id" /&gt;
                                                                  &lt;/Identifier&gt;
                                                              &lt;/ScalarOperator&gt;
                                                          &lt;/RangeExpressions&gt;
                                                      &lt;/Prefix&gt;
                                                  &lt;/SeekKeys&gt;
                                              &lt;/SeekPredicateNew&gt;
                                          &lt;/SeekPredicates&gt;
                                      &lt;/IndexScan&gt;
                                  &lt;/RelOp&gt;
                              &lt;/NestedLoops&gt;
                          &lt;/RelOp&gt;
                          &lt;RelOp AvgRowSize="23" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="85" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="92"&gt;
                              &lt;OutputList&gt;
                                  &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_O_V]" Alias="[ov]" Column="o_id" /&gt;
                              &lt;/OutputList&gt;
                              &lt;RunTimeInformation&gt;
                                  &lt;RunTimeCountersPerThread Thread="0" ActualRows="5" ActualEndOfScans="0" ActualExecutions="5" /&gt;
                              &lt;/RunTimeInformation&gt;
                              &lt;IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false"&gt;
                                  &lt;DefinedValues&gt;
                                      &lt;DefinedValue&gt;
                                          &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_O_V]" Alias="[ov]" Column="o_id" /&gt;
                                      &lt;/DefinedValue&gt;
                                  &lt;/DefinedValues&gt;
                                  &lt;Object Database="[DB]" Schema="[DBO]" Table="[Table_O_V]" Index="[PK_O_V]" Alias="[ov]" IndexKind="Clustered" /&gt;
                                  &lt;SeekPredicates&gt;
                                      &lt;SeekPredicateNew&gt;
                                          &lt;SeekKeys&gt;
                                              &lt;Prefix ScanType="EQ"&gt;
                                                  &lt;RangeColumns&gt;
                                                      &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_O_V]" Alias="[ov]" Column="id" /&gt;
                                                  &lt;/RangeColumns&gt;
                                                  &lt;RangeExpressions&gt;
                                                      &lt;ScalarOperator ScalarString="[DB].[DBO].[Table_A_O].[o_v_id] as [ao].[o_v_id]"&gt;
                                                          &lt;Identifier&gt;
                                                              &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_A_O]" Alias="[ao]" Column="o_v_id" /&gt;
                                                          &lt;/Identifier&gt;
                                                      &lt;/ScalarOperator&gt;
                                                  &lt;/RangeExpressions&gt;
                                              &lt;/Prefix&gt;
                                          &lt;/SeekKeys&gt;
                                      &lt;/SeekPredicateNew&gt;
                                  &lt;/SeekPredicates&gt;
                              &lt;/IndexScan&gt;
                          &lt;/RelOp&gt;
                      &lt;/QueryPlan&gt;
                  &lt;/StmtSimple&gt;
              &lt;/Statements&gt;
          &lt;/Batch&gt;
          &lt;Batch&gt;
              &lt;Statements&gt;
                  &lt;StmtSimple StatementCompId="1" StatementEstRows="21366.7" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="23362.2" StatementText="SELECT *;" StatementType="SELECT" QueryHash="0x66605DE9B339CF45" QueryPlanHash="0xD286887E2ADC3D77"&gt;
                      &lt;StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" /&gt;
                      &lt;QueryPlan DegreeOfParallelism="1" MemoryGrant="2496" CachedPlanSize="176" CompileTime="45" CompileCPU="45" CompileMemory="2896"&gt;
                          &lt;RelOp AvgRowSize="23" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="21365.7" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="75" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="105.672"&gt;
                              &lt;OutputList&gt;
                                  &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_A_O]" Alias="[ao]" Column="o_v_id" /&gt;
                              &lt;/OutputList&gt;
                              &lt;RunTimeInformation&gt;
                                  &lt;RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" /&gt;
                              &lt;/RunTimeInformation&gt;
                              &lt;NestedLoops Optimized="false"&gt;
                                  &lt;OuterReferences&gt;
                                      &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_A_A]" Alias="[asso]" Column="a2_id" /&gt;
                                  &lt;/OuterReferences&gt;
                                  &lt;RelOp AvgRowSize="23" EstimateCPU="0.862078" EstimateIO="50.2091" EstimateRebinds="21365.7" EstimateRewinds="0" EstimateRows="1" LogicalOp="Eager Spool" NodeId="76" Parallel="false" PhysicalOp="Index Spool" EstimatedTotalSubtreeCost="102.198"&gt;
                                      &lt;OutputList&gt;
                                          &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_A_A]" Alias="[asso]" Column="a2_id" /&gt;
                                      &lt;/OutputList&gt;
                                      &lt;RunTimeInformation&gt;
                                          &lt;RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" /&gt;
                                      &lt;/RunTimeInformation&gt;
                                      &lt;Spool&gt;
                                          &lt;SeekPredicateNew&gt;
                                              &lt;SeekKeys&gt;
                                                  &lt;Prefix ScanType="EQ"&gt;
                                                      &lt;RangeColumns&gt;
                                                          &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_A_A]" Alias="[asso]" Column="a1_id" /&gt;
                                                          &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_A_A]" Alias="[asso]" Column="a_a_type" /&gt;
                                                      &lt;/RangeColumns&gt;
                                                      &lt;RangeExpressions&gt;
                                                          &lt;ScalarOperator ScalarString="[DB].[DBO].[Table_A].[id] as [a].[id]"&gt;
                                                              &lt;Identifier&gt;
                                                                  &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_A]" Alias="[a]" Column="id" /&gt;
                                                              &lt;/Identifier&gt;
                                                          &lt;/ScalarOperator&gt;
                                                          &lt;ScalarOperator ScalarString="'P'"&gt;
                                                              &lt;Const ConstValue="'P'" /&gt;
                                                          &lt;/ScalarOperator&gt;
                                                      &lt;/RangeExpressions&gt;
                                                  &lt;/Prefix&gt;
                                              &lt;/SeekKeys&gt;
                                          &lt;/SeekPredicateNew&gt;
                                          &lt;RelOp AvgRowSize="61" EstimateCPU="0.948159" EstimateIO="22.7579" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="861820" LogicalOp="Clustered Index Scan" NodeId="77" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="23.7061" TableCardinality="861820"&gt;
                                              &lt;OutputList&gt;
                                                  &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_A_A]" Alias="[asso]" Column="a1_id" /&gt;
                                                  &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_A_A]" Alias="[asso]" Column="a2_id" /&gt;
                                                  &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_A_A]" Alias="[asso]" Column="a_a_type" /&gt;
                                              &lt;/OutputList&gt;
                                              &lt;RunTimeInformation&gt;
                                                  &lt;RunTimeCountersPerThread Thread="0" ActualRows="861820" ActualEndOfScans="1" ActualExecutions="1" /&gt;
                                              &lt;/RunTimeInformation&gt;
                                              &lt;IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false"&gt;
                                                  &lt;DefinedValues&gt;
                                                      &lt;DefinedValue&gt;
                                                          &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_A_A]" Alias="[asso]" Column="a1_id" /&gt;
                                                      &lt;/DefinedValue&gt;
                                                      &lt;DefinedValue&gt;
                                                          &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_A_A]" Alias="[asso]" Column="a2_id" /&gt;
                                                      &lt;/DefinedValue&gt;
                                                      &lt;DefinedValue&gt;
                                                          &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_A_A]" Alias="[asso]" Column="a_a_type" /&gt;
                                                      &lt;/DefinedValue&gt;
                                                  &lt;/DefinedValues&gt;
                                                  &lt;Object Database="[DB]" Schema="[DBO]" Table="[Table_A_A]" Index="[PK_A_A]" Alias="[asso]" IndexKind="Clustered" /&gt;
                                              &lt;/IndexScan&gt;
                                          &lt;/RelOp&gt;
                                      &lt;/Spool&gt;
                                  &lt;/RelOp&gt;
                                  &lt;RelOp AvgRowSize="23" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="21365.7" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="78" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="3.38433" TableCardinality="215"&gt;
                                      &lt;OutputList&gt;
                                          &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_A_O]" Alias="[ao]" Column="o_v_id" /&gt;
                                      &lt;/OutputList&gt;
                                      &lt;RunTimeInformation&gt;
                                          &lt;RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="0" ActualExecutions="1" /&gt;
                                      &lt;/RunTimeInformation&gt;
                                      &lt;IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false"&gt;
                                          &lt;DefinedValues&gt;
                                              &lt;DefinedValue&gt;
                                                  &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_A_O]" Alias="[ao]" Column="o_v_id" /&gt;
                                              &lt;/DefinedValue&gt;
                                          &lt;/DefinedValues&gt;
                                          &lt;Object Database="[DB]" Schema="[DBO]" Table="[Table_A_O]" Index="[PK_A_O]" Alias="[ao]" IndexKind="Clustered" /&gt;
                                          &lt;SeekPredicates&gt;
                                              &lt;SeekPredicateNew&gt;
                                                  &lt;SeekKeys&gt;
                                                      &lt;Prefix ScanType="EQ"&gt;
                                                          &lt;RangeColumns&gt;
                                                              &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_A_O]" Alias="[ao]" Column="ID" /&gt;
                                                          &lt;/RangeColumns&gt;
                                                          &lt;RangeExpressions&gt;
                                                              &lt;ScalarOperator ScalarString="[DB].[DBO].[Table_A_A].[a2_id] as [asso].[a2_id]"&gt;
                                                                  &lt;Identifier&gt;
                                                                      &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_A_A]" Alias="[asso]" Column="a2_id" /&gt;
                                                                  &lt;/Identifier&gt;
                                                              &lt;/ScalarOperator&gt;
                                                          &lt;/RangeExpressions&gt;
                                                      &lt;/Prefix&gt;
                                                  &lt;/SeekKeys&gt;
                                              &lt;/SeekPredicateNew&gt;
                                          &lt;/SeekPredicates&gt;
                                      &lt;/IndexScan&gt;
                                  &lt;/RelOp&gt;
                              &lt;/NestedLoops&gt;
                          &lt;/RelOp&gt;
                          &lt;RelOp AvgRowSize="23" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="21365.7" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="79" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="3.3937" TableCardinality="92"&gt;
                              &lt;OutputList&gt;
                                  &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_O_V]" Alias="[ov]" Column="o_id" /&gt;
                              &lt;/OutputList&gt;
                              &lt;RunTimeInformation&gt;
                                  &lt;RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="0" ActualExecutions="1" /&gt;
                              &lt;/RunTimeInformation&gt;
                              &lt;IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false"&gt;
                                  &lt;DefinedValues&gt;
                                      &lt;DefinedValue&gt;
                                          &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_O_V]" Alias="[ov]" Column="o_id" /&gt;
                                      &lt;/DefinedValue&gt;
                                  &lt;/DefinedValues&gt;
                                  &lt;Object Database="[DB]" Schema="[DBO]" Table="[Table_O_V]" Index="[PK_O_V]" Alias="[ov]" IndexKind="Clustered" /&gt;
                                  &lt;SeekPredicates&gt;
                                      &lt;SeekPredicateNew&gt;
                                          &lt;SeekKeys&gt;
                                              &lt;Prefix ScanType="EQ"&gt;
                                                  &lt;RangeColumns&gt;
                                                      &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_O_V]" Alias="[ov]" Column="id" /&gt;
                                                  &lt;/RangeColumns&gt;
                                                  &lt;RangeExpressions&gt;
                                                      &lt;ScalarOperator ScalarString="[DB].[DBO].[Table_A_O].[o_v_id] as [ao].[o_v_id]"&gt;
                                                          &lt;Identifier&gt;
                                                              &lt;ColumnReference Database="[DB]" Schema="[DBO]" Table="[Table_A_O]" Alias="[ao]" Column="o_v_id" /&gt;
                                                          &lt;/Identifier&gt;
                                                      &lt;/ScalarOperator&gt;
                                                  &lt;/RangeExpressions&gt;
                                              &lt;/Prefix&gt;
                                          &lt;/SeekKeys&gt;
                                      &lt;/SeekPredicateNew&gt;
                                  &lt;/SeekPredicates&gt;
                              &lt;/IndexScan&gt;
                          &lt;/RelOp&gt;
                      &lt;/QueryPlan&gt;
                  &lt;/StmtSimple&gt;
              &lt;/Statements&gt;
          &lt;/Batch&gt;
      &lt;/BatchSequence&gt;
  &lt;/ShowPlanXML&gt;
  </code></pre>