SQL Server Blog

As databases evolve, you may find that some indexes are no longer used. This is something I have seen a lot in my current job.

This script will allow you to see which indexes are used and whether they are used for seeks, scans or key lookups.

SELECT
   SC.[name] + ‘.’ + OBJECT_NAME(S.[object_id]) AS [Object Name],
   I.[name] AS [Index Name],
   S.[user_seeks] AS [User Seeks],
   S.[user_scans] AS [User Scans],
   S.[user_lookups] AS [User Lookups]
FROM sys.dm_db_index_usage_stats S INNER JOIN sys.indexes I
   ON I.[object_id] = S.[object_id] AND I.[index_id] = S.[index_id]
INNER JOIN sys.tables T
   ON T.[object_id] = I.[object_id]
INNER JOIN sys.schemas SC
   ON T.[schema_id] = SC.[schema_id]
WHERE
   OBJECTPROPERTY(S.[object_id],’IsUserTable’) = 1 AND
   OBJECTPROPERTY(S.[object_id], ‘IsMSShipped’) = 0 AND
   S.[database_id] = DB_ID(DB_NAME())
ORDER BY
   SC.[name] + ‘.’ + OBJECT_NAME(S.[object_id]), I.[index_id]