Índices de cobertura no SQL Server: chave, INCLUDE e lookups

Um plano pode apresentar Index Seek e continuar lento. A busca pode identificar milhares de linhas e executar um lookup no índice clusterizado para recuperar cada coluna ausente. A questão não é simplesmente existir um seek, mas quanto trabalho adicional a consulta inteira exige.

Um índice cobre uma consulta específica quando contém as colunas necessárias para atendê-la. Cobertura é uma relação entre consulta e índice. Acrescentar uma coluna ao SELECT ou mudar um predicado pode eliminar essa cobertura. Comece pela instrução verdadeira e pelos parâmetros comuns e extremos da aplicação.

Organizar a chave para navegar

O exemplo restringe o cliente por igualdade, aplica um intervalo de datas e devolve os pedidos mais recentes. CustomerId inicia a chave, seguido de OrderedAt e SaleId para desempatar datas. Total e Status são retornados, mas não definem a ordenação solicitada; por isso ficam em INCLUDE.

CREATE TABLE #Sales
(
    SaleId bigint NOT NULL PRIMARY KEY,
    CustomerId int NOT NULL,
    OrderedAt datetime2(0) NOT NULL,
    Total decimal(12,2) NOT NULL,
    Status tinyint NOT NULL
);
INSERT #Sales VALUES
(1,7,'2021-10-01',90,1),(2,7,'2021-10-02',40,0),
(3,8,'2021-10-02',20,1),(4,7,'2021-10-03',70,1);

CREATE INDEX IX_Sales_Customer_Date
ON #Sales(CustomerId, OrderedAt DESC, SaleId DESC)
INCLUDE(Total, Status);

DECLARE @CustomerId int=7;
DECLARE @From datetime2(0)='2021-10-01';
SELECT TOP (2) SaleId, OrderedAt, Total, Status
FROM #Sales
WHERE CustomerId=@CustomerId AND OrderedAt>=@From
ORDER BY OrderedAt DESC, SaleId DESC;

SET STATISTICS IO, TIME ON;
SELECT SaleId, OrderedAt, Total, Status
FROM #Sales
WHERE CustomerId=@CustomerId AND OrderedAt>=@From;
SET STATISTICS IO, TIME OFF;
DROP TABLE #Sales;

Para o cliente 7, a primeira consulta retorna 4 e 2. O índice atende ao filtro do cliente e à ordem. Se a data viesse primeiro, o SQL Server poderia navegar pelo intervalo e ainda precisar ler muitos outros clientes. Assim, colocar sempre a coluna globalmente mais seletiva na frente é uma regra incompleta.

Uma condição de intervalo geralmente limita quanto as colunas seguintes conseguem estreitar a busca. Elas ainda podem contribuir com ordenação e cobertura. Inspecione os predicados efetivos do seek e os filtros residuais. Compare linhas lidas e retornadas, em vez de deduzir eficiência somente pelos nomes das colunas.

Usar INCLUDE para um custo demonstrado

Colunas incluídas ficam no nível folha do índice não clusterizado e não fazem parte da sua ordem de navegação. Podem eliminar lookups, mas aumentam páginas, consumo de cache, armazenamento e volume dos backups. Também precisam ser mantidas quando mudam. Incluir um estado atualizado frequentemente pode elevar significativamente as escritas.

Não copie automaticamente todas as colunas de uma recomendação de índice ausente. Essas sugestões representam apenas parte do custo estimado e não consolidam as necessidades de toda a carga. Compare com os índices existentes. Uma pequena ampliação pode servir a várias consultas; diferenças de ordem das chaves, porém, podem continuar justificadas.

Lookup não é necessariamente um defeito. Dez acessos extras para dez linhas podem custar menos do que manter um índice largo e raramente utilizado. O ponto de transição depende da quantidade, largura, cache e alternativas de plano. Se um cliente possui dez pedidos e outro um milhão, teste os dois casos.

Medir o padrão inteiro

A tabela temporária verifica o resultado desejado, mas não demonstra melhoria de desempenho. Para isso, utilize dados representativos e capture STATISTICS IO, duração, CPU e planos reais. Experimente filtros seletivos e amplos de cliente e data. Não limpe o cache de produção para fabricar um teste frio; compare condições equivalentes.

Inclua inserções, ajustes de valores, atualizações de estado e remoção de dados antigos. Se o índice eliminar uma ordenação, compare concessões de memória e spills em tempdb. Se uma consulta grande continuar escolhendo scan, talvez esse seja o caminho correto. Forçar um seek seguido por centenas de milhares de lookups pode piorar.

A decisão deve dizer quais instruções melhoram, qual índice anterior pode ficar redundante e quanto custam as escritas e o espaço adicional. Preserve a definição antiga e observe um ciclo normal de negócio. Um índice de cobertura útil reduz o custo total da carga, em vez de simplesmente produzir um desenho de plano mais atraente.

Referências técnicas: Microsoft Learn: Included columns · Microsoft Learn: Index design.