Index Usage - DMV

Find the usage of all index configured in the current database.


SELECT OBJECT_NAME(i.[object_id]) AS [Object Name],
CASE i.[index_id] WHEN 0 THEN N'HEAP' ELSE i.[name] END AS [Index Name],
i.index_id AS [Index ID]
FROM sys.indexes AS i INNER JOIN sys.objects AS o
ON i.[object_id] = o.[object_id]
WHERE NOT EXISTS( SELECT * FROM sys.dm_db_index_usage_stats AS u
WHERE u.[object_id] = i.[object_id]
AND u.[index_id] = i.[index_id] AND [database_id] = DB_ID())
AND OBJECTPROPERTY(i.[object_id],'IsUserTable') = 1
ORDER BY [Object Name], [Index ID] ASC



SELECT * FROM sys.dm_db_index_usage_stats

select *from sys.indexes

select * from sys.objects where name like '%have%'

select i.name, o.name from
sys.indexes AS i INNER JOIN sys.objects AS o
ON i.[object_id] = o.[object_id]
order by o.name


SELECT
b.name IndexName, object_name(b.[object_id]) TableName, b.type_desc, is_unique, fill_factor, is_padded, is_disabled, allow_row_locks, allow_page_locks, a.*
FROM sys.dm_db_index_usage_stats a (nolock), sys.indexes b(nolock)
where database_id = db_id() and a.[object_id] = b.[object_id] and OBJECTPROPERTY(b.[object_id],'IsUserTable') = 1
and b.type_desc != 'Heap'
order by TableName