Welcome to AspAdvice Sign in | Join | Help

Find Unused SQL 2005 Indexes for Current DB

Gregg Stark sent me a nice query a while back that's a great tool for optimizing indexes (indices) in SQL Server 2005.  Here's the query:

SELECT    o.name AS object_name, i.name AS index_name
   , i.type_desc, u.user_seeks, u.user_scans, u.user_lookups
, u.user_updates, u.last_user_seek, u.last_user_scan
, 'Drop index ' + i.name + ' on ' + o.name as DropIndexStatement
FROM sys.indexes i
JOIN sys.objects o ON  i.object_id = o.object_id
LEFT JOIN  sys.dm_db_index_usage_stats u ON i.object_id = u.object_id
          AND    i.index_id = u.index_id
          AND    u.database_id = DB_ID()
WHERE    o.type <> 'S'    
and isnull(u.user_updates,0) > 0
and i.type_desc <> 'HEAP'
ORDER BY    (convert(decimal(19,4),ISNULL(u.user_seeks, 0)) 
+ ISNULL(u.user_scans, 0) 
+ ISNULL(u.user_lookups, 0))/ISNULL(u.user_updates, 0) asc
, user_updates desc, o.name, i.name

The results of this will show you how many times each index has been used, and how often it's been updated.  If you have 0 seeks, scans, and lookups but a ton of updates, it's a good bet that the index in question is a waste of time and can be deleted.

The query even includes the DROP command as part of the results, so all you need to do is cut, paste, and execute.  Beautiful.  I just found one in my production database that has 1.4M user_updates and has never once been used.  I think it can go.

On the other end of the spectrum, the query will show you which indices are really earning their paycheck.  I have one that's had 2 user_updates and 18.3M seeks and 5K scans.  It's the PK for a very commonly joined lookup table (my top indexes are all clustered PKs - I think that's to be expected).

Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!
Published Monday, March 31, 2008 3:14 PM by ssmith

Comments

New Comments to this post are disabled