Ler um plano real do SQL Server com evidências

Um diagrama parece permitir um diagnóstico simples: encontrar a maior porcentagem e remover aquele operador. Porém, as porcentagens representam custos estimados pelo otimizador, não o tempo medido na execução observada. Uma investigação confiável relaciona a requisição exata, as linhas que percorrem o plano e os recursos efetivamente consumidos.

Registrar a execução correta

Guarde o texto SQL, os valores e tipos dos parâmetros, o nível de compatibilidade e as opções relevantes da sessão. Colar a consulta em outra janela com parâmetros diferentes muda o experimento. Ative o plano real no SSMS ou utilize STATISTICS XML. Essa coleta executa a instrução; para um lote que grava dados, ela não equivale à consulta de um plano estimado sem execução.

O exemplo compara a mesma agregação antes e depois da criação de um índice. Ative o plano real antes de executá-lo em uma sessão de teste.

CREATE TABLE #PlanOrders
(
    OrderId int NOT NULL PRIMARY KEY,
    CustomerId int NOT NULL,
    Amount decimal(12,2) NOT NULL
);
;WITH N AS
(
    SELECT TOP (10000)
        CONVERT(int, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) AS n
    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b
)
INSERT #PlanOrders (OrderId, CustomerId, Amount)
SELECT n, n % 100, CONVERT(decimal(12,2), n % 250)
FROM N;

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT SUM(Amount) AS Total
FROM #PlanOrders WHERE CustomerId = 42;

CREATE INDEX IX_PlanOrders_Customer
ON #PlanOrders (CustomerId) INCLUDE (Amount);

SELECT SUM(Amount) AS Total
FROM #PlanOrders WHERE CustomerId = 42;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
DROP TABLE #PlanOrders;

Os dois SELECT devem retornar o mesmo total. Compare as leituras lógicas na saída de mensagens e os operadores de acesso. A segunda consulta dispõe de um índice que cobre CustomerId e Amount, mas registre a escolha efetiva do otimizador em vez de prometer a presença de um ícone específico.

A tabela é pequena para tornar a demonstração controlável. Seletividade e largura das linhas de produção podem alterar o resultado. Separe também preparação e medição: a criação do índice aparece no lote e consome recursos, mas seu plano não descreve o SELECT. Salve os planos com as medidas correspondentes. Não limpe os caches compartilhados do servidor para fabricar um teste aparentemente limpo.

Encontrar a primeira divergência relevante

Siga o fluxo dos acessos aos dados em direção ao resultado. Compare quantidades estimadas e reais nos pontos importantes. Se um filtro estimou 100 linhas e entregou 200.000, um join caro adiante pode ser consequência desse erro inicial. Ajustar somente a ordenação final pode manter a causa do problema.

Diferencie linhas lidas de linhas retornadas. Um seek pode localizar um intervalo grande e descartar quase tudo com um predicado residual. O nome do operador não comprova pouco trabalho. Abra as propriedades, examine os predicados de busca e os residuais e compare o volume examinado com o encaminhado. Percorrer uma tabela pequena pode ser mais barato que realizar muitas buscas aleatórias.

Em Nested Loops, observe o número de execuções da operação interna. Um acesso barato repetido 100.000 vezes pode dominar o trabalho. Verifique se a estimativa apresentada vale para cada execução ou para o conjunto, especialmente em planos paralelos. Compare medidas equivalentes, considerando também como os dados são apresentados por thread.

Lookup, sort, spool e hash join não são defeitos automáticos. Pergunte qual função cumprem e quanto trabalho realizam. Um índice que elimina lookups pode aumentar armazenamento e custo de escrita. Um spool pode evitar processamento repetido, e uma ordenação pode atender uma exigência explícita do produto.

Testar uma hipótese por vez

Escolha uma hipótese verificável: um predicado residual examina dados demais, um filtro com distribuição desigual erra a cardinalidade ou colunas desnecessárias aumentam a largura de uma ordenação. Faça uma alteração relacionada a essa causa. Repita parâmetros representativos e compare correção do resultado, leituras, CPU, duração e comportamento sob concorrência.

Avisos de execução precisam de investigação, mas não encerram o diagnóstico. Um spill pode prejudicar muito um sistema ocupado e pouco uma consulta pequena eventual. Uma sugestão de índice ausente descreve uma possibilidade para determinado contexto. Compare-a com os índices existentes e com a carga de escrita antes de implementá-la.

Por fim, relacione o trabalho no servidor com a demora percebida pelo usuário. Pouca CPU e poucas leituras não descartam bloqueios, espera por memória ou um cliente que consome os resultados devagar. O plano real complementa os dados de espera e a medição de ponta a ponta. Preserve a referência inicial e a justificativa da mudança para que uma regressão futura possa ser analisada com o mesmo contexto.

Referências técnicas: Microsoft Learn: Actual execution plans · Microsoft Learn: Showplan operators · Microsoft Learn: STATISTICS IO.