Find Unused SQL 2005 Indexes
SELECT o.name AS object_name, i.name AS index_name
, i.type_desc, u.user_seeks, u.user_scans, u.user_lookups
, u.user_updates, u.last_user_seek, u.last_user_scan
, 'Drop index ' + i.name + ' on ' + o.name as DropIndexStatement
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 = DB_ID()
WHERE o.type <> 'S'
and isnull(u.user_updates,0) > 0
and i.type_desc <> 'HEAP'
ORDER BY (convert(decimal(19,4),ISNULL(u.user_seeks, 0))
+ ISNULL(u.user_scans, 0)
+ ISNULL(u.user_lookups, 0))/ISNULL(u.user_updates, 0) asc
, user_updates desc, o.name, i.name
The results of this will show you how many times each index has been used, and how often it’s been updated. If you have 0 seeks, scans, and lookups but a ton of updates, it’s a good bet that the index in question is a waste of time and can be deleted. Conversly, if you have an index that has lots of seeks/scans and minimal updates – it’s really earning it’s paycheck.
The query even includes the DROP command as part of the results, so all you need to do is cut, paste, and execute.
This entry was posted in DBA
. Bookmark the permalink