Parcourir les aide-mémoire · SQL Server

Technologie: SQL ServerCatégorie: IndexationNiveau: IntermédiaireMis à jour

Trouver les index manquants

Utilisez les DMV d’index manquants de SQL Server comme signal de tri, classez les candidats selon leur impact estimé et validez chaque recommandation avant de créer un index.

Sur cette page
  1. Problème
  2. Réponse rapide
  3. Code / commandes
  4. 1. Classer les candidats d’index manquants selon le gain estimé
  5. 2. Afficher le détail brut des index manquants pour une table
  6. 3. Comparer les recommandations d’index manquants aux index existants
  7. 4. Générer un modèle CREATE INDEX à relire
  8. 5. Vérifier l’activité d’écriture sur la table cible
  9. 6. Vérifier quand la fenêtre d’observation actuelle a commencé
  10. Interpréter le résultat
  11. Points à vérifier
  12. Score élevé + nombre de seeks élevé
  13. Nombreuses recommandations pour la même table
  14. Longues listes INCLUDE
  15. Colonnes d’égalité et d’inégalité mélangées
  16. Recommandation apparue une seule fois après un redémarrage récent
  17. Notes de production
  18. Les DMV d’index manquants sont indicatives
  19. Les recommandations sont éphémères
  20. Les recommandations peuvent se recouvrir
  21. Les DMV d’index manquants ne disent pas tout
  22. Les autorisations varient selon la version et la plateforme
  23. La création d’un index peut provoquer des blocages ou consommer beaucoup de ressources
  24. Erreurs fréquentes
  25. 1. Transformer chaque recommandation en instruction CREATE INDEX
  26. 2. Considérer avg_user_impact comme un pourcentage d’amélioration garanti
  27. 3. Ignorer les index existants
  28. 4. Ignorer le coût en écriture
  29. 5. Créer des index couvrants très larges
  30. 6. Utiliser une fenêtre d’observation trop courte
  31. 7. Supposer qu’un index manquant est la cause racine
  32. Étape suivante
  33. Pour aller plus loin

Problème

SQL Server peut enregistrer des suggestions d’index manquants lorsque l’optimiseur estime qu’un autre index pourrait réduire le coût d’une requête.

Ces DMV sont utiles, mais ce sont des recommandations, pas des instructions de conception.

L’objectif est de répondre aux questions suivantes :

  • Quelles suggestions d’index manquants présentent la plus forte valeur estimée ?
  • Quelles requêtes ou quelles tables en sont à l’origine ?
  • Plusieurs suggestions demandent-elles en réalité presque le même index ?
  • Un index existant couvre-t-il déjà l’essentiel de la recommandation ?
  • Quel est le gain probable en lecture par rapport au coût en écriture et en stockage ?

Réponse rapide

Commencez par joindre les DMV d’index manquants et classez les candidats à l’aide du signal de gain estimé par l’optimiseur.

SELECT TOP (50)
    DB_NAME(mid.database_id) AS database_name,
    OBJECT_SCHEMA_NAME(mid.object_id, mid.database_id) AS schema_name,
    OBJECT_NAME(mid.object_id, mid.database_id) AS table_name,

    migs.user_seeks,
    migs.user_scans,
    migs.avg_total_user_cost,
    migs.avg_user_impact,

    CAST(
        migs.user_seeks
        * migs.avg_total_user_cost
        * (migs.avg_user_impact / 100.0)
        AS decimal(18,2)
    ) AS estimated_improvement_score,

    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
    ON mig.index_group_handle = migs.group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
    ON mid.index_handle = mig.index_handle
WHERE
    mid.database_id = DB_ID()
ORDER BY
    estimated_improvement_score DESC;

Utilisez ce résultat pour prioriser l’analyse. Ne transformez pas directement la sortie en instructions CREATE INDEX sans vérifier les index existants, l’importance de la charge de travail, l’ordre des clés et le coût en écriture.

Code / commandes

1. Classer les candidats d’index manquants selon le gain estimé

C’est la meilleure requête pour une première passe.

SELECT TOP (50)
    DB_NAME(mid.database_id) AS database_name,
    OBJECT_SCHEMA_NAME(mid.object_id, mid.database_id) AS schema_name,
    OBJECT_NAME(mid.object_id, mid.database_id) AS table_name,

    migs.unique_compiles,
    migs.user_seeks,
    migs.user_scans,
    migs.last_user_seek,
    migs.last_user_scan,

    migs.avg_total_user_cost,
    migs.avg_user_impact,

    CAST(
        (migs.user_seeks + migs.user_scans)
        * migs.avg_total_user_cost
        * (migs.avg_user_impact / 100.0)
        AS decimal(18,2)
    ) AS estimated_improvement_score,

    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
    ON mig.index_group_handle = migs.group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
    ON mid.index_handle = mig.index_handle
