How to know the unused indexes

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)

How to know the unused indexes

Author

Topic

venkath
Posting Yak Master

202 Posts

Posted - 2008-12-04 : 04:53:37

Hi all

I would like to get rid off all the un used indexes in the database.

Can you tell me a way to find this.

Thanks

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-04 : 05:06:53

You can use built-in Standard reports on each database (index usage statistics and index physical statistics) through Management Studio

Carat
Yak Posting Veteran

92 Posts

Posted - 2008-12-04 : 08:19:37

We use the following query to find the unused indexes:

select d.Name as DatabaseName, schema_name(t.schema_id) + '.' + object_name(i.object_id) as TableName, i.name as IndexName, i.Type_Desc,
user_seeks, user_scans, user_lookups, user_updates, last_user_seek, last_user_scan
from sys.indexes i
left outer join sys.dm_db_index_usage_stats u on (i.object_id = u.object_id and i.index_id = u.index_id)
left outer join sys.databases d on (d.database_id = db_id())
left outer join sys.tables t on (t.object_id = i.object_id)
where d.Name = ' --> DatabaseName <--'
and t.is_ms_shipped = 0
order by user_seeks + user_scans + user_lookups + user_updates desc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-04 : 08:32:31

http://blog.sqlauthority.com/2008/02/11/sql-server-2005-find-unused-indexes-of-current-database/

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-04 : 08:53:29

quote:
Originally posted by darkdusky

You can use built-in Standard reports on each database (index usage statistics and index physical statistics) through Management Studio

Are you sure you have these reports in Management studio? I can't find mine.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-04 : 09:07:26

quote:
Originally posted by sodeep

quote:
Originally posted by darkdusky

You can use built-in Standard reports on each database (index usage statistics and index physical statistics) through Management Studio

Are you sure you have these reports in Management studio? I can't find mine.

you've such reports available in sql mgmnt studio

http://blogs.msdn.com/buckwoody/archive/2007/10/09/sql-server-management-studio-standard-reports-overview.aspx

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-04 : 09:10:29

O... I see . It needs SP2.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-04 : 09:14:30