:::: MENU ::::

Unused indexes

Indexes are only really ever useful for finding rows, when they are not being used they only take up space and take time to update, use something like below to find indexes that are never used for what they are good for.

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
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'
and u.index_id is null or (u.user_updates > 0 and u.user_seeks < 10 and u.user_scans < 10 and u.user_lookups < 10) ORDER BY u.user_updates desc


So, what do you think ?