sql List Unused Indexes

Select
	object_name(i.object_id) As object_name
	,i.name 
	,s.user_updates
	,s.user_seeks
	,s.user_scans
	,s.user_lookups
	--,*
From MyDatabase.sys.indexes i 
Left Join MyDatabase.sys.dm_db_index_usage_stats s On s.object_id = i.object_id And i.index_id = s.index_id
Where
	objectproperty(i.object_id, 'IsIndexable') = 1
	and s.index_id is null or
	(s.user_updates > 0 and s.user_seeks = 0 
	and s.user_scans = 0 and s.user_lookups = 0)
Order By object_name(i.object_id) Asc
Helps identify indexes that have not been used and that could potentially be removed.

Updated: Sunday 17th July 2011, 11:25pm

There are 0 comments

Leave a comment of your own

Comments are currently closed.