I'd found a script that persists index usage stats into a table so that restarts don't cause them all to be reset. It runs hourly and updates the stats for each index in the table.

This was good, but it in creating a single record for each index on the server, it did not provide the information I needed to decide whether or not to retain an index. I've come to the conclusion that usage trends are more important than simply the raw numbers of how many times the index gets used. So I modified the script to create a new snapshot of the index usage every two hours, storing the delta values so I can compute stats like seeks per day. I've created a report to graph the daily usage of each index so now I can see the trends that help me decide whether to drop little used indexes. For example, two indexes might have 4000 user seeks total, but one might have accumulated those seeks three weeks ago, and has not registered any in the past three weeks. Armed with this information, I can investigate: perhaps refreshing the statistics to see if that gets the index used again, and if not, dropping it totally.

What is still missing from my analysis is a list of queries that are currently using each index so that, when a query does stop getting used, I can display the execution plan for the query that was using the index to see if the optimizer still calls for the index to be used. If so, this would be a good indication that the report that was using that query has either been changed so the query is not being used, or the report is not being used. This can tell me whether I should drop the index, or, if the report is performing poorly as a result of the change, modify the index so the changed report goes back to using it.