Find unused / redundant indexes, make them invisible, drop safely

Command

-- Candidate 1: indexes unused since the last restart
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'shop';
-- Candidate 2: redundant indexes that are a left prefix of another
SELECT table_name, redundant_index_name, redundant_index_columns, dominant_index_name, sql_drop_index
FROM sys.schema_redundant_indexes WHERE table_schema = 'shop';

-- Make it invisible before dropping; revert instantly if anything regresses
ALTER TABLE shop.big ALTER INDEX ix_region INVISIBLE;
SELECT INDEX_NAME, IS_VISIBLE FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'shop' AND TABLE_NAME = 'big' AND SEQ_IN_INDEX = 1;
-- Try the invisible index for a single query
EXPLAIN SELECT /*+ SET_VAR(optimizer_switch = 'use_invisible_indexes=on') */ COUNT(*) FROM shop.big WHERE region = 'TR';
ALTER TABLE shop.big ALTER INDEX ix_region VISIBLE;
-- After a few business cycles (incl. month-end) without issues
ALTER TABLE shop.big DROP INDEX ix_region, ALGORITHM=INPLACE, LOCK=NONE;

What it does

Lists unused indexes and indexes covered by another one; making an index invisible before dropping it lets you test production behavior without it and revert instantly (no rebuild) if needed.

When to use it

When cleaning up indexes to reduce write cost, buffer pool pressure and disk usage.

Caution

schema_unused_indexes relies on statistics collected since the last restart only; infrequent queries such as monthly reports may not show up. If replicas serve different workloads, check each server separately. An invisible index is still maintained on DML (write cost remains) but the optimizer ignores it; a unique index still enforces uniqueness when invisible. A PRIMARY KEY cannot be made invisible. Queries that name the index in FORCE INDEX / USE INDEX fail once it is invisible.