Plan Forcing Scenario: Use USE PLAN to Force a JOIN Algorithm

This example forces SQL Server to use a plan that specifies using the inner join logical operator and merge join physical operator for the query.

Example

USE tempdb;
GO
CREATE TABLE t1(i INT)
CREATE TABLE t2(j INT)
GO
SELECT * FROM t1, t2 WHERE t1.i = t2.j OPTION (USE PLAN N'
<ShowPlanXML xmlns="https://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="0.5" Build="9.00.938">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementText="SELECT * FROM t1, t2 WHERE t1.i = t2.j" StatementId="1" 
         StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="0.0514796" 
         StatementEstRows="1" StatementOptmLevel="FULL">
          <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" 
           ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
          <QueryPlan CachedPlanSize="20">
            <RelOp NodeId="0" PhysicalOp="Merge Join" LogicalOp="Inner Join" EstimateRows="1" 
             EstimateIO="0.000313" EstimateCPU="0.00564738" AvgRowSize="15" 
             EstimatedTotalSubtreeCost="0.0514796" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
              <OutputList>
                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t1]" Column="i" />
                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t2]" Column="j" />
              </OutputList>
              <Merge ManyToMany="1">
                <InnerSideJoinColumns>
                  <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t2]" Column="j" />
                </InnerSideJoinColumns>
                <OuterSideJoinColumns>
                  <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t1]" Column="i" />
                </OuterSideJoinColumns>
                <Residual>
                  <ScalarOperator ScalarString="[tempdb].[dbo].[t2].[j]=[tempdb].[dbo].[t1].[i]">
                    <Compare CompareOp="EQ">
                      <ScalarOperator>
                        <Identifier>
                          <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t2]" Column="j" />
                        </Identifier>
                      </ScalarOperator>
                      <ScalarOperator>
                        <Identifier>
                          <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t1]" Column="i" />
                        </Identifier>
                      </ScalarOperator>
                    </Compare>
                  </ScalarOperator>
                </Residual>
                <RelOp NodeId="1" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="1" EstimateIO="0.01625" 
                 EstimateCPU="0.000100011" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0227581" Parallel="0" 
                 EstimateRebinds="0" EstimateRewinds="0">
                  <OutputList>
                    <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t1]" Column="i" />
                  </OutputList>
                  <MemoryFractions Input="1" Output="0.5" />
                  <Sort Distinct="0">
                    <OrderBy>
                      <OrderByColumn Ascending="1">
                        <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t1]" Column="i" />
                      </OrderByColumn>
                    </OrderBy>
                    <RelOp NodeId="2" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="1"
                     EstimateIO="0.0063285" EstimateCPU="7.96e-005" AvgRowSize="11" 
                     EstimatedTotalSubtreeCost="0.0064081" Parallel="0" EstimateRebinds="0" 
                     EstimateRewinds="0">
                      <OutputList>
                        <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t1]" Column="i" />
                      </OutputList>
                      <TableScan Ordered="0" ForcedIndex="0">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t1]" Column="i" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[tempdb]" Schema="[dbo]" Table="[t1]" />
                      </TableScan>
                    </RelOp>
                  </Sort>
                </RelOp>
                <RelOp NodeId="3" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="1" 
                 EstimateIO="0.01625" EstimateCPU="0.000100011" AvgRowSize="11" 
                 EstimatedTotalSubtreeCost="0.0227581" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                  <OutputList>
                    <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t2]" Column="j" />
                  </OutputList>
                  <MemoryFractions Input="0.5" Output="0.5" />
                  <Sort Distinct="0">
                    <OrderBy>
                      <OrderByColumn Ascending="1">
                        <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t2]" Column="j" />
                      </OrderByColumn>
                    </OrderBy>
                    <RelOp NodeId="4" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="1" 
                     EstimateIO="0.0063285" EstimateCPU="7.96e-005" AvgRowSize="11" 
                     EstimatedTotalSubtreeCost="0.0064081" Parallel="0" EstimateRebinds="0" 
                     EstimateRewinds="0">
                      <OutputList>
                        <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t2]" Column="j" />
                      </OutputList>
                      <TableScan Ordered="0" ForcedIndex="0">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t2]" Column="j" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[tempdb]" Schema="[dbo]" Table="[t2]" />
                      </TableScan>
                    </RelOp>
                  </Sort>
                </RelOp>
              </Merge>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>')

See Also

Concepts

Plan Forcing Scenarios and Examples
Specifying Query Plans with Plan Forcing

Other Resources

Query Performance

Help and Information

Getting SQL Server 2005 Assistance