Parcourir les aide-mémoire · SQL Server

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

Vérifier l’utilisation des index

Examinez l’activité de lecture et d’écriture des index SQL Server, repérez les index à faible valeur et déterminez lesquels méritent une analyse approfondie avant toute suppression.

Sur cette page
  1. Problème
  2. Réponse rapide
  3. Code / commandes
  4. 1. Afficher l’activité de lecture et d’écriture de chaque index classique
  5. 2. Trouver les index avec des écritures mais aucune lecture utilisateur observée
  6. 3. Trouver les index avec beaucoup d’écritures par rapport aux lectures
  7. 4. Afficher les dernières lectures et écritures observées
  8. 5. Vérifier depuis combien de temps la fenêtre d’observation actuelle de la DMV existe
  9. 6. Ajouter la taille des index à la vue d’utilisation
  10. 7. Afficher les colonnes indexées avant de prendre une décision
  11. Interpréter le résultat
  12. Points à vérifier
  13. Zéro lecture + beaucoup d’écritures
  14. Très peu de lectures + énormément de mises à jour
  15. Scans nombreux
  16. Lookups nombreux
  17. Index volumineux + faible activité de lecture
  18. Index similaires sur la même table
  19. Notes de production
  20. Réinitialisation des statistiques d’utilisation
  21. user_updates compte des opérations, pas des lignes modifiées
  22. Une ligne absente de la DMV ne prouve pas une non-utilisation permanente
  23. Les autorisations requises varient
  24. Les index optimisés en mémoire utilisent d’autres DMV
  25. Le nombre d’utilisations n’est pas la valeur métier
  26. Erreurs fréquentes
  27. 1. Supprimer tous les index sans lecture
  28. 2. Ignorer l’heure de redémarrage du serveur
  29. 3. Considérer les scans comme mauvais
  30. 4. Utiliser user_updates comme nombre de lignes modifiées
  31. 5. Supprimer des index de clé primaire ou de contrainte d’unicité
  32. 6. Évaluer un index isolément
  33. 7. Utiliser les statistiques d’utilisation comme supervision historique
  34. Étape suivante
  35. Pour aller plus loin

Problème

Avec le temps, SQL Server peut accumuler des index.

Certains sont très utilisés. Certains existent pour un rapport mensuel précis. Certains servent de support à des contraintes. D’autres n’aident peut-être plus aucune charge de travail importante, mais ajoutent toujours un coût de stockage, de mémoire et de maintenance à l’écriture.

L’objectif n’est pas de trouver automatiquement des index à supprimer.

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

  • Quels index sont utilisés pour des seeks, des scans ou des lookups ?
  • Quels index sont maintenus par des écritures ?
  • Quels index semblent inutilisés pendant la période observée ?
  • À quand remonte la dernière utilisation d’un index ?
  • La maintenance d’un index est-elle coûteuse au regard du bénéfice qu’il apporte en lecture ?
  • Quels index méritent un examen plus approfondi ?

Réponse rapide

Commencez par sys.dm_db_index_usage_stats, joint aux vues de catalogue afin que les index sans ligne dans la DMV apparaissent quand même.

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    i.index_id,
    i.name AS index_name,
    i.type_desc,
    i.is_unique,
    i.is_primary_key,
    i.is_unique_constraint,

    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,

    COALESCE(us.user_seeks, 0)
      + COALESCE(us.user_scans, 0)
      + COALESCE(us.user_lookups, 0) AS total_user_reads,

    us.last_user_seek,
    us.last_user_scan,
    us.last_user_lookup,
    us.last_user_update
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.is_ms_shipped = 0
    AND i.index_id > 0
    AND i.is_hypothetical = 0
ORDER BY
    total_user_reads ASC,
    user_updates DESC,
    s.name,
    t.name,
    i.index_id;

Considérez le résultat comme des éléments à investiguer, et non comme un script de suppression.

Code / commandes

1. Afficher l’activité de lecture et d’écriture de chaque index classique

C’est la requête d’inventaire par défaut.

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    i.index_id,
    i.name AS index_name,
    i.type_desc,

    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,

    COALESCE(us.user_seeks, 0)
      + COALESCE(us.user_scans, 0)
      + COALESCE(us.user_lookups, 0) AS total_user_reads,

    us.last_user_seek,
    us.last_user_scan,
    us.last_user_lookup,
    us.last_user_update
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.is_ms_shipped = 0
    AND i.index_id > 0
    AND i.is_hypothetical = 0
ORDER BY
    total_user_reads DESC,
    user_updates DESC;

Le LEFT JOIN est important.

