Can I delete this index? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Can I delete this index?

Author

Topic

jlashmet
Starting Member

1 Post

Posted - 2008-02-25 : 18:03:02

So I've had the need in the past few days to drop unused indexes from our database. I've done some research and found many people using the sys.dm_db_index_usage_stats view to determine which indexes are not used. The problem I'm facing is that different queries I've found return different results. The approaches I've seen determine an index is unused based on the following criteria:

1. The index is not found at all in the sys.dm_db_index_usage_stats view
2. Some or all of the following columns return 0 - user_seeks, user_scans, user_lookups, user_updates, system_seeks, system_scans, system_lookups

My question is what approach is correct? My gut says an index is not being used if the user_seeks, user_scans, and user_lookups are 0. However, some approaches look at user_updates and also the system columns as well. If the user_updates is > 0, doesn't that just mean records are being inserted or updated in the table? That wouldn't indicate the index is being used for searches. Can someone shed some light on this?

Thanks,
Jason

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-02-25 : 18:54:16

This query returns the list of indexes ordered by the usage. Indexes that are listed at the top of the result-set may not be as beneficial as indexes listed at the bottom.

--
select object_name(i.object_id) as tablename, i.name as indexname,
s.user_seeks + s.user_scans + s.user_lookups + s.user_updates as usage
from sys.indexes i
inner join sys.dm_db_index_usage_stats s
on s.object_id = i.object_id
and s.index_id = i.index_id
and s.database_id = db_id()
where objectproperty(i.object_id, 'IsUserTable') = 1
and i.index_id > 0
order by usage

but test it before dropping

cybertron
Starting Member

4 Posts

Posted - 2008-02-26 : 03:46:45

I've written about indexing at my blog. You can visit to find your answer.

Right here, You can find unused index using query sql
http://cybertron-network.blogspot.com/2008/02/how-to-find-unused-index-on-sql-server.html

Right here, you can find index that must be dropped.
http://cybertron-network.blogspot.com/2008/02/maintaining-index-on-sql-server-2005.html

I hope my answer is helping you ...

"We give you that we know, We share to you that we've learned, We send to you that we need to be discussed"
http://cybertron-network.blogspot.com

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-02-26 : 14:27:22

You can't drop index directly without analyzing. If your index is not used for 3-4 months ,then i think its better to drop that index because index depends on Query.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-02-26 : 15:19:41

Also , all because an index is used infrequently , it may also be used just by a weekly job.
Analyse the queries you will be running against the database

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources