SQL Server: usage statistics of each index
List, for each index of the user tables, the number of reads (seeks, scans, lookups) and writes, with the row count of the table.
This query joins sys.dm_db_index_usage_stats with the tables and indexes, for each index of the user tables: number of seeks, scans and lookups (reads), number of writes, and dates of the last scan and update. It only covers user databases (database_id > 4) and excludes heaps.
Query
SELECT OBJECT_NAME(ddius.[object_id], ddius.database_id) AS [object_name] , i.name,p.rows,
ddius.index_id ,
ddius.user_seeks ,
ddius.user_scans ,
ddius.user_lookups ,
ddius.user_seeks + ddius.user_scans + ddius.user_lookups
AS user_reads ,
ddius.user_updates AS user_writes ,
ddius.last_user_scan ,
ddius.last_user_update,*
FROM sys.dm_db_index_usage_stats ddius
INNER JOIN sys.tables t ON t.object_id = ddius.[object_id]
INNER JOIN sys.indexes i ON i.object_id=t.object_id AND i.index_id = ddius.index_id
LEFT OUTER join sys.partitions p ON t.object_id = p.OBJECT_ID AND i.index_id = p.index_id
WHERE ddius.database_id > 4 -- filter out system tables
AND OBJECTPROPERTY(ddius.OBJECT_ID, 'IsUserTable') = 1
AND ddius.index_id > 0 -- filter out heaps
--AND OBJECT_NAME(ddius.[object_id], ddius.database_id) = 'YourTableName'
--AND i.name LIKE 'YourIndexPrefix%'
ORDER BY p.rows,ddius.user_scans DESC
Notes
- The counters in
sys.dm_db_index_usage_statsare reset when the instance restarts: an index with no reads has only been unused since that restart. - The two commented lines filter on a table or an index name prefix.