Dynamic Management View sys.dm_db_index_usage_stats shows counts of different types of index operations. This DMV can be used to know whether particular index is used frequently or not or which index is incurring maintenance overhead.
Let us take an example.
SELECT OBJECT_NAME( object_id ) Table_Name, * FROM sys.dm_db_index_usage_stats
WHERE DB_NAME( database_id ) = ' MyDB'
AND OBJECT_NAME( object_id ) = 'Order' -- Commenting this line displays result for all tables
Column index_id specifies type of index being used. 0 for heap, 1 for clustered index and >1 for non-clustered index.
user_seeks, user_scans, user_lookups and user_updates columns specify no. of respective operations by user queries.
last_user_seek, last_user_scan, last_user_lookup and last_user_update columns specify time of last respective operations done.
Similarly, there are other 8 columns which are recorded against system queries.
If any of the indexes are not used by queries, you may want to consider dropping the index and creating other index.
See BOL for more details.