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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

# re: Find Unused SQL 2005 Indexes for Current DB

great advice.

Monday, March 31, 2008 5:38 PM by werner

# re: Find Unused SQL 2005 Indexes for Current DB

Thanks...

very useful script

Tuesday, April 01, 2008 10:01 AM by Ronald Cancel

# Interesting Finds: April 1, 2008

Tuesday, April 01, 2008 10:05 AM by Jason Haley

# re: Find Unused SQL 2005 Indexes for Current DB

Thanks...

Is there a SQL Server 2000 version of this script?

Thursday, April 03, 2008 5:43 PM by David Parslow

Leave a Comment

(required) 
required 
(required) 
Enter the code you see below