SQL Server Finding Unused Indexes

In SQL 2005 Microsoft introduced the DMV’s sys.dm_db_missing_index * that allows you to find missing indexes, or at least what SQL Server thinks is a missing index. Unfortunately there is no corresponding tool that allows you to find unused indexes. There is however the tools available that allows you to get this information with a little elbow grease. The query below finds unused and minimally used indexes. The query is fairly straight forward, but there are a couple of points I’d like to bring to light. The primary DMV used here is sys.dm_db_index_usage_stats which records everytime an index is used whether it’s a scan, seek, lookup, or is updated. This starts gathering when SQL Server starts, and is incremented upon each use. If an index is not used at all it doesn’t appear in the DMV. This point is critical to understand since the underlying point of this query is to find indexes that are being updated, but not queried, meaning your spending your resources to update an object that’s never used. This is the point behind the threshold variable that gives you the flexibility to not only find unused indexes but indexes that might only queried 10 times, or whatever you set the threshold to. The reason’s why an index might only a few uses can vary from ad-hoc statements during investigation to once a night rollup jobs.


Declare @Threshold int
Set @Threshold = 0

Create Table #AllIndexes
(TableName varchar(100),
IndexName varchar(100),
indexID int,
ObjectID int)

Create Table #IndexUsage
(
object_id int,
index_id int,
user_seeks int,
user_scans int,
user_lookups int,
user_updates int,
last_user_seek datetime,
last_user_scan datetime,
last_user_lookup datetime,
last_user_update datetime)

insert into #IndexUsage(object_id,index_id,user_seeks,
user_scans,user_lookups,user_updates,last_user_seek,
last_user_scan,last_user_lookup,last_user_update)

Select object_id,index_id,user_seeks,
user_scans,user_lookups,user_updates,last_user_seek,
last_user_scan,last_user_lookup,last_user_update
from sys.dm_db_index_usage_stats
where database_id = db_id('AdventureWorks')

insert into #AllIndexes
select t.name, i.name, i.index_id, t.object_id
from Adventureworks.sys.indexes i
inner join Adventureworks.sys.tables t
on
i.object_id = t.object_id
where i.type != 0

select * from #AllIndexes ai
left join #IndexUsage iu
on
ai.indexid = iu.index_id
and
ai.objectid = iu.object_id
where (user_seeks is null or user_seeks <= @Threshold)
and (user_scans is null or user_scans <= @Threshold)
and (user_lookups is null or user_lookups <= @Threshold)
order by user_updates desc, TableName, IndexID