Indexes Execution Plans: Guia de Otimização de Queries

Otimizar queries com indexes e execution plans não é um bicho de sete cabeças. Na prática, você lê o que o banco pretende fazer, encontra o gargalo e cria um índice que transforma uma varredura completa em uma busca pontual. O resultado: menos leitura de disco, menos CPU e respostas mais rápidas.

Neste guia, vamos percorrer o processo passo a passo, do básico ao avançado, usando SQL Server como referência. Você vai aprender a ler um execution plan, identificar operadores caros e criar indexes eficientes. Ao final, terá um checklist para aplicar em qualquer query lenta.

Pré-requisitos

Antes de começar, você precisa de:

• Acesso a um banco SQL Server com permissão para criar índices e visualizar planos de execução.
• Uma query que esteja lenta ou que você suspeite que possa ser melhorada.
• Conhecimento básico de SQL, como SELECT, JOIN e WHERE.
• O SQL Server Management Studio (SSMS) instalado, ou outra ferramenta que exiba planos gráficos.

Passo 1: Capture o execution plan da query

O primeiro passo é ver o que o otimizador decidiu fazer. No SSMS, abra uma nova janela de consulta, escreva sua query e pressione Ctrl+M para ativar a inclusão do plano de execução real. Depois, execute a query normalmente.

O plano aparece como uma aba chamada "Execution plan" na parte inferior. Se preferir, use SET STATISTICS PROFILE ON ou SET SHOWPLAN_ALL ON para obter o plano em formato textual, útil para documentação ou para ferramentas de linha de comando.

Dica: Capture o plano com dados representativos. Se você testar com uma tabela vazia, o otimizador pode escolher um scan simples que não reflete a realidade com milhões de linhas.

Erro comum: Ignorar o plano estimado em vez do real. O plano estimado é baseado em estatísticas, que podem estar desatualizadas. O real mostra o que de fato foi executado, incluindo o número de linhas lidas.

Passo 2: Identifique os operadores mais caros

No plano gráfico, cada operador tem um custo relativo, expresso em percentual. O operador com o maior percentual é o seu primeiro alvo. Os suspeitos habituais são:

• Table Scan ou Clustered Index Scan: leitura de todas as linhas da tabela.
• Index Scan: leitura de todas as linhas de um índice, quase tão caro quanto um table scan.
• Key Lookup ou RID Lookup: busca de linhas adicionais em uma tabela heap ou índice clustered, após um seek.
• Hash Match ou Nested Loops: operadores de junção que podem ser ineficientes com grandes volumes de dados.

Passe o mouse sobre o operador para ver o custo exato, o número de linhas estimadas e o número de execuções. Se o número de execuções for alto, o problema pode estar em um loop que repete a mesma operação várias vezes.

Dica: Use o plano com o mouse para abrir as propriedades do operador. Lá você encontra o Estimated Number of Rows e o Actual Number of Rows. Uma diferença grande indica estatísticas desatualizadas.

Erro comum: Focar apenas no custo relativo. Um operador com 40% de custo pode ser menos relevante que um com 10%, se ele executa 10.000 vezes. Avalie também o número de execuções.

Passo 3: Crie ou ajuste indexes para transformar scans em seeks

Quando você identifica um Index Scan ou Table Scan, o próximo passo é criar um índice que permita um Index Seek. Um seek lê apenas as linhas que atendem ao filtro da cláusula WHERE, reduzindo drasticamente o número de páginas lidas.

Por exemplo, se sua query filtra por WHERE status = 'ativo' e a tabela não tem índice nessa coluna, o banco faz um scan. Criar um índice na coluna status permite um seek.

A sintaxe básica:

CREATE INDEX IX_Nome_Indice ON Tabela (coluna);

Mas não pare aí. Se a query também faz SELECT nome, email, inclua essas colunas como colunas incluídas (INCLUDE) no índice, para evitar um lookup:

CREATE INDEX IX_Status_Include ON Tabela (status) INCLUDE (nome, email);

