Szenario zum Erzwingen eines Planes: Verwenden von USE PLAN zum Erzwingen eines JOIN-Algorithmus

Dieses Beispiel zwingt SQL Server, einen Plan zu verwenden, der die Verwendung des logischen Operators Inner Join und des physikalischen Operators Merge Join für die Abfrage angibt.

Beispiel

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>')

Siehe auch

Konzepte

Szenarien und Beispiele zur Erzwingung des Planes
Angeben von Abfrageplänen mit Planerzwingung

Andere Ressourcen

Abfrageleistung

Hilfe und Informationen

Informationsquellen für SQL Server 2005