• Artikel

Verwenden von fehlenden Indexinformationen zum Schreiben von CREATE INDEX-Anweisungen

Dieses Thema enthält Richtlinien und Beispiele für das Verwenden der von Komponenten des Features für fehlende Indizes zurückgegebenen Informationen, um CREATE INDEX-Anweisungen für die fehlenden Indizes zu schreiben.

Anordnen von Spalten in CREATE INDEX-Anweisungen

Komponenten des Features für fehlende Indizes listen Spalten für Gleichheit oder Ungleichheit sowie eingeschlossene Spalten in ihrer Ausgabe auf.

So gibt beispielsweise das XML-Showplanelement MissingIndexes an, ob eine Indexschlüsselspalte für Gleichheit (=) oder Ungleichheit (<, > usw.) im Transact-SQL-Anweisungsprädikat verwendet oder nur zum Abdecken einer Abfrage eingeschlossen wird. Diese Informationen werden als einer der folgenden Werte für das Usage-Attribut des ColumnGroup-Unterelements dargestellt:

<ColumnGroup Usage="EQUALITY">

<ColumnGroup Usage="INEQUALITY">

<ColumnGroup Usage="INCLUDE">

Die dynamischen Verwaltungsobjekte sys.dm_db_missing_index_details und sys.dm_db_missing_index_columns geben Ergebnisse zurück, die angeben, ob eine Indexschlüsselspalte eine Spalte für Gleichheit oder Ungleichheit oder eine eingeschlossene Spalte ist. Das Resultset von sys.dm_db_missing_index_details gibt diese Informationen in den Spalten equality_columns, inequality_columns und included_columns zurück. Das von sys.dm_db_missing_index_columns zurückgegebene Resultset gibt diese Informationen in der column_usage-Spalte zurück.

Beachten Sie die folgenden Richtlinien beim Anordnen von Spalten in den CREATE INDEX-Anweisungen, die Sie anhand der Ausgabe der Komponenten des Features für fehlende Indizes schreiben:

  • Listen Sie die Spalten für Gleichheit zuerst auf (äußerst links in der Spaltenliste).

  • Listen Sie die Spalten für Ungleichheit nach den Spalten für Gleichheit auf (rechts von den aufgelisteten Spalten für Gleichheit).

  • Listen Sie die eingeschlossenen Spalten in der INCLUDE-Klausel der CREATE INDEX-Anweisung auf.

  • Ordnen Sie die Spalten für Gleichheit anhand ihrer Selektivität an, um eine sinnvolle Reihenfolge zu bestimmen. Listen Sie also die selektivsten Spalten zuerst auf.

Beispiele

Verwenden der Ausgabe des XML-Showplanelements MissingIndexes

Das Feature für fehlende Indizes nutzt Informationen, die der Abfrageoptimierer automatisch beim Optimieren einer Abfrage generiert. Abfragen müssen jedoch zuerst in der SQL Server-Instanz ausgeführt werden, damit der Optimierer diese fehlende Indexinformation generieren kann.

Im folgenden Beispiel wird das Erstellen einer DDL-Anweisung (Data Definition Language) anhand der vom MissingIndexes-Element zurückgegebenen Informationen dargestellt:

  1. Aktivieren Sie das XML-Showplanfeature mithilfe der Option SET STATISTICS XML ON, und führen Sie die folgende Abfrage für die AdventureWorks-Beispieldatenbank aus:

    USE AdventureWorks;
