We have many indices which seem to be used a few times (seek > 0 but say less than 1000). Many of these indices are bad and I want to drop them but I am afraid that there is some badly written query that is relying on this badly defined index. How do I trap those queries that are using these indices?
Before dropping those indexes, few points should be noted. sys.dm_db_operational_Stats data get cleared when you had a server restart, disable then enabled the index or when a drop and create happened like wise sys.dm_db_index_usage_Stats also has similar limitations, so if you are good with above, then the next is. There may be few queries that are seldom used, like annual report or monthly reports your indexes may be used by these queries but their stats may be minimal in DMV, so you should getaway with those. The better way to filter them is to find the queries or SP that are runs less frequently (you may sit with your developer to find those) then the indexes used in that query by analyzing its execution plans and document them. There is no easy way to find which indexes queries relationship without analyzing its execution plans, its not that easy to remove less used index, disabling\dropping the indexes may results in poor performance for some queries. One approach is to find the procedure stats and the query stats from sys.dm_exec_procedure_stats and Query_Stats find out the execution_count and last_execution_time column, this gives you an idea about how frequent is the query\procedure called and when was it lastly executed(Again the catch is if sp is altered then its details may get cleared from cache), then analyze those execution plans to find the indexes used and then document them. You can also export these dmv data to some table by using jobs daily for future references and to be safe from restarts and sp modifications. You may need to repeat this steps for few months, so when your list is ready disable the index and monitor and compare the sps\queries performance(using dmv data collected earlier)for few months if you are good then you can drop the index else you may need to recheck.