WHERE
    mid.database_id = DB_ID()
ORDER BY
    estimated_improvement_score DESC;

Le score est une heuristique de tri fondée sur le coût et l’impact estimés par l’optimiseur.

Ce n’est pas un gain de performance garanti.


2. Afficher le détail brut des index manquants pour une table

Utilisez cette requête lorsqu’une table apparaît à plusieurs reprises dans le classement.

DECLARE @schema_name sysname = N'dbo';
DECLARE @table_name  sysname = N'YourTable';

SELECT
    mid.index_handle,
    mid.database_id,
    mid.object_id,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    mid.statement
FROM sys.dm_db_missing_index_details AS mid
WHERE
    mid.database_id = DB_ID()
    AND mid.object_id = OBJECT_ID(
        QUOTENAME(@schema_name) + N'.' + QUOTENAME(@table_name)
    )
ORDER BY
    mid.index_handle;

Si vous voyez plusieurs lignes similaires, regroupez-les mentalement avant de concevoir quoi que ce soit.

Par exemple, ces suggestions peuvent largement se recouvrir :

(A, B) INCLUDE (C)
(A, B) INCLUDE (C, D)
(A, B, E) INCLUDE (C)

Créer les trois est rarement la bonne réponse.


3. Comparer les recommandations d’index manquants aux index existants

Avant de créer quoi que ce soit, examinez les définitions d’index actuelles.

DECLARE @schema_name sysname = N'dbo';
DECLARE @table_name  sysname = N'YourTable';

SELECT
    i.index_id,
    i.name AS index_name,
    i.type_desc,
    i.is_unique,
    i.is_primary_key,
    i.is_unique_constraint,
    i.has_filter,
    i.filter_definition,
    ic.key_ordinal,
    ic.is_included_column,
    c.name AS column_name
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
    ON ic.object_id = i.object_id
   AND ic.index_id = i.index_id
INNER JOIN sys.columns AS c
    ON c.object_id = ic.object_id
   AND c.column_id = ic.column_id
WHERE
    i.object_id = OBJECT_ID(
        QUOTENAME(@schema_name) + N'.' + QUOTENAME(@table_name)
    )
    AND i.is_hypothetical = 0
ORDER BY
    i.index_id,
    ic.is_included_column,
    ic.key_ordinal,
    ic.index_column_id;

Vous cherchez :

  • les mêmes colonnes de tête dans la clé
  • des ensembles INCLUDE quasi identiques
  • la même logique de filtre
  • des index qui pourraient éventuellement être élargis plutôt que dupliqués
  • une sémantique d’unicité ou de contrainte qui compte

4. Générer un modèle CREATE INDEX à relire

Cette requête produit un brouillon, pas un script de déploiement automatique.

SELECT TOP (50)
    'CREATE INDEX IX_'
    + REPLACE(
        REPLACE(
            OBJECT_NAME(mid.object_id, mid.database_id),
            ']',
            ''
        ),
        ' ',
        '_'
    )
    + '_MissingIndex'
    + CHAR(13) + CHAR(10)
    + 'ON '
    + QUOTENAME(OBJECT_SCHEMA_NAME(mid.object_id, mid.database_id))
    + '.'
    + QUOTENAME(OBJECT_NAME(mid.object_id, mid.database_id))
    + ' ('
    + COALESCE(mid.equality_columns, '')
    + CASE
        WHEN mid.equality_columns IS NOT NULL
         AND mid.inequality_columns IS NOT NULL
            THEN ', '
        ELSE ''
      END
    + COALESCE(mid.inequality_columns, '')
    + ')'
    + CASE
        WHEN mid.included_columns IS NOT NULL
            THEN ' INCLUDE (' + mid.included_columns + ')'
        ELSE ''
      END
    + ';' AS create_index_draft,

    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
    ON mig.index_group_handle = migs.group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
    ON mid.index_handle = mig.index_handle
WHERE
    mid.database_id = DB_ID()
ORDER BY
    (
        migs.user_seeks
        * migs.avg_total_user_cost
        * (migs.avg_user_impact / 100.0)
    ) DESC;

Relisez manuellement le SQL généré.

Les modifications habituelles avant déploiement peuvent consister à :

  • choisir un vrai nom d’index
  • supprimer les colonnes INCLUDE redondantes
  • modifier l’ordre des clés
  • fusionner plusieurs recommandations similaires
  • envisager un index filtré
  • décider de ne pas créer l’index du tout

