Identifying indexes which can be deleted

Dynamic management views contain a lot of important information which can be used to manage the data more efficiently. The first instinct when asked to performance tune a database is to add new indexes and improve the not so well performing queries. However a more logical view is to beign with removing the unused indexes first before adding any new ones as adding an index also has an overhead.

When working on indentifying the unsed indexes the following query which is basec on the dynamic management view sys.dm_db_index_usage_stats can be used :

SELECT o.name
, indexname=i.name
, i.index_id
, reads = user_seeks + user_scans + user_lookups
, writes = user_updates
, rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id)
, CASE WHEN s.user_updates < 1 THEN
100
ELSE
1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates
END AS reads_per_write
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i
ON i.index_id = s.index_id AND s.object_id = i.object_id
INNER JOIN sys.objects o
on s.object_id = o.object_id
INNER JOIN sys.schemas c
on o.schema_id = c.schema_id
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
AND s.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 10000
ORDER BY reads


The column reads_per_write is used to identify the indexes which are the least used. The indexes which are most often written to and the least read from are the ones which should be gotten rid of. The most useful index is the one which is the most used.