Ungenutzte / nur beschriebene Indizes finden

Befehl

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;

Was es tut

Listet Nonclustered-Indizes, die seit dem Neustart nicht gelesen wurden (seek/scan/lookup = 0), aber weiterhin Schreibkosten verursachen, mit ihrer Größe und erzeugt eine Entwurfsanweisung DROP INDEX.

Wann einsetzen

Bei langsamen INSERT/UPDATE auf schreiblastigen Tabellen, um Speicher- und Wartungszeit (Rebuild, CHECKDB) zu senken, oder vor einer Index-Konsolidierung.

Vorsicht

Die Zähler werden bei Neustart, Offline-Schalten/Detach der Datenbank und (bei einigen älteren 2012/2014-Builds) bei Index-Rebuild zurückgesetzt; nur von Monats-/Jahresabschlussberichten genutzte Indizes wirken in einem kurzen Zeitfenster "ungenutzt". Beobachte vor dem Löschen mindestens einen vollständigen Geschäftszyklus und prüfe in einer AG die Nutzung auf lesbaren Secondaries separat (die Zähler gelten pro Replica). Unique Indizes erzwingen Constraints, auch wenn sie nicht gelesen werden; sie sind ausgeschlossen. Reine Leseabfrage; sicher.