Encontrar índices não usados / redundantes, torná-los invisíveis e removê-los com segurança

Comando

-- 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;

O que faz

Lista índices não usados e índices cobertos por outro; tornar um índice invisível antes de removê-lo permite testar o comportamento da produção sem ele e reverter na hora (sem rebuild) se necessário.

Quando usar

Ao limpar índices para reduzir o custo de escrita, a pressão no buffer pool e o uso de disco.

Atenção

schema_unused_indexes se baseia apenas em estatísticas coletadas desde o último restart; consultas pouco frequentes, como relatórios mensais, podem não aparecer. Se as réplicas atendem cargas diferentes, verifique cada servidor separadamente. Um índice invisível continua sendo mantido no DML (o custo de escrita permanece), mas o otimizador o ignora; um índice único invisível continua garantindo a unicidade. Uma PRIMARY KEY não pode ser tornada invisível. Consultas que citam o índice em FORCE INDEX / USE INDEX falham quando ele fica invisível.