How to find unused indexes?

Try this script, It has helped me in the past:

-- Unused Index Script
-- Original Author: Pinal Dave 
SELECT TOP 25
o.name AS ObjectName
, i.name AS IndexName
, i.index_id AS IndexID
, dm_ius.user_seeks AS UserSeek
, dm_ius.user_scans AS UserScans
, dm_ius.user_lookups AS UserLookups
, dm_ius.user_updates AS UserUpdates
, p.TableRows
, 'DROP INDEX ' + QUOTENAME(i.name)
+ ' ON ' + QUOTENAME(s.name) + '.'
+ QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id 
AND dm_ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID
FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p
ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1
AND dm_ius.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC

http://blog.sqlauthority.com/2011/01/04/sql-server-2008-unused-index-script-download/

I have found that Brent Ozar Unlimited's free BlitzIndex script (written by Kendra Little) is the best way to isolate unsused indexes (as well as indexes that are beneficial to be added, indexes that are duplicating the work of other indexes etc)

http://www.brentozar.com/blitzindex/

It will tell you the number of times any index has been read since the last time the statistic counts were reset (or an index was created/recreated).

I seem to remember Brent Ozar saying in webcast that a good rule of thumb is no more than 10 indexes for a table that is read often, 20ish for tables that are static/historical/archived data that will not change frequently.

If you are still having issues with import speed is there a time when the database isn't being actively queried (perhaps this is out of office hours). It may be beneficial to drop the index, import the data and then re-apply the indexes. (Statistics will be reset of course.) The reason for this is that the index(es) will be updated as each record goes in, pages will get reordered and that takes time and disk I/O. Building the indexes after requires a single scan of the table.

No hard and fast rule you may have to experiment with this depending on the types of index and the data involved. Indexes should be regularly reviewed as needs/queries change.