Un index qui n’a pas été utilisé depuis la réinitialisation des statistiques d’utilisation peut ne pas avoir de ligne dans sys.dm_db_index_usage_stats. Une jointure interne masquerait précisément les index que vous voulez peut-être examiner.


2. Trouver les index avec des écritures mais aucune lecture utilisateur observée

Utilisez cette requête comme liste de candidats, jamais comme liste de suppression automatique.

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    i.index_id,
    i.name AS index_name,
    i.type_desc,

    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,

    us.last_user_update
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.is_ms_shipped = 0
    AND i.index_id > 1
    AND i.is_hypothetical = 0
    AND i.is_primary_key = 0
    AND i.is_unique_constraint = 0
    AND COALESCE(us.user_seeks, 0) = 0
    AND COALESCE(us.user_scans, 0) = 0
    AND COALESCE(us.user_lookups, 0) = 0
    AND COALESCE(us.user_updates, 0) > 0
ORDER BY
    user_updates DESC;

Pourquoi index_id > 1 ?

Ce filtre cible les index rowstore non cluster et évite de traiter un index cluster comme un index secondaire facultatif.

Vous devez quand même examiner la définition de l’index, l’historique de la charge de travail, les dépendances et l’usage métier avant d’envisager une suppression.


3. Trouver les index avec beaucoup d’écritures par rapport aux lectures

Cette requête peut révéler des index dont le coût de maintenance est élevé par rapport à l’activité de lecture utilisateur observée.

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    i.index_id,
    i.name AS index_name,

    COALESCE(us.user_seeks, 0)
      + COALESCE(us.user_scans, 0)
      + COALESCE(us.user_lookups, 0) AS total_user_reads,

    COALESCE(us.user_updates, 0) AS user_updates,

    CAST(
        COALESCE(us.user_updates, 0) * 1.0
        / NULLIF(
            COALESCE(us.user_seeks, 0)
              + COALESCE(us.user_scans, 0)
              + COALESCE(us.user_lookups, 0),
            0
        )
        AS decimal(18,2)
    ) AS writes_per_read
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.is_ms_shipped = 0
    AND i.index_id > 1
    AND i.is_hypothetical = 0
ORDER BY
    writes_per_read DESC,
    user_updates DESC;

Un ratio élevé n’est pas forcément mauvais.

Un index utilisé une fois par heure par une requête critique peut valoir bien plus qu’un index lu des milliers de fois par une charge de travail sans importance.


4. Afficher les dernières lectures et écritures observées

Utilisez cette requête pour déterminer si un index est saisonnier, périodique ou potentiellement obsolète.

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    i.name AS index_name,

    us.last_user_seek,
    us.last_user_scan,
    us.last_user_lookup,
    us.last_user_update
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.is_ms_shipped = 0
    AND i.index_id > 0
    AND i.is_hypothetical = 0
ORDER BY
    COALESCE(
        us.last_user_seek,
        us.last_user_scan,
        us.last_user_lookup,
        us.last_user_update
    ) ASC;

Un horodatage de dernière utilisation NULL ne prouve pas qu’un index n’a jamais été utilisé.

Il signifie qu’aucune utilisation correspondante n’a été enregistrée dans la fenêtre d’observation actuelle.


5. Vérifier depuis combien de temps la fenêtre d’observation actuelle de la DMV existe

Les compteurs d’utilisation n’ont de sens que si vous savez quand ils ont pu être réinitialisés.

SELECT
    sqlserver_start_time
FROM sys.dm_os_sys_info;

Les compteurs de sys.dm_db_index_usage_stats sont réinitialisés au démarrage du moteur de base de données SQL Server.

Les lignes d’une base de données peuvent aussi disparaître lorsque la base est détachée ou arrêtée, par exemple avec AUTO_CLOSE.

Si l’instance a redémarré hier, les conclusions du type « index inutilisé » sont fragiles.


6. Ajouter la taille des index à la vue d’utilisation

Un index volumineux, fortement sollicité en écriture et rarement lu peut mériter plus d’attention qu’un index minuscule.

WITH index_size AS
(
    SELECT
        ps.object_id,
        ps.index_id,
        SUM(ps.used_page_count) AS used_pages
    FROM sys.dm_db_partition_stats AS ps
    GROUP BY
        ps.object_id,
        ps.index_id
)
SELECT
    s.name AS schema_name,
    t.name AS table_name,
    i.name AS index_name,

    CAST(COALESCE(sz.used_pages, 0) * 8.0 / 1024 AS decimal(18,2)) AS used_mb,

    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 index_size AS sz
    ON sz.object_id = i.object_id
   AND sz.index_id = i.index_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.is_ms_shipped = 0
    AND i.index_id > 0
    AND i.is_hypothetical = 0
