Make sure the SQL Server hasn’t been restarted for a longer period.
SELECT TOP 50
o.[Name] AS [ObjectName]
, i.[Name] AS IndexName
, i.index_id AS IndexID
, dm_ius.user_seeks AS UserSeek
, dm_ius.user_scans AS UserScans
, dm_ius.user_lookups AS UserLookups
, dm_ius.user_updates AS UserUpdates
, p.TableRows
, 'DROP INDEX ' + QUOTENAME(i.[Name])
+ ' ON ' + QUOTENAME(s.[Name]) + '.'
+ QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'Drop statement'
FROM [sys].dm_db_index_usage_stats dm_ius
INNER JOIN [sys].indexes i ON i.index_id = dm_ius.index_id
AND dm_ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN [sys].objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID
INNER JOIN [sys].schemas s ON o.SCHEMA_ID = s.SCHEMA_ID
INNER JOIN (SELECT SUM(p.ROWS) TableRows, p.index_id, p.OBJECT_ID
FROM [sys].partitions p GROUP BY p.index_id, p.OBJECT_ID) p
ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1
AND dm_ius.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC;
GO
Other Refrences:
