Based on this query, if I see a low amount of total reads (very close to 0 or 0, like 1 or 2) and a high or moderate amount of user updates (I couldn't find inserts or deletes with this query) with a large row count, I should in theory remove the index.
SELECT DISTINCT OBJECT_NAME(s.[object_id]) AS ObjectName , p.rows TableRows , i.name AS [INDEX NAME] , (user_seeks + user_scans + user_lookups) AS TotalReads , user_updates UserUpdates FROM sys.dm_db_index_usage_stats s INNER JOIN sys.indexes i ON i.[object_id] = s.[object_id] AND i.index_id = s.index_id INNER JOIN sys.partitions p ON p.object_id = i.object_id WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1 AND s.database_id = DB_ID() AND i.name IS NOT NULL ORDER BY (user_seeks + user_scans + user_lookups) ASC
I want to cross-check the accuracy of this assumption here. For instance, an index that has existed for over a year but has never been read, but highly updated, seems like it would be a bad idea to have. Is there a scenario where this assumption is invalid?