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 it’s performance you care about, is there a particular DML statement that is slower than it needs to be due to index maintenance? Those are the indexes that you want to consider. – Andrew Sayer Feb 24 at 20:23

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.

  • 1
    Also don't rely on searching sys.all_sql_modules.definition for references to index names, because that will only catch hints in views/procs/other and obviously miss cases where a really clever person has included an index hint in ad-hoc SQL in the application layer. – David Spillett Feb 25 at 13:11
  • 1
    Sometimes a really clever person uses index id also, and in that case you won't be able to find the reference. – Learning_DBAdmin Feb 25 at 17:05

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

  • 1
    Thanks, helpful tip. – MinnRick Feb 24 at 23:20

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).

  • Yep, principal objective is enhancing performance. Freeing up storage is secondary. For purposes of this thread I'm talking about indexes that don't have any (or extremely few) seeks, scans and lookups. – MinnRick Feb 24 at 18:33

In addition to unique indexes, indexes supporting foreign keys should normally be retained even if they are infrequently used.

  • Good one, thanks. – MinnRick Feb 24 at 21:19

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.

  • Good thought, thanks. – MinnRick Feb 24 at 18:34

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.