How do you identify unused or duplicate indexes that might be safely removed to improve write performance?

You can query dynamic management views like sys.dm_db_index_usage_stats to see how often each index is actually being used for reads versus how much overhead it adds to writes, and compare index definitions across a table to spot duplicate or overlapping indexes that provide little additional benefit while still slowing down every insert and update.

SELECT OBJECT_NAME(s.object_id) AS TableName, i.name AS IndexName,
  s.user_seeks, s.user_scans, s.user_updates
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id;