Are my Indexes Being Used?
This is made very simple by the index usage DMV:  sys.dm_db_index_usage_stats

For example, this query I use very often to identify unused indexes:

SELECT o.name Object_Name,
       i.name Index_name, 
       i.Type_Desc 
 FROM sys.objects AS o
     JOIN sys.indexes AS i
 ON o.object_id = i.object_id 
  LEFT OUTER JOIN 
  sys.dm_db_index_usage_stats AS s    
 ON i.object_id = s.object_id   
  AND i.index_id = s.index_id
 WHERE  o.type = 'u'
-- Clustered and Non-Clustered indexes
  AND i.type IN (1, 2) 
-- Indexes without stats
  AND (s.index_id IS NULL) OR
-- Indexes that have been updated by not used
      (s.user_seeks = 0 AND s.user_scans = 0 AND s.user_lookups = 0 );


That query is taken verbatim from sql-server-performance.com but because their blogging platform puts in em-dashes and smart quotes it renders the code on their site un-copy-pasteable. (Why? Why on a code blog??) So I put it here in a form you can use without having to resolve syntax errors.

All due credit to them for this and many other useful code snippets I rely on.