Les requêtes de cette page lisent des informations de l'instance sans modifier vos données. Les commandes qui modifient quelque chose sont signalées dans le texte. Testez toujours sur un environnement non critique avant la production, et relisez chaque requête avant de l'exécuter.
Le script
À exécuter dans la base à analyser. Les compteurs viennent de sys.dm_db_index_usage_stats.
-- Index non clusterisés jamais lus (aucun seek, scan ou lookup)
-- À exécuter dans la base à analyser. Droit requis : VIEW SERVER STATE.
-- Attention : les compteurs sont remis à zéro au redémarrage du service.
SET NOCOUNT ON;
SELECT
SCHEMA_NAME(o.schema_id) AS schema_nom,
o.name AS table_nom,
i.name AS index_nom,
i.type_desc AS type_index,
i.is_unique AS est_unique,
ISNULL(u.user_updates, 0) AS mises_a_jour,
u.last_user_update AS derniere_mise_a_jour,
CONVERT(decimal(18, 2), t.taille_mo) AS taille_mo,
si.sqlserver_start_time AS compteurs_depuis
FROM sys.indexes AS i
JOIN sys.objects AS o
ON o.object_id = i.object_id
AND o.type = 'U'
AND o.is_ms_shipped = 0
LEFT JOIN sys.dm_db_index_usage_stats AS u
ON u.database_id = DB_ID()
AND u.object_id = i.object_id
AND u.index_id = i.index_id
CROSS APPLY (SELECT SUM(p.used_page_count) * 8 / 1024.0 AS taille_mo
FROM sys.dm_db_partition_stats AS p
WHERE p.object_id = i.object_id
AND p.index_id = i.index_id) AS t
CROSS JOIN sys.dm_os_sys_info AS si
WHERE i.index_id > 1 -- exclut le tas et l'index clusterisé
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND i.is_disabled = 0
AND i.is_hypothetical = 0
AND ISNULL(u.user_seeks, 0) + ISNULL(u.user_scans, 0) + ISNULL(u.user_lookups, 0) = 0
ORDER BY t.taille_mo DESC;Comment lire le résultat
- compteurs_depuis : la période observée. Si le serveur vient de redémarrer, la liste est trompeuse. Attendez un cycle complet d'activité (fin de mois, clôture, traitements annuels) avant de décider.
- mises_a_jour : le nombre d'écritures que l'index a absorbées pour rien. Plus il est élevé, plus la suppression rapporte.
- est_unique : un index unique peut servir à garantir l'unicité sans jamais être lu. Ne le supprimez pas sans avoir vérifié la règle qu'il porte.
Précautions avant de supprimer
- Un index qui sert une fois par an (état de fin d'exercice) paraît inutilisé le reste du temps.
- Les compteurs d'une base sont aussi réinitialisés quand la base est fermée, détachée ou restaurée.
- Dans un groupe de disponibilité, les lectures sur le réplica secondaire sont comptées sur ce réplica : lancez le script sur chaque réplica avant de conclure.
- Avant de supprimer, conservez le script de création (
CREATE INDEX) pour pouvoir le recréer. Désactiver l'index (ALTER INDEX ... DISABLE) est une étape intermédiaire prudente.