SQLServerPedia

Excellent questions…

Why remove unused indexes? Because you’re creating extra I/O operations keeping unused indexes around, and if [hopefully] you have maintenance plans ensuring you have up-to-date statistics and that clustered indexes are defragmented, you’re wasting precious time in your maintenance window.
How can you detect unused indexes? Well if you’re using SQL Server 2005, you can use the [sys].[dm_db_index_usage_stats] DMV.

The following query will return the indexes defined in a given database, sorted by the most frequently updated index to the least, and will highlight indexes that haven’t been used since the instance was last brought online:

select OBJECT_NAME(idx.[object_id]) "base table"
,CASE WHEN ist.[object_id] IS NULL THEN '!! UNUSED INDEX: ' ELSE '' END+ISNULL([name],'HEAP') "index name"
,idx.[index_id],[type_desc],[is_primary_key]
,ISNULL([user_updates],0) "user updates"
,ISNULL([user_seeks],0) "user seeks"
,ISNULL([user_scans],0) "user scans"
,ISNULL([user_lookups],0) "user lookups"
from [sys].[indexes] idx
left join [sys].[dm_db_index_usage_stats] ist
on idx.[object_id]=ist.[object_id]
and idx.[index_id]=ist.[index_id]
and ist.[database_id]=db_id()
order by [user_updates] desc, [user_seeks], [user_scans], [user_lookups]

Clearly, the longer your instance has been online, the more confident you can be that the unused indexes specified actually are not being used. Also, it’s imperative that you always remain aware that (for example) month-end, quarterly, semi-annual, etc jobs that might rely on some less frequently used indexes. Still, you don’t necessarily have to drop an unused index, especially if it’s been defined in a small table or if you’re not noticing slowdowns in application processing…but you might consider changing your maintenance jobs to focus on the most heavily-used indexes first.