How many indexes do you have that aren't used? - SimonS Blog on SQL Server Stuff

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 16 May 2007 17:02 by simonsabin
Filed under: , , ,

Comments

16 May 2007 17:35 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