Index usage report

SQL Server 2005/2008 : Index usage report

The new missing index report makes it easy to identify what new indexes will improve the performance. But if you do not investigate all the recommended indexes before creating them then you might end up will unnecessary indexes and thereby increasing the write times on the tables.

So I run the following query once in a while to identify which indexes are not being used and then drop them. Take a look at the user_updates and if it is significantly higher than (user_seeks+user_scans) then you might want to drop them,

SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],

         I.[NAME] AS [INDEX NAME],

         USER_SEEKS,

         USER_SCANS,

         USER_LOOKUPS,

         USER_UPDATES, last_user_seek, last_user_scan, last_user_lookup

FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S

         INNER JOIN SYS.INDEXES AS I

           ON I.[OBJECT_ID] = S.[OBJECT_ID]

              AND I.INDEX_ID = S.INDEX_ID

WHERE    I.[NAME] not like 'PK%' and s.database_id = DB_ID() and

OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1   order by user_updates desc