DBAtlas · Atlas d'exploitation des bases de données

Trouver les index inutilisés / en écriture seule

SQL ServerMaintenance

Commande

USE [Musteri];
SELECT OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
       OBJECT_NAME(i.object_id)        AS table_name,
       i.name                          AS index_name,
       ISNULL(us.user_seeks, 0) + ISNULL(us.user_scans, 0) + ISNULL(us.user_lookups, 0) AS reads,
       ISNULL(us.user_updates, 0)      AS writes,
       ps.used_mb,
       us.last_user_seek, us.last_user_scan,
       N'DROP INDEX ' + QUOTENAME(i.name) + N' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id))
         + N'.' + QUOTENAME(OBJECT_NAME(i.object_id)) + N';' AS drop_stmt_draft
FROM   sys.indexes i
  LEFT JOIN sys.dm_db_index_usage_stats us
         ON us.database_id = DB_ID() AND us.object_id = i.object_id AND us.index_id = i.index_id
  CROSS APPLY (SELECT CAST(SUM(p.used_page_count) * 8 / 1024.0 AS DECIMAL(18,1)) AS used_mb
               FROM sys.dm_db_partition_stats p
               WHERE p.object_id = i.object_id AND p.index_id = i.index_id) ps
WHERE  OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
  AND  i.type_desc = N'NONCLUSTERED'
  AND  i.is_primary_key = 0 AND i.is_unique_constraint = 0 AND i.is_unique = 0
  AND  ISNULL(us.user_seeks, 0) + ISNULL(us.user_scans, 0) + ISNULL(us.user_lookups, 0) = 0
ORDER BY writes DESC, ps.used_mb DESC;

-- Usage counters since:
SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

Ce que fait la commande

Liste les index nonclustered qui n'ont pas été lus (seek/scan/lookup = 0) depuis le redémarrage mais subissent toujours un coût d'écriture, avec leur taille, et génère une instruction DROP INDEX préliminaire.

Quand l'utiliser

Pour les INSERT/UPDATE lents sur des tables à forte écriture, pour réduire le stockage et la durée de maintenance (rebuild, CHECKDB), ou avant une consolidation des index.

Attention

Les compteurs sont réinitialisés au redémarrage, à la mise hors ligne/détachement de la base et (sur certains anciens builds 2012/2014) à la reconstruction d'index ; les index utilisés uniquement par les rapports de fin de mois/d'année semblent "inutilisés" sur une courte fenêtre. Observez au moins un cycle métier complet avant de supprimer, et dans un AG vérifiez séparément l'utilisation sur les secondaires lisibles (les compteurs sont par réplica). Les index uniques imposent des contraintes même sans être lus ; ils sont exclus. Requête en lecture seule ; sûre.

Ouvrir dans DBAtlas →

Voir aussi