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
Deux seuils à adapter : @SeuilLectures (nombre maximal de seeks, scans et lookups) et @SeuilMaj (nombre minimal de mises à jour). Sur une base très active, montez-les. Une valeur de départ raisonnable : moins de 100 lectures, plus de 1 000 mises à jour.
-- Index non clusterisés peu lus et beaucoup mis à jour
-- À exécuter dans la base à analyser. Droit requis : VIEW SERVER STATE.
SET NOCOUNT ON;
DECLARE @SeuilLectures bigint = 100; -- lectures (seeks + scans + lookups) strictement inférieures à ce seuil
DECLARE @SeuilMaj bigint = 1000; -- mises à jour supérieures ou égales à ce seuil
SELECT
SCHEMA_NAME(o.schema_id) AS schema_nom,
o.name AS table_nom,
i.name AS index_nom,
i.is_unique AS est_unique,
u.user_seeks AS seeks,
u.user_scans AS scans,
u.user_lookups AS lookups,
u.user_updates AS mises_a_jour,
CONVERT(decimal(18, 1),
u.user_updates * 1.0
/ NULLIF(u.user_seeks + u.user_scans + u.user_lookups, 0)) AS maj_par_lecture,
lec.derniere_lecture AS derniere_lecture,
CONVERT(decimal(18, 2), t.taille_mo) AS taille_mo
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
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 MAX(d) AS derniere_lecture
FROM (VALUES (u.last_user_seek), (u.last_user_scan), (u.last_user_lookup)) AS v(d)) AS lec
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
WHERE i.index_id > 1
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND i.is_disabled = 0
AND u.user_seeks + u.user_scans + u.user_lookups < @SeuilLectures
AND u.user_updates >= @SeuilMaj
ORDER BY u.user_updates DESC;Les index jamais lus (aucune ligne dans la vue d'utilisation, ou zéro lecture) sont traités par le script sur les index inutilisés.
Comment lire le résultat
- maj_par_lecture : le nombre d'écritures pour chaque lecture. Un ratio de plusieurs centaines indique un index à fort coût et faible bénéfice.
- derniere_lecture : la date de la dernière utilisation. Une date très ancienne conforte la suppression.
- taille_mo : l'espace récupérable, et le temps gagné sur les sauvegardes et les maintenances.
Que faire d'un index de cette liste
- Vérifier qu'il n'est pas le seul à servir une requête critique rare (clôture, export réglementaire).
- Examiner s'il est un doublon, ou un préfixe d'un autre index : les colonnes clés sont-elles les mêmes que celles d'un autre index plus large ?
- Envisager de le fusionner avec un autre index plutôt que de le supprimer.
- Comme pour les index inutilisés, tenir compte de la durée depuis le dernier redémarrage et des réplicas secondaires.