Índices filtrados no SQL Server para filas e dados ativos

Uma tabela de trabalho pode guardar milhões de tarefas concluídas enquanto os processos procuram algumas centenas de pendências. Um índice completo ajuda nessa busca, mas continua armazenando todo o histórico. Um índice filtrado representa diretamente o subconjunto necessário à consulta ativa.

O tamanho relevante é o conjunto ativo. Cinquenta milhões de linhas com 200 pendentes não se comportam como vinte milhões de pendências depois de uma interrupção. Planeje operação normal e recuperação. O comportamento de uma fila quase vazia não basta para prever os planos quando o acúmulo passa a ocupar boa parte da tabela.

Representar o predicado do negócio

No exemplo, zero significa pendente e um concluído. O índice contém somente pendências, ordenadas por criação e identificador. CustomerId está incluído porque a resposta precisa dele, sem utilizá-lo para pesquisa ou ordenação. O resultado esperado são as tarefas 2 e 3. A amostra demonstra correção, não um ganho confiável de desempenho.

CREATE TABLE #Work
(
    WorkId bigint NOT NULL PRIMARY KEY,
    Status tinyint NOT NULL,
    CreatedAt datetime2(0) NOT NULL,
    CustomerId int NOT NULL
);
INSERT #Work VALUES
(1,1,'2024-01-01',10),(2,0,'2024-01-02',20),
(3,0,'2024-01-03',10),(4,1,'2024-01-04',30);

CREATE INDEX IX_Work_Pending
ON #Work(CreatedAt, WorkId)
INCLUDE(CustomerId)
WHERE Status = 0;

SELECT TOP (20) WorkId, CreatedAt, CustomerId
FROM #Work
WHERE Status = 0
ORDER BY CreatedAt, WorkId;

SELECT i.name, p.row_count, p.used_page_count
FROM tempdb.sys.indexes AS i
JOIN tempdb.sys.dm_db_partition_stats AS p
  ON p.object_id=i.object_id AND p.index_id=i.index_id
WHERE i.object_id=OBJECT_ID('tempdb..#Work');

DROP TABLE #Work;

Com dados reais, compare páginas do índice, leituras lógicas e linhas lidas em profundidades normais e máximas. Um índice somente no estado ainda pode exigir ordenação ou muitos acessos à tabela. Coloque as colunas de ordenação na chave e limite as incluídas. Um documento JSON grande geralmente pode ser buscado depois da seleção de poucas IDs.

As opções SET também precisam ser compatíveis. Verifique ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_WARNINGS, ANSI_PADDING, CONCAT_NULL_YIELDS_NULL e ARITHABORT habilitadas, com NUMERIC_ROUNDABORT desabilitada. Se a escrita falhar pela aplicação, mas funcionar na sessão administrativa, compare as opções da conexão usada pelo sistema.

Entender por que o otimizador ignora o índice

O otimizador precisa provar que o índice contém todas as linhas que poderiam satisfazer a consulta. Um plano reutilizável com Status = @Status geralmente não pode depender de um índice limitado a Status = 0. Esse plano poderia ser executado depois para tarefas concluídas. Um teste isolado com zero não resolve a validade para os demais valores.

Uma consulta específica para pendências com predicado literal costuma ser a alternativa mais clara. Recompilar somente a instrução também pode funcionar quando seu custo é aceitável. Não comece forçando o índice: uma sugestão incompatível com o filtro pode impedir a geração do plano. Teste a parametrização verdadeira, incluindo parametrização forçada no banco, se existir.

As estatísticas filtradas descrevem o subconjunto e podem melhorar as estimativas. Uma população pequena que muda rapidamente continua exigindo observação. Compare estimativas e linhas reais, além da frequência de transições. Estatísticas globais da tabela não representam automaticamente as pendências. Para um filtro IS NULL, confira também se incluir a coluna filtrada é necessário para obter o acesso desejado.

Separar busca rápida e reserva correta

Mover uma tarefa para concluída remove sua entrada do índice. Reabri-la insere novamente a entrada. O histórico pesa menos nesse índice, mas mudanças de estado ainda geram escritas. Meça a latência e a contenção quando muitos trabalhadores alteram simultaneamente a parte mais antiga da fila.

O SELECT apresentado não reserva tarefas atomicamente. Dois trabalhadores podem ler as mesmas IDs antes que algum altere o estado. Uma fila real exige reserva transacional, proprietário ou lease, tentativas controladas e recuperação de trabalhadores interrompidos. READPAST e bloqueios de atualização dependem do nível de isolamento e não devem ser adicionados sem análise.

Examine o acúmulo durante falhas, tarefas permanentemente defeituosas e a velocidade com que o trabalho deixa o conjunto ativo. Um índice pequeno não resolve processos que nunca concluem tarefas. Também não substitui a política de retenção do histórico concluído.

O objetivo operacional é esvaziar o atraso sem monopolizar o banco. Um bom índice filtrado torna barato encontrar a parte correta dos dados, mantendo explícitas as regras de reserva, recuperação e conservação. Essa separação ajuda a identificar se a limitação está na consulta ou no próprio processamento.

Referências técnicas: Microsoft Learn: Filtered indexes · Microsoft Learn: Index design.