Index Usage Query

" How are my indexes being used? "

The following TSQL (from MSSQL Tips) shows us...

SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
         I.[NAME] AS [INDEX NAME], 
         USER_SEEKS, 
         USER_SCANS, 
         USER_LOOKUPS, 
         USER_UPDATES 
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    OBJECT_NAME(S.[OBJECT_ID]) = 'mytable'

It can easily be rolled up like this to produce 1 line per index >

SELECT   
 OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
  I.[NAME] AS [INDEX NAME], 
 SUM(USER_SEEKS) AS TOTAL_SEEKS, 
 SUM(USER_SCANS) AS TOTAL_SCANS, 
 SUM(USER_LOOKUPS) AS TOTAL_LOOKUPS, 
 SUM(USER_UPDATES) AS TOTAL_UPDATES 
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    OBJECT_NAME(S.[OBJECT_ID]) = 'tablename'
GROUP BY 
 OBJECT_NAME(S.[OBJECT_ID])
    ,I.[NAME] 
ORDER BY
 OBJECT_NAME(S.[OBJECT_ID])
    ,I.[NAME]