Indexes are one of the most powerful performance tools in SQL Server — and one of the most overlooked sources of overhead. Every index you create must be maintained on every INSERT, UPDATE, and DELETE operation. Unused indexes cost you disk space, backup time, memory, and write performance, without providing any read benefit. Finding and removing them is a routine but high-impact DBA task.

How SQL Server Tracks Index Usage

SQL Server populates SYS.DM_DB_INDEX_USAGE_STATS with a row for each index the first time it is accessed after an instance restart. The counters track seeks, scans, lookups, and updates. Key implications:

• Indexes not listed in the view have never been used since the last restart.
• Indexes listed with zero seek/scan/lookup counters have been maintained (writes happened) but never read.
• Counters reset on every instance restart — run these queries after a representative period of normal workload, ideally several weeks or more.

Important: always discuss findings with the application team before dropping anything. Some indexes are used only by batch processes, month-end reports, or features that aren’t active year-round.

Query 1: Indexes Never Used (not in the DMV)

-- Indexes that have never been used since the last instance restart
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;

Query 2: Indexes That Exist but Have Zero Read Activity

-- Indexes present in the DMV but with zero seek/scan/lookup activity
DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())

SELECT u.*
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 o.[type] <> 'S'
  AND i.[type_desc] <> 'HEAP'
  AND u.[user_seeks] + u.[user_scans] + u.[user_lookups] = 0
ORDER BY i.name;

The candidates identified by these queries are a starting point for analysis, not an immediate drop list. Cross-reference with query plans, application release notes, and scheduled job schedules before making any changes.