Index Usage DMV

Index Usage DMV

The following DMV query retrieves the usage statistics for existing indexes.

• User Seeks – A high number indicates a well utilized index.
• User Scans – Number of times the index has been scanned. Could indicate improper ordering of the composite columns
• User Lookups – Indicates a different index was used for criteria and the actual data was looked up from this index for the select list
• User Updates – Number of times the index was updated with additional records

SELECT
    ObjectName      = object_schema_name(idx.object_id) + '.' + object_name(idx.object_id)
    ,IndexName      = idx.name
    ,IndexType      = CASE
                        WHEN is_unique = 1 THEN 'UNIQUE '
                        ELSE '' END + idx.type_desc
    ,User_Seeks     = us.user_seeks
    ,User_Scans     = us.user_scans
    ,User_Lookups   = us.user_lookups
    ,User_Updates   = us.user_updates
FROM sys.indexes idx
LEFT JOIN sys.dm_db_index_usage_stats us
    ON idx.object_id = us.object_id
    AND idx.index_id = us.index_id
    AND us.database_id = db_id()
WHERE object_schema_name(idx.object_id) != 'sys'
ORDER BY us.user_seeks + us.user_scans + us.user_lookups DESC