- sys.indexes, sys.objects and sys.dm_db_index_usage_stats
- Checks where writes are greater than the reads i.e. index maintenance is higher than the benefit it is serving during read operation on that table; an indicator that probably not a good index
SELECT SCHEMA_NAME(o.[schema_id]) AS [Schema Name] , OBJECT_NAME(s.[object_id]) AS [Table Name] , i.name AS [Index Name], i.index_id , i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor , s.user_updates - (s.user_seeks + s.user_scans + s.user_lookups) AS [Difference] , s.user_updates AS [Total Writes], s.user_seeks + s.user_scans + s.user_lookups AS [Total Reads] FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK) INNER JOIN sys.indexes AS i WITH (NOLOCK) ON s.object_id = i.object_id AND i.index_id = s.index_id INNER JOIN sys.objects AS o WITH (NOLOCK) ON i.object_id = o.object_id WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1 AND s.database_id = DB_ID() AND s.user_updates > (s.user_seeks + s.user_scans + s.user_lookups) AND i.index_id > 1 AND i.type_desc = N'NONCLUSTERED' AND i.is_primary_key = 0 AND i.is_unique_constraint = 0 AND i.is_unique = 0 ORDER BY Difference DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE)
Full details on Microsoft’s Documentation page