5. Vérifier l’activité d’écriture sur la table cible

Un index optimisé pour la lecture peut pénaliser une table fortement modifiée.

DECLARE @schema_name sysname = N'dbo';
DECLARE @table_name  sysname = N'YourTable';

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    i.name AS index_name,
    COALESCE(us.user_seeks, 0) AS user_seeks,
    COALESCE(us.user_scans, 0) AS user_scans,
    COALESCE(us.user_lookups, 0) AS user_lookups,
    COALESCE(us.user_updates, 0) AS user_updates
FROM sys.indexes AS i
INNER JOIN sys.tables AS t
    ON t.object_id = i.object_id
INNER JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id
LEFT JOIN sys.dm_db_index_usage_stats AS us
    ON us.database_id = DB_ID()
   AND us.object_id = i.object_id
   AND us.index_id = i.index_id
WHERE
    t.object_id = OBJECT_ID(
        QUOTENAME(@schema_name) + N'.' + QUOTENAME(@table_name)
    )
    AND i.is_hypothetical = 0
ORDER BY
    i.index_id;

Si la table subit beaucoup d’écritures, chaque index ajouté a un coût.


6. Vérifier quand la fenêtre d’observation actuelle a commencé

Les recommandations d’index manquants sont éphémères.

SELECT
    sqlserver_start_time
FROM sys.dm_os_sys_info;

Si l’instance a redémarré récemment, les DMV d’index manquants ne reflètent que la charge de travail depuis ce redémarrage.

Cette fenêtre peut être trop courte pour prendre une décision de conception.

Interpréter le résultat

Champ Pourquoi c’est important
user_seeks Nombre de recherches (seeks) utilisateur qui auraient pu bénéficier de la recommandation.
user_scans Nombre d’analyses (scans) utilisateur associées à la recommandation.
avg_total_user_cost Coût moyen, estimé par l’optimiseur, des requêtes utilisateur associées au groupe.
avg_user_impact Estimation par l’optimiseur de la réduction possible du coût de la requête grâce à l’index, exprimée en pourcentage.
estimated_improvement_score Heuristique de tri combinant fréquence, coût et impact estimé. Pas une garantie.
equality_columns Colonnes référencées par des prédicats d’égalité tels que =.
inequality_columns Colonnes référencées par des prédicats de type plage tels que >, < ou BETWEEN.
included_columns Colonnes hors clé que l’optimiseur suggère de couvrir dans l’index.
unique_compiles Nombre de compilations/recompilations ayant contribué au groupe de recommandations.
last_user_seek Dernière recherche (seek) observée associée à la recommandation.

Points à vérifier

Score élevé + nombre de seeks élevé

C’est un bon candidat pour une analyse approfondie.

Vérifiez que :

  • la requête est importante pour le métier
  • la charge de travail est représentative
  • aucun index existant ne couvre déjà l’essentiel du modèle d’accès
  • le nouvel index n’entraînera pas un coût d’écriture excessif

Nombreuses recommandations pour la même table

Cela signifie souvent que la sortie brute des DMV doit être consolidée.

Repérez les colonnes de tête répétées et les ensembles INCLUDE qui se recouvrent.

Un petit nombre d’index soigneusement conçus vaut généralement mieux que l’application littérale de chaque recommandation.

Longues listes INCLUDE

Soyez prudent.

Un index couvrant très large peut :

  • consommer beaucoup de stockage
  • accroître la pression sur le buffer pool
  • augmenter le coût en écriture
  • allonger la durée de maintenance

Ne couvrez que ce qui apporte un bénéfice défendable pour la charge de travail.

Colonnes d’égalité et d’inégalité mélangées

Un point de départ courant consiste à placer :

  • les colonnes d’égalité en premier
  • les colonnes d’inégalité ensuite

Mais ne considérez pas le texte renvoyé par la DMV comme une conception parfaite de l’ordre des clés.

La sélectivité des colonnes, les besoins de tri, les modèles de jointure, les index existants et la nature de la charge de travail restent déterminants.

Recommandation apparue une seule fois après un redémarrage récent

Indice faible.

Collectez les données sur une fenêtre d’observation plus longue ou validez avec Query Store et les plans d’exécution.

Notes de production

Les DMV d’index manquants sont indicatives

L’optimiseur enregistre ce qu’il estime pouvoir aider pour un problème d’optimisation précis.

Il ne tient pas compte de l’ensemble de vos index, de la charge d’écriture, du budget de stockage, de la stratégie de maintenance ni de toutes les autres requêtes sur la table.

Les recommandations sont éphémères

Les informations sur les index manquants peuvent être effacées par des événements tels qu’un redémarrage de SQL Server.

