Script to identify unused indexes

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