• Article

Utilisation des informations d'index manquant pour l'écriture d'instructions CREATE INDEX

Cette rubrique contient des consignes et des exemples sur l'utilisation des informations qui sont retournées par les composants de la fonctionnalité d'index manquants afin d'écrire des instructions CREATE INDEX pour les index manquants.

Ordre des colonnes dans les instructions CREATE INDEX

Les composants de la fonctionnalité d'index manquants répertorient l'égalité, l'inégalité et les colonnes incluses dans la sortie.

Par exemple, l'élément Showplan XML MissingIndexes indique si une colonne de clé d'index est utilisée pour l'égalité (=) ou l'inégalité (<, >, etc.) dans le prédicat de l'instruction Transact-SQL ou si elle est simplement incluse pour couvrir une requête. Il affiche cette information sous la forme d'une des valeurs suivantes pour l'attribut Usage du sous-élément ColumnGroup :

<ColumnGroup Usage="EQUALITY">

<ColumnGroup Usage="INEQUALITY">

<ColumnGroup Usage="INCLUDE">

Les objets de gestion dynamique sys.dm_db_missing_index_details et sys.dm_db_missing_index_columns retournent des résultats qui indiquent si une colonne de clé d'index est une colonne d'égalité, d'inégalité ou incluse. Le jeu de résultats de sys.dm_db_missing_index_details retourne cette information dans les colonnes equality_columns, inequality_columns et included_columns. Le jeu de résultats retourné par sys.dm_db_missing_index_columns retourne cette information dans sa colonne column_usage.

Utilisez les consignes ci-dessous pour ordonner les colonnes dans les instructions CREATE INDEX que vous écrivez à partir de la sortie des composants de la fonctionnalité d'index manquants :

  • Commencez par répertorier les colonnes d'égalité (à l'extrême gauche dans la liste des colonnes).

  • Répertoriez les colonnes d'inégalité après les colonnes d'égalité (à droite des colonnes d'égalité répertoriées).

  • Répertoriez les colonnes incluses dans la clause INCLUDE de l'instruction CREATE INDEX.

  • Pour déterminer un ordre efficace des colonnes d'égalité, ordonnez-les en fonction de leur sélectivité ; c'est-à-dire, répertoriez les colonnes les plus sélectives en premier.

Exemples

Utilisation de la sortie de l'élément Showplan XML MissingIndexes

La fonctionnalité d'index manquants tire parti d'informations que l'optimiseur de requête génère automatiquement lorsqu'il optimise une requête. Toutefois, les requêtes doivent être exécutées en premier lieu sur l'instance de SQL Server, de sorte que l'optimiseur puisse générer ces informations d'index manquant.

L'exemple suivant montre comment créer une instruction DDL (Data Definition Language) à partir des informations retournées par l'élément MissingIndexes :

  1. Activez la fonctionnalité Showplan XML en utilisant l'option SET STATISTICS XML ON et exécutez la requête ci-dessous sur l'exemple de base de données AdventureWorks :

    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. Affichez les résultats retournés dans l'élément MissingIndexes du Showplan qui est produit :

    <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. Créez l'index manquant en utilisant les informations retournées dans les éléments MissingIndex et ColumnGroup pour écrire une instruction DDL CREATE INDEX, comme suit :

    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

    Cette instruction CREATE INDEX utilise la base de données (USE AdventureWorks), le schéma et le nom de table (ON Sales.SalesOrderHeader) répertoriés dans l'élément MissingIndex. Elle utilise également les colonnes répertoriées pour chaque sous-élément ColumnGroup des colonnes clés (TerritoryID, ShipMethodID, SubTotal, Freight) et des colonnes non-clés (INCLUDE (SalesOrderNumber, CustomerID)).

Utilisation des résultats retournés par un objet de gestion dynamique

Avant de pouvoir récupérer les informations d'index manquant, vous devez exécuter des requêtes sur l'instance de SQL Server afin que l'optimiseur de requête puisse générer les informations d'index manquant.

L'exemple suivant montre comment créer une instruction DDL à partir des informations retournées par la vue de gestion dynamique sys.dm_db_missing_index_details.

  1. Exécutez la requête ci-dessous sur l'exemple de base de données AdventureWorks :

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

  2. Exécutez la requête ci-dessous sur la vue de gestion dynamique sys.dm_db_missing_index_details :

    SELECT *
FROM sys.dm_db_missing_index_details

    L'exécution d'une requête sur cette vue de gestion dynamique retourne les résultats suivants :

index_handle

database_id

object_id

equality_columns

inequality_columns

included_columns

statement

1

6

53575229

[StateProvince]

NULL

[City], [PostalCode]

[AdventureWorks].[Person].[Address]

  1. Après l'exécution d'une requête sur la vue de gestion dynamique sys.dm_db_missing_index_details, vous pouvez créer l'index manquant en utilisant les informations qui sont retournées dans les colonnes equality_columns, included_columns et statement, comme suit :

    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

Dans cet exemple, aucune colonne inequality_columns n'a été retournée dans le jeu de résultats sys.dm_db_missing_index_details. Dans le cas contraire, vous devriez répertorier ces colonnes après les colonnes equality_columns. Les colonnes qui sont retournées dans included_columns sont toujours répertoriées dans la clause INCLUDE de l'instruction CREATE INDEX.