ORDER BY
    used_mb DESC;

Utilisez la taille en complément de l’utilisation, pas à sa place.


7. Afficher les colonnes indexées avant de prendre une décision

Avant de supprimer ou de consolider quoi que ce soit, examinez la définition.

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    i.name AS index_name,
    i.index_id,
    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.tables AS t
    ON t.object_id = i.object_id
INNER JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id
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
    t.is_ms_shipped = 0
    AND i.name = N'YourIndexName'
    AND t.object_id = OBJECT_ID(N'dbo.YourTable')
ORDER BY
    ic.is_included_column,
    ic.key_ordinal,
    ic.index_column_id;

Deux index aux noms similaires peuvent tout de même servir des charges de travail différentes en raison de l’ordre des clés, des colonnes INCLUDE, des filtres ou de l’unicité.

Interpréter le résultat

Champ Pourquoi c’est important
user_seeks Nombre de seeks de requêtes utilisateur enregistrés sur l’index.
user_scans Nombre de scans de requêtes utilisateur enregistrés sur l’index. Un scan n’est pas forcément mauvais.
user_lookups Activité de bookmark/key lookup impliquant la structure de l’index.
user_updates Nombre d’opérations d’écriture utilisateur ayant nécessité une maintenance de l’index. Il s’agit d’un nombre d’opérations, pas de lignes modifiées.
last_user_seek Dernier seek utilisateur enregistré pendant la fenêtre d’observation actuelle de la DMV.
last_user_scan Dernier scan utilisateur enregistré.
last_user_lookup Dernier lookup utilisateur enregistré.
last_user_update Dernier événement de maintenance en écriture utilisateur enregistré.
total_user_reads Calcul de commodité : seeks + scans + lookups.
writes_per_read Ratio de tri uniquement. Aide à faire ressortir les index lourds à maintenir, mais ne constitue pas une règle de suppression.
used_mb Espace de stockage actuellement occupé par l’index.

Points à vérifier

Zéro lecture + beaucoup d’écritures

C’est un bon candidat à l’examen si la fenêtre d’observation est suffisamment longue.

Avant d’agir, vérifiez que :

  • SQL Server n’a pas redémarré récemment
  • la base de données n’a pas été récemment restaurée, détachée ou fermée
  • l’index n’est pas utilisé par une charge de travail mensuelle, trimestrielle ou de fin d’année
  • il n’est pas nécessaire à l’unicité ni à la sémantique d’une contrainte
  • aucune requête importante n’en dépend en dehors de la période observée

Très peu de lectures + énormément de mises à jour

Cela peut indiquer un surcoût de maintenance pour un faible bénéfice en lecture observé.

Donnez la priorité aux index volumineux sur les tables très sollicitées en écriture pour un examen approfondi.

Scans nombreux

Ne qualifiez pas l’index de mauvais parce que les scans sont nombreux.

Un scan peut être approprié pour :

  • les requêtes analytiques volumineuses
  • les prédicats peu sélectifs
  • les petites tables ou les petits index
  • l’accès ordonné
  • les modèles d’accès couvrants

Examinez les plans d’exécution qui l’utilisent.

Lookups nombreux

Des key/bookmark lookups fréquents peuvent indiquer qu’un index non cluster amène SQL Server près des lignes recherchées, mais ne couvre pas les colonnes demandées.

Étape suivante possible :

  • examiner la requête
  • examiner le coût des lookups dans le plan d’exécution
  • déterminer si des colonnes INCLUDE sont pertinentes

N’ajoutez pas aveuglément à l’index toutes les colonnes concernées par les lookups.

Index volumineux + faible activité de lecture

Cette combinaison mérite attention, car elle affecte :

  • le stockage
  • le buffer pool
  • la taille des sauvegardes
  • la maintenance des index
  • le coût des écritures

Validez néanmoins la charge de travail métier avant toute suppression.

Index similaires sur la même table

Un chevauchement potentiel peut exister lorsque des index partagent :

  • les mêmes clés de tête
  • des colonnes INCLUDE similaires
  • des filtres similaires

Les compteurs d’utilisation ne permettent pas à eux seuls de savoir si deux index peuvent être consolidés sans risque.

Comparez les définitions et les plans d’exécution réels.

Notes de production

Réinitialisation des statistiques d’utilisation

sys.dm_db_index_usage_stats ne constitue pas un historique permanent.

Ses compteurs sont réinitialisés au démarrage du moteur de base de données.

