Through Below script we can find out unused indexes in a database. Sometimes we have created
some indexes in a table but forget to use them or it is used very rarely.
— List out unused indexes in database
SELECT obj.name Object_Name,
idx.name Index_name,
idx.Type_Desc
FROM sys.objects AS obj
JOIN sys.indexes AS idx
ON obj.object_id = idx.object_id
LEFT OUTER JOIN
sys.dm_db_index_usage_stats AS ius
ON idx.object_id = ius.object_id
AND idx.index_id = ius.index_id
WHERE obj.type = ‘u’
AND idx.type IN (1, 2)
AND (ius.index_id IS NULL) OR
(ius.user_seeks = 0 AND ius.user_scans = 0 AND ius.user_lookups = 0 );