When should an index be dropped?

An index may be a candidate for removal when it is redundant, unused over a representative workload period, disproportionately expensive to maintain, or no longer supports current application queries.

Dropping indexes should be evidence-based. Review usage statistics, Query Store, execution plans, index definitions, maintenance cost, and business reporting cycles.

Indexes that enforce PRIMARY KEY or UNIQUE constraints have correctness implications and must not be treated like ordinary optional performance indexes.