Parameter sniffing no SQL Server: diagnostique antes de corrigir

Um procedimento armazenado executa em 80 milissegundos para um cliente e em 40 segundos para outro. O código não mudou, o servidor não está sobrecarregado e, às vezes, uma nova execução parece eliminar o problema. Esse é um sinal clássico de plano sensível a parâmetros, conhecido como parameter sniffing.

Parameter sniffing não é automaticamente um defeito. Quando o SQL Server compila uma consulta parametrizada, ele usa os valores atuais para estimar linhas e escolher um plano. Reutilizar esse plano economiza compilações e normalmente é benéfico. O problema aparece quando um único plano precisa atender distribuições de dados muito diferentes.

Por que um plano pode falhar

Imagine que a maioria dos clientes tenha menos de 100 pedidos, enquanto um grande cliente tenha 8 milhões. Um plano compilado para um cliente pequeno pode escolher index seeks e nested loops. O mesmo plano pode ficar extremamente lento para o cliente grande. Já um plano compilado para o cliente grande pode usar scans e hashes, desperdiçando recursos para todos os outros.

Dados assimétricos, filtros opcionais, estatísticas desatualizadas e colunas correlacionadas aumentam a probabilidade.

Reproduza o comportamento com segurança

Comece com um procedimento representativo:

CREATE OR ALTER PROCEDURE dbo.GetOrdersByCustomer
    @CustomerId int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT OrderId, OrderDate, TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId
    ORDER BY OrderDate DESC;
END;

Em um ambiente que não seja de produção, teste um valor seletivo e outro pouco seletivo. Capture o plano de execução real e as estatísticas de execução de ambos. Se o desempenho depender do valor usado para compilar o plano em cache, há forte evidência de sensibilidade a parâmetros.

Não limpe todo o cache de planos em produção. Isso cria um pico de compilações e afeta cargas não relacionadas.

Fluxo prático de diagnóstico

1. Use o Query Store para encontrar grandes variações de duração, CPU ou leituras lógicas.
2. Compare linhas reais e estimadas no primeiro join ou lookup relevante.
3. Registre os valores usados na compilação e na execução.
4. Verifique o histograma e a última atualização das estatísticas relevantes.
5. Elimine conversões implícitas, predicados não sargable e índices ausentes antes de culpar o parameter sniffing.
6. Confirme que as execuções rápida e lenta usam a mesma consulta e identidade de plano.

A pergunta principal não é "O plano é ruim?", mas "Ele é bom para um formato importante dos dados e ruim para outro?"

Escolha a menor correção confiável

Comece pelos fundamentos. Atualize estatísticas imprecisas e crie um índice somente quando o padrão de acesso justificar. Se a consulta realmente possui formatos diferentes, separar esses formatos costuma ser mais claro do que forçar um plano de compromisso.

No SQL Server 2022 ou posterior, o Parameter Sensitive Plan Optimization pode manter várias variantes de plano para predicados de igualdade elegíveis. Verifique o nível de compatibilidade e o formato da consulta, depois confirme as variantes no Query Store.

Uma dica do Query Store ou um plano forçado pode estabilizar uma emergência, mas deve ser monitorado porque os dados mudam.

Use OPTION (RECOMPILE) quando o custo de compilação for baixo e cada execução precisar de um plano específico. Aplique à menor instrução possível. Use OPTIMIZE FOR apenas com um valor estável e representativo ou quando desejar deliberadamente um plano médio. SQL dinâmico com sp_executesql funciona bem para filtros opcionais, pois cria planos para formatos distintos sem perder a parametrização.

Evite erros comuns

• Não desative parameter sniffing globalmente por causa de uma consulta.
• Não adicione todo índice sugerido sem medir custos de escrita e armazenamento.
• Não force um plano sem responsável, monitoramento e condição de remoção.
• Não presuma que toda lentidão intermitente seja parameter sniffing.

Checklist de produção

Capture uma linha de base, preserve o plano original, teste grupos representativos de parâmetros, meça CPU e leituras lógicas, implante a alteração mais restrita e acompanhe o Query Store. Defina a reversão antes da implantação.

Parameter sniffing deve ser tratado como um problema de distribuição de dados, não como uma falha misteriosa de cache. Comprove os formatos de dados concorrentes e escolha uma solução correspondente. Para uma segunda opinião, use o formulário de perguntas abaixo e envie o procedimento, parâmetros representativos e um plano de execução anonimizado.