SQL Server 2005 how to find a rarely used index?

For a SQL Server database having an index is always beneficial and from performance point of view too.

But how about the clustered index which is occupying space on the database that is not used by any of the queries in the database. The Query Optimizer thinks that column statistics are useful, there is also a good chance that adding an appropriate index to this same column would be useful.

One of the indexing tips from [SSP] refers that before you can identify and create optimal indexes for your tables, you must thoroughly understand the kinds of queries that will be run against them. This is not an easy task, especially if you are attempting to add indexes to a new database.

How do you identify which queries are run the most often, and which are the most resource intensive?

In SQL Server 2005 you could take help of DMVs  and this BLOG-Post too to obtain that information.

How about finding rarely used indexes on a database?

Again DMV comes as a saviour and SQL Development team provided another useful query:

declare @dbid int
select @dbid = db_id()
select objectname=object_name(s.object_id), s.object_id
, indexname=i.name, i.index_id
, user_seeks, user_scans, user_lookups, user_updates
from sys.dm_db_index_usage_stats s,
sys.indexes i
where database_id = @dbid
and objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
order by (user_seeks + user_scans + user_lookups + user_updates) asc

Bear in mind if the number and type of accesses are tracked using another DMVs then this may get you information that may not be useful to the entire concept of finding rarely used indexes. So in any case you must run the code on a 2 SQL 2005 instances with a connection to same database.