Skip to content

PopSQL: limited support.

Migrate now →

Indexes written to but never read

Non-clustered indexes with updates and no seeks, scans or lookups since the last restart.

SQL Server · SQL
SELECT TOP 25
       OBJECT_SCHEMA_NAME(i.object_id) + '.' + OBJECT_NAME(i.object_id) AS table_name,
       i.name AS index_name,
       u.user_updates AS writes,
       u.user_seeks + u.user_scans + u.user_lookups AS reads
FROM sys.indexes i
JOIN sys.dm_db_index_usage_stats u
       ON u.object_id = i.object_id AND u.index_id = i.index_id AND u.database_id = DB_ID()
WHERE i.type_desc = 'NONCLUSTERED'
  AND i.is_primary_key = 0
  AND i.is_unique = 0
  AND u.user_seeks + u.user_scans + u.user_lookups = 0
ORDER BY u.user_updates DESC

This query is part of the "SQL Server health" pack: connect the database in DataRunner and install the whole pack — queries, dashboard and checks — at once.