Parcourir les aide-mémoire · SQL Server
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
- Problème
- Réponse rapide
- Code / commandes
- 1. Afficher l’activité de lecture et d’écriture de chaque index classique
- 2. Trouver les index avec des écritures mais aucune lecture utilisateur observée
- 3. Trouver les index avec beaucoup d’écritures par rapport aux lectures
- 4. Afficher les dernières lectures et écritures observées
- 5. Vérifier depuis combien de temps la fenêtre d’observation actuelle de la DMV existe
- 6. Ajouter la taille des index à la vue d’utilisation
- 7. Afficher les colonnes indexées avant de prendre une décision
- Interpréter le résultat
- Points à vérifier
- Zéro lecture + beaucoup d’écritures
- Très peu de lectures + énormément de mises à jour
- Scans nombreux
- Lookups nombreux
- Index volumineux + faible activité de lecture
- Index similaires sur la même table
- Notes de production
- Réinitialisation des statistiques d’utilisation
- user_updates compte des opérations, pas des lignes modifiées
- Une ligne absente de la DMV ne prouve pas une non-utilisation permanente
- Les autorisations requises varient
- Les index optimisés en mémoire utilisent d’autres DMV
- Le nombre d’utilisations n’est pas la valeur métier
- Erreurs fréquentes
- 1. Supprimer tous les index sans lecture
- 2. Ignorer l’heure de redémarrage du serveur
- 3. Considérer les scans comme mauvais
- 4. Utiliser user_updates comme nombre de lignes modifiées
- 5. Supprimer des index de clé primaire ou de contrainte d’unicité
- 6. Évaluer un index isolément
- 7. Utiliser les statistiques d’utilisation comme supervision historique
- Étape suivante
- 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.
Ce contenu vous a-t-il été utile ?
Que pourrions-nous améliorer ?
Merci — cela m’aide à améliorer Javi Builds.
Restez au courant
Recevez uniquement les nouvelles de Javi Builds qui vous intéressent.
Consultez votre boîte de réception
Je vous ai envoyé un lien de confirmation. Votre abonnement sera actif après confirmation.