I've been doing some index usage reviews (using DMV seek, scan and lookup stats) and have identified a number of 'unused' indexes in our biggest and busiest DB. I am interested in the opinions of this forum on what checks or confirmations other than reads should be evaluated before performing the index DROPs. One that comes immediately to mind is whether the index is being used to enforce a necessary unique constraint. What other factors are important to consider?
If there was a clever person who decided to use index hints in their application's queries, dropping said index will cause the query to fail outright if/when it runs.
Something like a quarter or year-end report might not be showing any index usage due to its infrequency of execution depending on how often the system is restarted.
If you are unsure if you have a periodic report or job running that might use an index you would be well advised to disable the index rather than dropping it as then you have the definition in situ should you discover that it was after all required.
ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee DISABLE;
ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee REBUILD;
should you discover the index was after all needed
Updates is the one you IMO you should weigh the positive aspects of the index (seek and scan) against. With few updates, then the the overhead is marginal. Unless you consider diskspace, but I assume you are after "what makes things go slower" as opposed to "what uses storage".
Note that if an index hasn't been touched since startup, you won't see it in sys.dm_db_index_usage_stats. OTOH, then it doesn't carry any operational overhead, so you may not bother anyhow (as per above reasoning).
If the index is not enforcing uniqueness, it is only there to (potentially) assist in read access. I'd just caution against dropping indexes that might be used for quarter end/year end reporting jobs that have been created to avoid locking tables for extended periods of time. You'll have to use some judgment and knowledge of the tables to determine that or just wait until you've collected enough information to know for sure.
May not necessarily apply to your case but I think important to know for whomever it could apply to, and relevant.
If any of those tables are larger in size (100s of millions of rows / 100s of GB in size) and the index you're considering to
DROP is the clustered index, bare in mind this operation will likely take substantially longer than dropping a nonclustered index on the same table. This is because the clustered index is the logical sorting of the table itself, and it's also contained as a reference in the rest of your nonclustered indexes as well.
I've seen dropping of a clustered index on a larger table take well over an hour to complete, thus blocking a majority of the other queries trying to run against that table during the process. So such a situation should definitely be planned well and likely executed off-hours during a maintenance window.