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

The same result can be obtained for a specific table using the below query. Replace DBNAME & TABLENAME in the code below:

USE DBNAME

GO

SELECT DB_NAME(DATABASE_ID) AS [DATABASE NAME]

     , OBJECT_NAME(SS.OBJECT_ID) AS [OBJECT NAME]

     , I.NAME AS [INDEX NAME]

     , 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 O.name = 'TABLENAME'  -- Add this condition to filter for the specific table

ORDER BY OBJECT_NAME(SS.OBJECT_ID), USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES ASC;