While indexes can increase the read performance, they can decrease the write performance during any updates. Any time a query modifies the data in a table the indexes on the data also must change. Any database that has a large number of transactions modifying the data will perform good with fewer indexes. But the key is striking a balance between fewer indexes that can improve the write performance and more indexes to improve the read performance.
Below is a query that can give useful statistics of how the indexes are being used. If the number of seeks and number of scans is 0 with the number of updates significantly large, then there is no point in having the index. The below is a good starting point for assessing and distinguishing between useful and unused indexes.
USE DBNAME
GO
SELECT DB_NAME(DATABASE_ID) AS [DATABASE NAME]
, OBJECT_NAME(SS.OBJECT_ID) AS [OBJECT NAME]
, I.NAME AS [INDEX NAME]
-- , I.INDEX_ID AS [INDEX ID]
, USER_SEEKS AS [NUMBER OF SEEKS]
, USER_SCANS AS [NUMBER OF SCANS]
, USER_LOOKUPS AS [NUMBER OF BOOKMARK LOOKUPS]
, USER_UPDATES AS [NUMBER OF UPDATES]
FROM
SYS.DM_DB_INDEX_USAGE_STATS SS
INNER JOIN SYS.INDEXES I
ON I.OBJECT_ID = SS.OBJECT_ID
AND I.INDEX_ID = SS.INDEX_ID
INNER JOIN SYS.OBJECTS O on O.OBJECT_ID=I.OBJECT_ID
WHERE DATABASE_ID = DB_ID()
AND OBJECTPROPERTY (SS.OBJECT_ID,'IsUserTable') = 1
AND SS.OBJECT_ID = o.object_id--OBJECT_ID(@TABLENAME)
ORDER BY
OBJECT_NAME(SS.OBJECT_ID),USER_SEEKS
, USER_SCANS
, USER_LOOKUPS
, USER_UPDATES ASC
