Thursday, August 25, 2016

Index Usage Query Using DMV's

SELECT
obj.name AS TableName
, idx.name AS IndexName
, ius.index_id
, ius.user_seeks
, ius.user_scans
, ius.user_lookups
, ius.user_updates
,  ius.user_seeks + ius.user_scans + ius.user_lookups + ius.user_updates AS TotalUserUsages
, last_user_seek
, last_user_scan
, last_user_lookup
, last_user_update
, idx.fill_factor
, idx.is_padded
, idx.is_primary_key
, idx.is_disabled
FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.objects obj
ON ius.object_id  = obj.object_id 
INNER JOIN sys.indexes idx
ON ius.object_id  = idx.object_id 
AND ius.index_id = idx.index_id
--WHERE obj.name = '[table name]'
--This will find indexes without user usage
WHERE ius.user_seeks + ius.user_scans + ius.user_lookups + ius.user_updates = 0

ORDER BY is_disabled, index_id

No comments: