How many indexes do you have that aren't used?

If you run this sql on a sql 2005 box then you will see which indexes aren't used in queries compared with the number of times they are updated.

select object_name(s.object_id) , *

from sys.dm_db_index_usage_stats s

join sys.indexes i on i.index_id = s.index_id

and s.object_id = i.object_id

where s.database_id = db_id()

order by user_seeks, user_scans, user_lookups



-
Published Wednesday, May 16, 2007 5:02 PM by simonsabin

Comments

Wednesday, May 16, 2007 5:35 PM by SQL_Menace

# re: How many indexes do you have that aren't used?

Simon,

You can expand on that by including the scan versus seek percentages, this is helpful to find the queries that generate a lot of index scans instead of seeks

here is something you can paste before the ,*

CASE s.user_seeks WHEN 0 THEN 0

ELSE s.user_seeks*1.0 /(s.user_scans + s.user_seeks) * 100.0 END AS SeekPercentage,

CASE s.user_scans WHEN 0 THEN 0

ELSE s.user_scans*1.0 /(s.user_scans + s.user_seeks) * 100.0 END AS ScanPercentage,

I have written a little about sys.dm_db_index_usage_stats here http://sqlservercode.blogspot.com/2006/09/sysdmdbindexusagestats.html

Also keep in mind that when SQL server restarts sys.dm_db_index_usage_stats  will be empty

Denis

Tuesday, January 13, 2009 4:13 PM by sdbatn1

# re: How many indexes do you have that aren't used?

Do we know if there is a easy way to find out the index usage in SQL 2000?