Index Usage DMV
-
Posted on June 17, 2009 by Derek Dieter
-
-2
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
[cc lang=”sql”]
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
[/cc]
Post a comment
- Comments (RSS)
- Trackback
- Permalink