Ne considérez pas les DMV comme un historique durable.

Si vous voulez analyser des tendances, enregistrez-en périodiquement des instantanés dans votre propre base de supervision.

Les recommandations peuvent se recouvrir

Une même charge de travail sous-jacente peut générer plusieurs suggestions d’index similaires.

Les appliquer toutes aveuglément peut entraîner une prolifération d’index.

Comparez toujours :

  • les colonnes de clé
  • l’ordre des clés
  • les colonnes INCLUDE
  • les filtres
  • l’unicité
  • les index existants
  • l’importance de la charge de travail

Les DMV d’index manquants ne disent pas tout

Elles ne remplacent pas :

  • l’analyse des plans d’exécution
  • Query Store
  • les statistiques d’utilisation des index
  • l’analyse de la taille des tables
  • l’analyse du volume d’écriture
  • les tests sur une charge de travail réelle

Les autorisations varient selon la version et la plateforme

L’accès aux DMV d’index manquants dépend de la version et de la plateforme de SQL Server.

Sur les versions récentes de SQL Server, des autorisations d’état des performances au niveau du serveur sont utilisées pour une visibilité étendue sur les DMV ; les versions antérieures reposent généralement sur VIEW SERVER STATE.

Azure SQL Database a ses propres règles de portée et d’autorisations.

La création d’un index peut provoquer des blocages ou consommer beaucoup de ressources

Sur une grande table de production, la création d’un index peut nécessiter une quantité importante de :

  • CPU
  • E/S
  • journal des transactions
  • mémoire
  • tempdb
  • activité de verrouillage/métadonnées

N’utilisez les options en ligne (online) ou avec reprise (resumable) que si votre édition/version de SQL Server les prend en charge et qu’elles s’intègrent à votre plan opérationnel.

Erreurs fréquentes

1. Transformer chaque recommandation en instruction CREATE INDEX

C’est l’erreur la plus fréquente.

Les DMV d’index manquants génèrent des suggestions propres à une charge de travail, pas une conception d’index optimisée globalement.

2. Considérer avg_user_impact comme un pourcentage d’amélioration garanti

C’est une estimation de l’optimiseur.

Cela ne signifie pas que la requête deviendra littéralement 80 % plus rapide.

3. Ignorer les index existants

Une recommandation peut être proche d’un index que vous avez déjà.

Parfois, la bonne réponse consiste à :

  • modifier un index existant
  • fusionner deux index
  • ajuster les colonnes INCLUDE
  • ne rien faire

4. Ignorer le coût en écriture

Chaque index non cluster doit être maintenu lors des modifications de données qui le concernent.

Le gain en lecture doit justifier ce coût permanent.

5. Créer des index couvrants très larges

Davantage de colonnes INCLUDE peut réduire les lookups, mais les index larges peuvent devenir coûteux en stockage, en mémoire, en écritures et en maintenance.

6. Utiliser une fenêtre d’observation trop courte

Un redémarrage peut donner aux DMV une apparence propre et ciblée simplement parce qu’elles ne contiennent que la charge de travail récente.

Ne repensez pas l’indexation à partir de quelques minutes ou heures de données, sauf si l’incident lui-même justifie ce périmètre restreint.

7. Supposer qu’un index manquant est la cause racine

Une requête lente peut aussi être due à :

  • de mauvaises estimations de cardinalité
  • des blocages
  • des statistiques obsolètes ou inadaptées
  • une sensibilité aux paramètres
  • une pression mémoire
  • une requête mal construite
  • une pression sur tempdb
  • une contention des ressources à l’échelle du serveur

Considérez les indices d’index manquants comme un signal parmi d’autres.

Étape suivante

Utilisez le profil de la recommandation pour décider quoi examiner ensuite.

  • Candidat à forte valeur + aucun index existant similaire → examinez le plan d’exécution et testez un index soigneusement conçu.
  • Plusieurs recommandations similaires → consolidez-les avant de créer quoi que ce soit.
  • Longue liste INCLUDE → identifiez les colonnes qui ont réellement besoin d’être couvertes.
  • Table fortement sollicitée en écriture → mettez en balance le gain en lecture et le coût des mises à jour.
  • Redémarrage récent de SQL Server → collectez davantage d’éléments avant d’effectuer des changements structurels.
  • Recommandation ciblant une seule requête problématique → validez avec Query Store et le plan d’exécution réel.
  • Un index existant est proche → envisagez de le modifier/consolider plutôt que d’ajouter un autre index.
  • Aucun candidat d’index manquant convaincant → poursuivez un diagnostic des performances plus large au lieu de forcer une correction par l’indexation.

← Tous les aide-mémoire