Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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