Les index sont l’un des outils de performance les plus puissants dans SQL Server — et l’une des sources d’overhead les plus négligées. Chaque index que vous créez doit être maintenu à chaque opération INSERT, UPDATE et DELETE. Les index inutilisés vous coûtent de l’espace disque, du temps de sauvegarde, de la mémoire et des performances en écriture, sans apporter aucun bénéfice en lecture. Les identifier et les supprimer est une tâche DBA routinière mais à fort impact.

Comment SQL Server suit l’utilisation des index

SQL Server alimente SYS.DM_DB_INDEX_USAGE_STATS avec une ligne pour chaque index la première fois qu’il est accédé après un redémarrage de l’instance. Les compteurs suivent les recherches, scans, lookups et mises à jour. Implications clés :

• Les index non listés dans la vue n’ont jamais été utilisés depuis le dernier redémarrage.
• Les index listés avec des compteurs seek/scan/lookup à zéro ont été maintenus (des écritures se sont produites) mais jamais lus.
• Les compteurs se réinitialisent à chaque redémarrage d’instance — exécutez ces requêtes après une période représentative de charge normale, idéalement plusieurs semaines ou plus.

Important : discutez toujours des résultats avec l’équipe applicative avant de supprimer quoi que ce soit. Certains index ne sont utilisés que par des processus batch, des rapports de fin de mois, ou des fonctionnalités qui ne sont pas actives toute l’année.

Requête 1 : Index jamais utilisés (absents de la DMV)

-- Index qui n'ont jamais été utilisés depuis le dernier redémarrage de l'instance
DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())

SELECT
    OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
    INDEXNAME  = I.NAME,
    I.INDEX_ID
FROM SYS.INDEXES I
JOIN SYS.OBJECTS O ON I.OBJECT_ID = O.OBJECT_ID
WHERE OBJECTPROPERTY(O.OBJECT_ID, 'IsUserTable') = 1
  AND I.INDEX_ID NOT IN (
        SELECT S.INDEX_ID
        FROM SYS.DM_DB_INDEX_USAGE_STATS S
        WHERE S.OBJECT_ID = I.OBJECT_ID
          AND I.INDEX_ID  = S.INDEX_ID
          AND DATABASE_ID = @dbid
  )
ORDER BY OBJECTNAME, I.INDEX_ID, INDEXNAME;

Requête 2 : Index présents mais sans activité de lecture

-- Index présents dans la DMV mais avec zéro activité seek/scan/lookup
DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())

SELECT
    OBJECTNAME        = OBJECT_NAME(i.OBJECT_ID),
    INDEXNAME         = i.NAME,
    user_seeks        = ISNULL(u.user_seeks, 0),
    user_scans        = ISNULL(u.user_scans, 0),
    user_lookups      = ISNULL(u.user_lookups, 0),
    user_updates      = ISNULL(u.user_updates, 0)
FROM [sys].[indexes] i
JOIN [sys].[objects] o ON i.OBJECT_ID = o.OBJECT_ID
LEFT JOIN [sys].[dm_db_index_usage_stats] u
    ON  i.OBJECT_ID  = u.OBJECT_ID
    AND i.[index_id] = u.[index_id]
    AND u.database_id = @dbid
WHERE OBJECTPROPERTY(o.OBJECT_ID, 'IsUserTable') = 1
  AND i.index_id > 0  -- Exclure les heap
  AND ISNULL(u.user_seeks,  0) = 0
  AND ISNULL(u.user_scans,  0) = 0
  AND ISNULL(u.user_lookups,0) = 0
ORDER BY ISNULL(u.user_updates, 0) DESC;

La colonne user_updates indique combien de fois l’index a été maintenu sans jamais être lu — c’est votre overhead pure. Les index avec un score élevé ici sont les candidats prioritaires à la suppression.