IndexUsageAndCandidates.sql

/*
Purpose:
- Compare nonclustered-index reads, writes, and size in the current database.
- Surface candidates for human review; this script never disables or drops an index.

Safety:
- Read-only.
- DMV counters reset after restart, failover, detach/attach, and some database operations.
- Validate over a representative business cycle before changing any index.

Requirements:
- SQL Server 2012 or later.
- VIEW DATABASE STATE.

Customization:
- Set @MinimumWrites to hide indexes with little activity.
*/

SET NOCOUNT ON;

DECLARE @MinimumWrites BIGINT = 100;

SELECT
    sqlserver_start_time AS usage_window_started_at
FROM sys.dm_os_sys_info;

;WITH IndexSizes AS
(
    SELECT
        ps.object_id,
        ps.index_id,
        SUM(ps.used_page_count) * 8.0 / 1024 AS used_mb,
        SUM(ps.row_count) AS row_count
    FROM sys.dm_db_partition_stats AS ps
    GROUP BY ps.object_id, ps.index_id
),
Usage AS
(
    SELECT
        i.object_id,
        i.index_id,
        COALESCE(us.user_seeks, 0) AS user_seeks,
        COALESCE(us.user_scans, 0) AS user_scans,
        COALESCE(us.user_lookups, 0) AS user_lookups,
        COALESCE(us.user_updates, 0) AS user_updates,
        us.last_user_seek,
        us.last_user_scan,
        us.last_user_lookup,
        us.last_user_update
    FROM sys.indexes AS i
    LEFT JOIN sys.dm_db_index_usage_stats AS us
        ON us.database_id = DB_ID()
       AND us.object_id = i.object_id
       AND us.index_id = i.index_id
)
SELECT
    s.name AS schema_name,
    t.name AS table_name,
    i.name AS index_name,
    CAST(sz.used_mb AS DECIMAL(18, 2)) AS used_mb,
    sz.row_count,
    u.user_seeks,
    u.user_scans,
    u.user_lookups,
    u.user_seeks + u.user_scans + u.user_lookups AS total_reads,
    u.user_updates AS total_writes,
    u.user_updates - (u.user_seeks + u.user_scans + u.user_lookups) AS writes_minus_reads,
    u.last_user_seek,
    u.last_user_scan,
    u.last_user_lookup,
    u.last_user_update,
    CASE
        WHEN u.user_seeks + u.user_scans + u.user_lookups = 0 THEN 'NO READS OBSERVED'
        WHEN u.user_updates > (u.user_seeks + u.user_scans + u.user_lookups) THEN 'MORE WRITES THAN READS'
        ELSE 'ACTIVE'
    END AS review_reason
FROM sys.tables AS t
JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id
JOIN sys.indexes AS i
    ON i.object_id = t.object_id
JOIN IndexSizes AS sz
    ON sz.object_id = i.object_id
   AND sz.index_id = i.index_id
JOIN Usage AS u
    ON u.object_id = i.object_id
   AND u.index_id = i.index_id
WHERE t.is_ms_shipped = 0
  AND i.type = 2
  AND i.is_primary_key = 0
  AND i.is_unique = 0
  AND i.is_unique_constraint = 0
  AND i.is_hypothetical = 0
  AND u.user_updates >= @MinimumWrites
  AND u.user_updates > (u.user_seeks + u.user_scans + u.user_lookups)
ORDER BY writes_minus_reads DESC, used_mb DESC, schema_name, table_name, index_name;