Dica: Use o Database Tuning Advisor (DTA) do SQL Server para sugerir índices, mas valide manualmente. O DTA tende a sugerir índices em excesso.

Erro comum: Criar índices em todas as colunas que aparecem no WHERE, sem pensar na ordem das colunas em índices compostos. A ordem importa: o índice é eficiente quando a primeira coluna é usada em uma igualdade ou quando cobre a ordem do filtro.

Passo 4: Revise o plano após a criação do índice

Crie o índice e execute a query novamente, com o plano de execução ativo. Compare o novo plano com o anterior. O scan deve ter virado um seek, e o custo relativo deve ter caído.

Também observe o tempo de execução. Use SET STATISTICS TIME ON para ver o tempo de CPU e o tempo decorrido. Uma melhoria real se traduz em números menores, não apenas em um plano mais bonito.

Dica: Guarde o plano antigo e o novo em arquivos .sqlplan para comparar lado a lado. No SSMS, você pode abrir os dois e usar a opção de comparação.

Erro comum: Achar que o índice novo é a solução final. Teste com diferentes volumes de dados e com a query em diferentes horários, se possível. O que é rápido com 10 mil linhas pode ser lento com 10 milhões.

Passo 5: Monitore o impacto e evite excesso de índices

Cada índice ocupa espaço e aumenta o custo de INSERT, UPDATE e DELETE. Um índice que acelera uma leitura pode desacelerar a escrita. Por isso, o monitoramento contínuo é parte do processo.

Use as DMVs (Dynamic Management Views) para ver quais índices são usados e quais são ignorados:

SELECT * FROM sys.dm_db_index_usage_stats;

Índices com user_seeks e user_scans zerados podem ser candidatos à remoção.

Dica: Crie um processo de revisão mensal dos índices. O que faz sentido hoje pode não fazer quando o volume de dados mudar.

Erro comum: Manter índices redundantes, como dois índices com a mesma primeira coluna. Um deles provavelmente é desnecessário.

Checklist final

Ao terminar este guia, você deve ter:

• Capturado o execution plan real da query.
• Identificado o operador com maior custo ou maior número de execuções.
• Criado um índice (ou ajustado um existente) para transformar scan em seek.
• Comparado o plano antes e depois, confirmando a redução de custo.
• Verificado o tempo de execução com estatísticas de tempo.
• Revisado a necessidade do índice, considerando o impacto em operações de escrita.

Perguntas Frequentes

O que é um execution plan?

É o roteiro que o Query Optimizer do SQL Server cria para executar uma query. Ele mostra as operações, a ordem de execução e os custos relativos. Analisá-lo ajuda a identificar gargalos e oportunidades de otimização.

Qual a diferença entre Index Seek e Index Scan?

Um Index Seek lê apenas as linhas que atendem à condição do WHERE, usando a estrutura B-tree do índice. Um Index Scan lê todas as linhas do índice, como uma varredura completa. Seek é quase sempre mais rápido para consultas seletivas.

Como saber se um índice está sendo usado?

Consulte a DMV sys.dm_db_index_usage_stats. Ela mostra contadores de seeks, scans e lookups por índice. Se um índice não aparece ou tem contadores zerados, provavelmente não está sendo utilizado.

Criar muitos índices é ruim?

Sim. Cada índice adicional consome espaço em disco e aumenta o custo de operações de escrita (INSERT, UPDATE, DELETE). O segredo é equilibrar o ganho em leitura com o custo em escrita, criando apenas índices que trazem benefício real.

O que é um covering index?

É um índice que inclui todas as colunas necessárias para responder a uma query, sem precisar acessar a tabela base. Isso elimina lookups e reduz o número de leituras, mas aumenta o tamanho do índice.

Como ler o custo de um operador no plano?

No plano gráfico, cada operador exibe um percentual de custo relativo ao total da query. Use esse número como prioridade, mas combine com o número de execuções e o número de linhas para entender o impacto real. Um operador com 5% de custo, mas executado 10.000 vezes, pode ser mais relevante que um com 30% executado uma vez.