You are here:Home » tsql » Find out Index Statistics and Usage

Find out Index Statistics and Usage

--Unused indexes and tables
SELECT object_name(i.object_id) AS ObjectName
, i.name as IndexName
, i.index_id
, user_seeks
, user_scans
, user_lookups
, user_updates
FROM sys.indexes i
INNER JOIN sys.objects o
ON o.object_id = i.object_id
LEFT JOIN sys.dm_db_index_usage_stats s
ON i.object_id=s.object_id
AND i.index_id=s.index_id
AND database_id = DB_ID()
WHERE objectproperty(o.object_id,'IsUserTable') = 1
AND s.index_id IS NULL
ORDER BY objectname,i.index_id,indexname ASC


--Index usage. Least used appear first.
SELECT object_name(s.object_id) AS ObjectName
, s.object_id
, i.name as IndexName
, i.index_id
, user_seeks
, user_scans
, user_lookups
, user_updates
, last_user_Scan
, last_user_Seek
, last_user_Update
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i
ON i.object_id = s.object_id
AND i.index_id = s.index_id
WHERE database_id = db_id ()
AND objectproperty(s.object_id,'IsUserTable') = 1
ORDER BY (user_seeks + user_scans + user_lookups + user_updates) ASC


--Index scan appear more than index seeks
SELECT object_name(s.object_id) AS ObjectName
, s.object_id
, i.name as IndexName
, i.index_id
, user_seeks
, user_scans
, user_lookups
, user_updates
, last_user_Scan
, last_user_Seek
, last_user_Update
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i
ON i.object_id = s.object_id
AND i.index_id = s.index_id
WHERE database_id = db_id ()
AND objectproperty(s.object_id,'IsUserTable') = 1
AND user_scans > user_seeks
ORDER BY user_scans DESC


--Index updated more than it is used
SELECT object_name(s.object_id) AS ObjectName
, s.object_id
, i.name as IndexName
, i.index_id
, user_seeks
, user_scans
, user_lookups
, user_updates
, last_user_Scan
, last_user_Seek
, last_user_Update
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i
ON i.object_id = s.object_id
AND i.index_id = s.index_id
WHERE database_id = db_id ()
AND objectproperty(s.object_id,'IsUserTable') = 1
AND user_updates > (user_scans + user_seeks)
AND s.index_id > 1
ORDER BY user_updates DESC