DBAtlas · Database operations atlas

Find unused / write-only indexes

SQL ServerMaintenance

Command

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;

What it does

Lists nonclustered indexes that have not been read (seek/scan/lookup = 0) since restart but still incur write cost, with their size, and generates a draft DROP INDEX statement.

When to use it

For slow INSERT/UPDATE on write-heavy tables, to reduce storage and maintenance (rebuild, CHECKDB) time, or before index consolidation.

Caution

Counters reset on restart, database offline/detach and (on some older 2012/2014 builds) index rebuild; indexes used only by month-end/year-end reports look "unused" in a short window. Observe for at least one full business cycle before dropping, and in an AG check usage on readable secondaries separately (counters are per replica). Unique indexes enforce constraints even when not read; they are excluded. Read-only query; safe.

Open in DBAtlas →

Related