Parameter sniffing no SQL Server: o que muda no 2022

A mesma stored procedure fica rápida quando um cliente consulta o próprio pedido e trava quando o financeiro roda o mesmo relatório para todos os pedidos "pendentes" — porque é a mesma query, mas o SQL Server compilou um único plano para os dois casos, e um plano bom para um valor pode ser péssimo para outro. Esse é o problema clássico de parameter sniffing, e até o SQL Server 2019 a única resposta era um workaround manual. No SQL Server 2022, compilado no nível de compatibilidade correto, o motor passa a manter mais de um plano para a mesma instrução.

Parameter sniffing no SQL Server 2019: um plano para todos os valores

Na primeira compilação de uma stored procedure, de um sp_executesql ou de uma query parametrizada, o otimizador "cheira" os valores de parâmetro recebidos naquela execução e gera um plano otimizado para eles. Esse plano vai para o cache e é reaproveitado em todas as execuções seguintes, não importa que valor chegue depois. É um comportamento correto e desejável na maioria dos casos: recompilar a cada chamada custaria CPU à toa.

O problema aparece quando a distribuição dos dados é desigual. Considere uma tabela de pedidos em que 99% das linhas têm status = 'concluido' e 1% têm status = 'pendente':

CREATE PROCEDURE dbo.PedidosPorStatus    @status NVARCHAR(20)ASSELECT *FROM dbo.PedidosWHERE status = @status;

Se a primeira chamada usar @status = 'pendente', o otimizador vê uma seletividade altíssima e escolhe um seek por índice — plano correto para esse valor, e péssimo para 'concluido', que devolve quase a tabela inteira e deveria ser um scan. O inverso também vale: se a primeira chamada sniffa 'concluido', o plano de scan fica cravado no cache e toda consulta por 'pendente' passa a varrer a tabela sem necessidade. O sintoma que chega ao DBA é sempre o mesmo: "a query está lenta hoje, ontem estava rápida", sem que ninguém tenha mudado uma linha de código.

As saídas do 2019 — e o que cada uma custa

Sem um jeito de manter mais de um plano por instrução, o SQL Server 2019 oferece apenas trocas, nunca uma correção real:

• OPTION (RECOMPILE) gera um plano novo a cada execução, sempre ajustado ao valor daquela chamada — ao custo de recompilar toda vez, o que pesa em cargas de alto volume.
• OPTION (OPTIMIZE FOR (@status = 'concluido')) fixa o plano para o valor considerado típico. Funciona enquanto a suposição sobre o valor típico continuar certa; para qualquer outro valor, o plano continua sendo o mesmo problema, só que documentado.
• OPTIMIZE FOR UNKNOWN, variável local ou a trace flag 4136 descartam o valor sniffado e usam a densidade média da coluna — um plano genérico, razoável para a maioria e mediano para todo mundo.
• DBCC FREEPROCCACHE ou KEEPFIXED PLAN são band-aids reativos: alguém percebe a lentidão, limpa o cache manualmente, e o próximo valor sniffado decide o próximo comportamento, para o bem ou para o mal.

Todas essas saídas partem da mesma limitação estrutural: só existe um plano em cache por instrução. A decisão é sempre "um plano bom para uns e ruim para outros" ou "recompilar sempre e pagar CPU por isso". Reescrever a query não muda essa restrição — ela está no motor, não no SQL.

O que muda no SQL Server 2022: Parameter Sensitive Plan optimization

O Parameter Sensitive Plan (PSP) optimization, parte da família Intelligent Query Processing, ataca a causa em vez de contornar o sintoma: em vez de um plano por instrução, o motor passa a manter vários, cada um para uma faixa de valores.

Na compilação inicial, o otimizador examina o histograma de estatísticas em busca de predicados de igualdade com distribuição não uniforme (até três por query, priorizando o mais assimétrico quando há mais de um na mesma tabela). Encontrando esse padrão, em vez de compilar um plano único ele gera um dispatcher plan: uma expressão que classifica o valor de parâmetro recebido em uma faixa de cardinalidade — baixa, média ou alta seletividade, derivada do próprio histograma — e direciona a execução para a variante de plano compilada especificamente para aquela faixa. 'pendente' e 'concluido' deixam de disputar o mesmo plano; cada um passa a ter o seu, compilado e mantido em cache separadamente.

O recurso exige nível de compatibilidade 160 e vem habilitado por padrão nesse nível. Sem alterar uma linha da query, os dois cenários do exemplo anterior passam a ter planos próprios:

SELECT name, compatibility_levelFROM sys.databasesWHERE name = DB_NAME();

Se o resultado mostrar 150 ou inferior, o banco está rodando em binários do SQL Server 2022 mas com o comportamento de otimização travado no nível do 2019 — o PSP simplesmente não entra em ação até o compat level subir para 160.

Como confirmar que o PSP está atuando

sys.query_store_plan guarda um plano por linha; mais de uma linha para o mesmo query_id é indício de múltiplas variantes (também pode vir de recompilação normal, então não é prova isolada):

SELECT    p.query_id,    COUNT(*) AS planos_em_cacheFROM sys.query_store_plan AS pGROUP BY p.query_idHAVING COUNT(*) > 1ORDER BY planos_em_cache DESC;

A confirmação definitiva vem dos Extended Events dedicados ao recurso — parameter_sensitive_plan_optimization, parameter_sensitive_plan_optimization_skipped_reason e query_with_parameter_sensitivity — e da catalog view sys.query_store_query_variant, que liga cada variante ao dispatcher plan que a originou:

CREATE EVENT SESSION [psp_monitoring] ON SERVERADD EVENT sqlserver.parameter_sensitive_plan_optimization,ADD EVENT sqlserver.parameter_sensitive_plan_optimization_skipped_reason,ADD EVENT sqlserver.query_with_parameter_sensitivityADD TARGET package0.event_file(SET filename = N'psp_monitoring.xel')WITH (STARTUP_STATE = ON);

O evento _skipped_reason também é útil ao contrário: mostra por que uma query candidata não recebeu PSP, o que costuma apontar para os limites do recurso descritos a seguir.

Os limites do recurso

PSP não substitui o parameter sniffing, ele opera em cima dele — desligar o sniffing (trace flag 4136, hint DISABLE_PARAMETER_SNIFFING ou PARAMETER_SNIFFING = OFF no escopo do banco) desliga o PSP junto. OPTION (RECOMPILE) no comando continua tendo prioridade e ignora o dispatcher plan. E o recurso hoje só reconhece predicados de igualdade; desigualdades, LIKE e intervalos ficam de fora, o que limita o alcance em relatórios com filtros de data por faixa, por exemplo.

Vale registrar também o Optional Parameter plan optimization (OPPO), feature vizinha que usa o mesmo mecanismo de dispatcher e variantes para resolver o padrão WHERE coluna = @parametro OR @parametro IS NULL, comum em telas de filtro opcional — outro caso que no 2019 dependia de OPTION (RECOMPILE) ou de branches de SQL dinâmico escritos à mão.

Quem quiser desligar o PSP por completo, por database ou por query, tem os dois controles:

ALTER DATABASE SCOPED CONFIGURATION    SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF;

SELECT *FROM dbo.PedidosWHERE status = @statusOPTION (USE HINT('DISABLE_PARAMETER_SENSITIVE_PLAN'));

Por que isso pesa na decisão de atualizar, e não só de ajustar query

O ponto que costuma passar despercebido: subir os binários para o SQL Server 2022 não ativa nada disso sozinho. O PSP depende do nível de compatibilidade do banco, e é rotina comum de migração manter o compat level do ambiente de origem por segurança — o que significa continuar pagando o mesmo custo do 2019 mesmo depois da instância já estar rodando 2022. A atualização de versão sem revisão do compat level entrega o binário novo e mantém o comportamento antigo.

Isso também é o argumento contra adiar a atualização indefinidamente cobrindo os sintomas com OPTION (RECOMPILE) e OPTIMIZE FOR: são ajustes por query, feitos um a um, que precisam ser revisitados a cada mudança na distribuição dos dados. PSP resolve a classe inteira do problema uma vez, no nível do motor, para qualquer query nova que se enquadre no padrão — inclusive as que ainda serão escritas. O mesmo movimento em direção a versões mais recentes já apareceu na migração de bancos SQL Server para Azure: postergar a atualização não é neutro, tem custo recorrente que só cresce com o volume de dados.

Próximo passo

Levantar se o ambiente atual já está em compat level 160, se o PSP está de fato atuando e quais queries seguem reféns de um único plano sniffado é exatamente o tipo de diagnóstico que não depende de reescrever a aplicação — depende de saber onde olhar.

A H1 Data faz um diagnóstico gratuito do ambiente SQL Server que inclui a leitura do compatibility level, dos planos em cache por query e das oportunidades reais de ganho de performance ao atualizar a versão, sem depender de patches manuais por consulta.