Freigeben über

Szenario zum Erzwingen eines Planes: Verwenden von USE PLAN zum Erzwingen eines Indexvereinigungsalgorithmus

Aktualisiert: 05. Dezember 2005

Im folgenden Beispiel wird die Verwendung eines Plans in SQL Server erzwungen, der eine Vereinigung von Indizes als Lösung für das Ausführen einer Abfrage angibt.

Beispiel

USE tempdb;
GO
IF OBJECT_ID('t') IS NOT NULL
    DROP TABLE t
GO
CREATE TABLE t(a int, b int, c int, d int, e int, f int, g char(1000))
CREATE CLUSTERED INDEX cidx ON t(a, b, c, d, e, f)
CREATE INDEX idx2 ON t(c)
CREATE INDEX idx3 ON t(d)
GO
INSERT t VALUES(1, 500, 1, 1, 1, 1, 1)
INSERT t VALUES(10, 500, 10, 10, 10, 10, 10)
INSERT t VALUES(999, 500, 999, 999, 999, 999, 999)
GO
SELECT * FROM t WHERE b > 100 AND (c = 999 OR d = 10) 
OPTION (USE PLAN N'<ShowPlanXML xmlns="https://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.0" Build="9.00.1352.00">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementText="SELECT * FROM t WHERE b &gt; 100 AND (c = 999 OR d = 10)" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="0.0172516" StatementEstRows="2.03388" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound">
          <StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false" />
          <QueryPlan CachedPlanSize="24">
            <RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="2.03388" EstimateIO="0" EstimateCPU="8.50162e-006" AvgRowSize="1031" EstimatedTotalSubtreeCost="0.0172516" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
              <OutputList>
                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="a" />
                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="e" />
                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="f" />
                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="g" />
              </OutputList>
              <NestedLoops Optimized="1">
                <OuterReferences>
                  <ColumnReference Column="Uniq1002" />
                  <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="a" />
                  <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
                  <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
                  <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
                  <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="e" />
                  <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="f" />
                </OuterReferences>
                <RelOp NodeId="2" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="2.03388" EstimateIO="0" EstimateCPU="2.03393e-006" AvgRowSize="35" EstimatedTotalSubtreeCost="0.0121717" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                  <OutputList>
                    <ColumnReference Column="Uniq1002" />
                    <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="a" />
                    <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
                    <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
                    <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
                    <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="e" />
                    <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="f" />
                  </OutputList>
                  <StreamAggregate>
                    <DefinedValues />
                    <GroupBy>
                      <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="a" />
                      <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
                      <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
                      <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
                      <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="e" />
                      <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="f" />
                      <ColumnReference Column="Uniq1002" />
                    </GroupBy>
                    <RelOp NodeId="3" PhysicalOp="Merge Join" LogicalOp="Concatenation" EstimateRows="2.03399" EstimateIO="0" EstimateCPU="0.00560238" AvgRowSize="35" EstimatedTotalSubtreeCost="0.0121696" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                      <OutputList>
                        <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="a" />
                        <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
                        <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
                        <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
                        <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="e" />
                        <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="f" />
                        <ColumnReference Column="Uniq1002" />
                      </OutputList>
                      <Merge>
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="a" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="a" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="a" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="e" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="e" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="e" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="f" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="f" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="f" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Column="Uniq1002" />
                            <ColumnReference Column="Uniq1002" />
                            <ColumnReference Column="Uniq1002" />
                          </DefinedValue>
                        </DefinedValues>
                        <RelOp NodeId="4" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1.01699" EstimateIO="0.003125" EstimateCPU="0.00015813" AvgRowSize="35" EstimatedTotalSubtreeCost="0.00328313" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                          <OutputList>
                            <ColumnReference Column="Uniq1002" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="a" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="e" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="f" />
                          </OutputList>
                          <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" NoExpandHint="0">
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Column="Uniq1002" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="a" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="e" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="f" />
                              </DefinedValue>
                            </DefinedValues>
                            <Object Database="[tempdb]" Schema="[dbo]" Table="[t]" Index="[idx2]" />
                            <SeekPredicates>
                              <SeekPredicate>
                                <Prefix ScanType="EQ">
                                  <RangeColumns>
                                    <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
                                  </RangeColumns>
                                  <RangeExpressions>
                                    <ScalarOperator ScalarString="(999)">
                                      <Const ConstValue="(999)" />
                                    </ScalarOperator>
                                  </RangeExpressions>
                                </Prefix>
                              </SeekPredicate>
                            </SeekPredicates>
                            <Predicate>
                              <ScalarOperator ScalarString="[tempdb].[dbo].[t].[b]&gt;(100)">
                                <Compare CompareOp="GT">
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Const ConstValue="(100)" />
                                  </ScalarOperator>
                                </Compare>
                              </ScalarOperator>
                            </Predicate>
                          </IndexScan>
                        </RelOp>
                        <RelOp NodeId="5" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1.01699" EstimateIO="0.003125" EstimateCPU="0.00015813" AvgRowSize="35" EstimatedTotalSubtreeCost="0.00328313" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                          <OutputList>
                            <ColumnReference Column="Uniq1002" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="a" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="e" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="f" />
                          </OutputList>
                          <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" NoExpandHint="0">
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Column="Uniq1002" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="a" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="e" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="f" />
                              </DefinedValue>
                            </DefinedValues>
                            <Object Database="[tempdb]" Schema="[dbo]" Table="[t]" Index="[idx3]" />
                            <SeekPredicates>
                              <SeekPredicate>
                                <Prefix ScanType="EQ">
                                  <RangeColumns>
                                    <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
                                  </RangeColumns>
                                  <RangeExpressions>
                                    <ScalarOperator ScalarString="(10)">
                                      <Const ConstValue="(10)" />
                                    </ScalarOperator>
                                  </RangeExpressions>
                                </Prefix>
                              </SeekPredicate>
                            </SeekPredicates>
                            <Predicate>
                              <ScalarOperator ScalarString="[tempdb].[dbo].[t].[b]&gt;(100)">
                                <Compare CompareOp="GT">
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Const ConstValue="(100)" />
                                  </ScalarOperator>
                                </Compare>
                              </ScalarOperator>
                            </Predicate>
                          </IndexScan>
                        </RelOp>
                      </Merge>
                    </RelOp>
                  </StreamAggregate>
                </RelOp>
                <RelOp NodeId="8" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="1007" EstimatedTotalSubtreeCost="0.00507144" Parallel="0" EstimateRebinds="1.03388" EstimateRewinds="0">
                  <OutputList>
                    <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="g" />
                  </OutputList>
                  <IndexScan Lookup="1" Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" NoExpandHint="0">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="g" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[tempdb]" Schema="[dbo]" Table="[t]" Index="[cidx]" TableReferenceId="-1" />
                    <SeekPredicates>
                      <SeekPredicate>
                        <Prefix ScanType="EQ">
                          <RangeColumns>
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="a" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="e" />
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="f" />
                            <ColumnReference Column="Uniq1002" />
                          </RangeColumns>
                          <RangeExpressions>
                            <ScalarOperator ScalarString="[tempdb].[dbo].[t].[a]">
                              <Identifier>
                                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="a" />
                              </Identifier>
                            </ScalarOperator>
                            <ScalarOperator ScalarString="[tempdb].[dbo].[t].[b]">
                              <Identifier>
                                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
                              </Identifier>
                            </ScalarOperator>
                            <ScalarOperator ScalarString="[tempdb].[dbo].[t].[c]">
                              <Identifier>
                                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
                              </Identifier>
                            </ScalarOperator>
                            <ScalarOperator ScalarString="[tempdb].[dbo].[t].[d]">
                              <Identifier>
                                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
                              </Identifier>
                            </ScalarOperator>
                            <ScalarOperator ScalarString="[tempdb].[dbo].[t].[e]">
                              <Identifier>
                                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="e" />
                              </Identifier>
                            </ScalarOperator>
                            <ScalarOperator ScalarString="[tempdb].[dbo].[t].[f]">
                              <Identifier>
                                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="f" />
                              </Identifier>
                            </ScalarOperator>
                            <ScalarOperator ScalarString="[Uniq1002]">
                              <Identifier>
                                <ColumnReference Column="Uniq1002" />
                              </Identifier>
                            </ScalarOperator>
                          </RangeExpressions>
                        </Prefix>
                      </SeekPredicate>
                    </SeekPredicates>
                  </IndexScan>
                </RelOp>
              </NestedLoops>
            </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

Änderungsverlauf

Version Verlauf

05. Dezember 2005
Geänderter Inhalt:
  • Aktualisiertes Beispiel.
  • Last updated on