Luckily, there are many ways to find unused indexes, and I created a simple script that will get you started. It will help you identify the indexes that are not used, but server needed to create and update. Results are sorted by the number of writes (you can interpret it as a portion of server’s wasted resources). If you remove the dm_db_index_usage_stats.user_updates <> 0 predicate, the query will list all unused indexes, which now includes even indexes that server hasn’t done any work with.
SELECT
objects.name AS TableName,
indexes.name AS IndexName,
dm_db_index_usage_stats.user_seeks AS UserSeek,
dm_db_index_usage_stats.user_scans AS UserScans,
dm_db_index_usage_stats.user_updates AS Writes
FROM
sys.dm_db_index_usage_stats
INNER JOIN sys.objects ON dm_db_index_usage_stats.OBJECT_ID = objects.OBJECT_ID
INNER JOIN sys.indexes ON indexes.index_id = dm_db_index_usage_stats.index_id AND dm_db_index_usage_stats.OBJECT_ID = indexes.OBJECT_ID
WHERE
indexes.is_primary_key = 0
AND --remove to show all unused indexes
dm_db_index_usage_stats.user_updates <> 0
AND
dm_db_index_usage_stats.user_seeks = 0
AND
dm_db_index_usage_stats.user_scans = 0
ORDER BY
dm_db_index_usage_stats.user_updates DESC