GO
SET STATISTICS XML ON;
GO
SELECT CustomerID, SalesOrderNumber, SubTotal
FROM Sales.SalesOrderHeader
WHERE ShipMethodID > 2
AND SubTotal > 500.00
AND Freight < 15.00
AND TerritoryID = 5;
GO

  2. Zeigen Sie die zurückgegebene Ausgabe im MissingIndexes-Element des erstellten Showplans an:

    <MissingIndexes>

      <MissingIndexGroup Impact="95.8296">

        <MissingIndex Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]">

          <ColumnGroup Usage="EQUALITY">

            <Column Name="[TerritoryID]" ColumnId="14" />

          </ColumnGroup>

          <ColumnGroup Usage="INEQUALITY">

            <Column Name="[ShipMethodID]" ColumnId="17" />

            <Column Name="[SubTotal]" ColumnId="21" />

            <Column Name="[Freight]" ColumnId="23" />

          </ColumnGroup>

          <ColumnGroup Usage="INCLUDE">

            <Column Name="[SalesOrderNumber]" ColumnId="8" />

            <Column Name="[CustomerID]" ColumnId="11" />

          </ColumnGroup>

        </MissingIndex>

      </MissingIndexGroup>

    </MissingIndexes>

  3. Erstellen Sie den fehlenden Index mithilfe der in den Elementen MissingIndex und ColumnGroup zurückgegebenen Informationen, um eine CREATE INDEX DDL-Anweisung folgendermaßen zu schreiben:

    USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
           WHERE name = N'IX_SalesOrderHeader_TerritoryID')
     DROP INDEX IX_SalesOrderHeader_TerritoryID ON Sales.SalesOrderHeader;
GO
CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_TerritoryID
     ON Sales.SalesOrderHeader (TerritoryID, ShipMethodID, SubTotal, Freight)
     INCLUDE (SalesOrderNumber, CustomerID);
GO

    Diese CREATE INDEX-Anweisung verwendet den im MissingIndex-Element aufgelisteten Namen der Datenbank (USE AdventureWorks), des Schemas und der Tabelle (ON Sales.SalesOrderHeader). Darüber hinaus verwendet sie die für jedes ColumnGroup-Unterelement für die Schlüsselspalten (TerritoryID, ShipMethodID, SubTotal, Freight) und Nichtschlüsselspalten (INCLUDE (SalesOrderNumber, CustomerID)) aufgelisteten Spalten.

Verwenden der von einem dynamischen Verwaltungsobjekt zurückgegebenen Ergebnisse

Sie können fehlende Indexinformationen erst abrufen, nachdem Sie Abfragen in der SQL Server-Instanz ausgeführt haben, damit der Abfrageoptimierer die fehlenden Indexinformationen generieren kann.

Im folgenden Beispiel wird das Erstellen einer DDL-Anweisung anhand der Informationen von der dynamischen Verwaltungssicht sys.dm_db_missing_index_details dargestellt.

  1. Führen Sie die folgende Abfrage für die AdventureWorks-Beispieldatenbank aus:

    USE AdventureWorks;
GO
SELECT City, StateProvinceID, PostalCode
FROM Person.Address
WHERE StateProvinceID = 9;
GO

  2. Führen Sie die folgenden Abfrage für die dynamische Verwaltungssicht sys.dm_db_missing_index_details aus:

    SELECT *
FROM sys.dm_db_missing_index_details

    Die Abfrage dieser dynamischen Verwaltungssicht gibt die folgenden Ergebnisse zurück:

index_handle

database_id

object_id

equality_columns

inequality_columns

included_columns

statement

1

6

53575229

[StateProvince]

NULL

[City], [PostalCode]

[AdventureWorks].[Person].[Address]

  1. Nach der Abfrage der dynamischen Verwaltungssicht sys.dm_db_missing_index_details können Sie den fehlenden Index anhand der in den Spalten equality_columns, included_columns und statement zurückgegebenen Informationen folgendermaßen erstellen:

    USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
           WHERE name = N'IX_PersonAddress_StateProvinceID')
     DROP INDEX IX_PersonAddress_StateProvinceID ON Person.Address;
GO
CREATE NONCLUSTERED INDEX IX_PersonAddress_StateProvinceID
     ON Person.Address (StateProvinceID)
     INCLUDE (City, PostalCode);
GO

In diesem Beispiel wurden keine Spalten für Ungleichheit (inequality_columns) im Resultset von sys.dm_db_missing_index_details zurückgegeben. Wären sie vorhanden, würden diese Spalten nach den Spalten für Gleichheit (equality_columns) aufgelistet. Spalten, die in included_columns zurückgegeben werden, werden immer in der INCLUDE-Klausel der CREATE INDEX-Anweisung aufgelistet.