LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon