One of the most important maintenance tasks for a database is making sure you have the right indexes. Sql Server 2005 makes this a lot easier than in the past by the creation of the dynamic management views.
I recently went on a cleaning spree in our database and wanted to find all the indexes that really aren't being used. I took the approach of finding the indexes that have a lot of updates to them but don't have many scans/seeks or user lookups. This tells us that we are inserting into the index but never really using the index in our queries. You will notice I ordered it by a ratio of the total seeks, scans and user lookups and divided that by the user updates. The code is below.
SELECT o.name AS object_name, i.name AS index_name
, i.type_desc, u.user_seeks, u.user_scans, u.user_lookups, u.user_updates
FROM sys.indexes i
JOIN sys.objects o ON i.object_id = o.object_id
LEFT JOIN sys.dm_db_index_usage_stats u ON i.object_id = u.object_id
AND i.index_id = u.index_id
AND u.database_id = DB_ID()
WHERE o.type <> 'S'
and isnull(u.user_updates,0) > 0
and i.type_desc <> 'HEAP'
ORDER BY (convert(decimal(19,4),ISNULL(u.user_seeks, 0)) + ISNULL(u.user_scans, 0)
+ ISNULL(u.user_lookups, 0))/ISNULL(u.user_updates, 0) asc, o.name, i.name