I'm using SYS.DM_DB_INDEX_USAGE_STATS and SYS.INDEXES to get an idea of index usage. The table in question was not optimally designed and we have to live with that. It has about 120 columns and 10 million rows. It currently has 18 indexes, some of which INCLUDE up to 20 columns (not counting the columns used for SEEKing). A few indexes are rarely used relative to the others. Before I get rid of them though I want to be sure that I'm not going to cause some report that only runs once a week, to go from a minute to say an hour to run. In other words just because an index is relatively rarely used, doesn't mean that it's not doing an important job when it is used. I would be able to work out which dependencies are using the index if I could capture the SQL that is being executed. I've had a quick look at the profiler and I can't seem to see any settings that return index names. The sys.dm_db_missing_index* DMOs don't seem to really be what I want. It's not so much a case of not enough indexes as working out whether we have some that are expendable by minor tweaks to other indexes or mods to the affected SQL.
If you have a good plan cache, you can adopt Jon's idea of [Finding Key Lookups inside the Plan Cache]. Ideally, I'd take snapshots of the plan cache (frequency should depend on your workload) to persist to a table for further analysis. As far as the SQL Trace goes, you'll have to capture the execution plan and look inside of them for the index references -- could be very expensive! Also, you could take snapshots on index_usage_stats to analyze the patterns, sometimes it helps to look at this DMV over time. Very good question! :
Capturing the full execution plans from either trace events or extended events is very expensive. I wouldn't recommend it without some pretty severe filters in place, and even then I'd only recommend using extended events. That said, I'd probably use extended events to capture the query call metrics. Then, where possible, conflate that with information from the cache and the plans there. It's never going to be perfect, but you should be able to at least identify if queries are running slower over time.