Always trying to figure out “if” and “how much” my indexes are being used.
This posting has the information:
http://www.mssqltips.com/sqlservertip/1239/how-to-get-index-usage-information-in-sql-server/
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 OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
Conversely, which indexes are being UPDATED but NEVER used, not mirrored, and is not disabled already (since the system has been up):
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 inner join sys.objects as o on i.object_id = o.object_id WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 and user_updates > 100 and is_disabled <> 1 and (USER_SEEKS + USER_SCANS + USER_LOOKUPS) = 0 AND o.is_ms_shipped = 0