While doing an index analysis activity i had to find the indexes are hardly being used in our production DB’s. I wrote this script to acheive the same

SELECT case when os.is_published = 1 then 'Replicated' else 'Not Replicated'
end as ReplicatedorNot,
OBJECT_NAME(idx.object_id) AS TableName ,
idx.name as Index_name,
istat.user_lookups,
istat.user_scans,
istat.user_seeks,
istat.user_updates
FROM sys.dm_db_index_usage_stats AS istat
JOIN sys.indexes AS idx ON idx.index_id = istat.index_id
join sys.objects os
on idx.object_id = os.object_id
AND idx.object_id = istat.object_id
WHERE
istat.database_id = DB_ID('databasename') -- type the name of the Db
and object_name(idx.object_id) not like 'Ms%'
and object_name(idx.object_id) not like 'sys%'
and object_name(idx.object_id) not like 'id_%'
and object_name(idx.object_id) not like 'conflict%'
and object_name(idx.object_id) not like 'Any other tables that you like to avoid%'
AND idx.is_unique_constraint = 0 -- no unique indexes
AND idx.is_primary_key = 0
AND idx.is_disabled = 0
AND idx.type > 1 -- don't consider heaps/clustered index
AND ( ( istat.user_seeks + istat.user_scans +istat.user_lookups )  <  istat.user_updates
or (istat.user_seeks = 0 AND istat.user_scans = 0 AND istat.user_lookups = 0))
order by (istat.user_seeks + istat.user_scans +
istat.user_lookups)

Tagged: Indexes, Non-Clustered Index, SQL Server