Des lignes peuvent aussi disparaître lorsqu’une base de données est détachée ou arrêtée.

Notez toujours le début de la fenêtre d’observation avant de qualifier un index d’inutilisé.

user_updates compte des opérations, pas des lignes modifiées

Une instruction qui met à jour 1 ligne incrémente le compteur une fois.

Une instruction qui met à jour 1 000 000 de lignes l’incrémente aussi une seule fois.

N’interprétez pas user_updates comme un volume de lignes modifiées.

Une ligne absente de la DMV ne prouve pas une non-utilisation permanente

Cela signifie qu’aucune ligne d’utilisation n’existe dans la fenêtre de collecte actuelle.

L’index est peut-être inutilisé, mais cela peut aussi s’expliquer par :

  • un redémarrage récent
  • une création récente de l’index
  • une charge de travail peu fréquente
  • un événement du cycle de vie de la base de données

Les autorisations requises varient

Sur SQL Server et SQL Managed Instance, un accès complet à sys.dm_db_index_usage_stats nécessite la visibilité sur l’état du serveur.

Pour SQL Server 2022 et versions ultérieures, Microsoft documente VIEW SERVER PERFORMANCE STATE.

Les versions antérieures de SQL Server utilisent VIEW SERVER STATE.

Dans Azure SQL Database, les autorisations dépendent de la base de données et du niveau de service.

Les index optimisés en mémoire utilisent d’autres DMV

sys.dm_db_index_usage_stats n’est pas le bon endroit pour évaluer les index optimisés en mémoire.

Utilisez les DMV de statistiques d’index XTP correspondantes pour les charges de travail optimisées en mémoire.

Le nombre d’utilisations n’est pas la valeur métier

Une requête exécutée une fois par jour peut être critique pour le métier.

N’optimisez pas uniquement en fonction de la fréquence.

Erreurs fréquentes

1. Supprimer tous les index sans lecture

C’est l’interprétation la plus dangereuse de la DMV.

Zéro lecture signifie zéro lecture observée dans la fenêtre de statistiques actuelle, et non « cet index n’a aucune valeur ».

2. Ignorer l’heure de redémarrage du serveur

Un index peut sembler inutilisé simplement parce que les compteurs ont été réinitialisés récemment.

Établissez toujours la fenêtre d’observation.

3. Considérer les scans comme mauvais

Un scan peut être la méthode d’accès optimale.

Évaluez la requête, la sélectivité, la largeur de l’index, le nombre de lignes et le plan d’exécution.

4. Utiliser user_updates comme nombre de lignes modifiées

Il compte des opérations, pas des lignes affectées.

N’en déduisez pas un volume d’écriture.

5. Supprimer des index de clé primaire ou de contrainte d’unicité

Les index qui sous-tendent des contraintes font partie de la sémantique d’intégrité des données.

Ne les traitez pas comme des index de performance facultatifs.

6. Évaluer un index isolément

Un index peut :

  • servir une requête critique
  • chevaucher un autre index
  • fournir un ordre de tri
  • garantir l’unicité
  • réduire les blocages en raccourcissant les lectures
  • augmenter le coût des écritures

Examinez ensemble la charge de travail et les index voisins.

7. Utiliser les statistiques d’utilisation comme supervision historique

La DMV n’est cumulative que pendant sa durée de vie actuelle.

Si vous avez besoin de preuves durables, enregistrez périodiquement des instantanés des statistiques d’utilisation dans votre propre base de supervision.

Étape suivante

Utilisez le profil d’utilisation pour décider de ce qu’il faut examiner ensuite.

  • Zéro lecture + écritures significatives + longue fenêtre d’observation → examinez la définition de l’index, ses dépendances, Query Store et la charge de travail métier avant d’envisager une suppression.
  • Beaucoup de lectures + beaucoup d’écritures → l’index est probablement utile ; n’optimisez que si la taille ou le coût de maintenance pose problème.
  • Beaucoup de lookups → examinez les plans d’exécution et évaluez une stratégie d’index couvrant.
  • Beaucoup de scans → examinez la sélectivité de la charge de travail et les plans ; ne supposez pas que l’index est mauvais.
  • Index volumineux + peu de lectures → comparez l’utilisation, la taille, le chevauchement et le coût de maintenance.
  • Plusieurs index similaires → comparez l’ordre des clés, les colonnes INCLUDE, les filtres et l’utilisation dans les plans en vue d’une éventuelle consolidation.
  • La fenêtre d’observation est courte → collectez davantage d’éléments avant de modifier quoi que ce soit.

← Tous les aide-mémoire