Parameter sniffing
Concept
Comportamento de mecanismos de banco de dados no estilo SQL Server que compilam um plano para os valores de parâmetro observados em tempo de compilação e reutilizam o plano em cache, o que pode gerar desempenho ruim para valores de parâmetro muito diferentes; também chamado de sensibilidade a parâmetros.
Descrição
Parameter sniffing é um comportamento de mecanismos de banco de dados no estilo SQL Server em que um plano de consulta é compilado para os valores de parâmetro observados em tempo de compilação, e o plano em cache é então reutilizado. Também é chamado de sensibilidade a parâmetros. O comportamento não é um defeito em si: o mecanismo examina os valores de parâmetro atuais ao compilar ou recompilar uma consulta e os repassa ao otimizador de consultas, que pode então produzir um plano de execução mais eficiente para esses valores. A dificuldade aparece quando o plano em cache é usado posteriormente para valores de parâmetro que parecem muito diferentes dos originais, pois um plano adequado a um valor pode ter desempenho ruim para outro.
O guia de arquitetura de processamento de consultas do SQL Server, da Microsoft, descreve o parameter sniffing em uma seção sobre sensibilidade a parâmetros. De acordo com esse guia, o processo se aplica a procedimentos armazenados, consultas executadas por meio de sp_executesql e consultas preparadas. Ele interage diretamente com a reutilização de planos, porque os planos compilados são armazenados no cache de planos e podem ser reutilizados por execuções posteriores. O primeiro conjunto de valores que dispara uma compilação, portanto, define o plano que as chamadas seguintes recebem, razão pela qual os resultados podem variar conforme a chamada que, por acaso, compilou o plano.
O guia indica um remédio: a dica de consulta OPTION (RECOMPILE). Com essa dica, o otimizador trata parâmetros e variáveis locais como constantes em tempo de compilação, que podem ser substituídas por literais. Os valores usados são aqueles vigentes imediatamente antes da execução da instrução, que não são necessariamente os valores originalmente passados. Isso pode ajudar quando os dados de entrada são altamente variáveis ou enviesados. O guia também alerta que a dica recompila a consulta a cada execução, o que pode ser custoso, e recomenda medir o efeito sobre toda a carga de trabalho antes de adotá-la. O guia também aponta um artigo separado sobre a otimização Parameter Sensitive Plan, para leitura complementar.
Um detalhe relacionado da mesma documentação diz respeito às variáveis locais. Seus valores não são observados em tempo de compilação. Em vez disso, o otimizador faz estimativas com base em informações de densidade ou, quando não há estatísticas disponíveis, usa percentuais fixos estimados para comparações de igualdade e de intervalo. Essa distinção é importante ao diagnosticar uma consulta lenta, pois um parâmetro e uma variável local podem levar a estimativas diferentes e, portanto, a planos diferentes.
Na prática, o parameter sniffing aparece como consultas que executam rapidamente para algumas entradas e lentamente para outras, ou como desempenho que muda depois que um plano é removido do cache e recompilado. O episódio do M365.FM "Fix Bad Data Upstream with T-SQL Contracts" lista o parameter sniffing entre falhas comuns em ambientes corporativos, descrevendo-o como algo que pode se disfarçar de aleatoriedade. O episódio também trata a forma dos planos como uma questão de governança, afirmando que, se a forma dos planos muda, os custos e a confiabilidade mudam junto, de modo que o comportamento dos planos se traduz em estabilidade de custos e risco operacional.
O episódio "Design Reliable and Resilient Azure Architectures" situa o parameter sniffing em um fluxo de solução de problemas para o Azure SQL Database. Ele sugere habilitar o Intelligent Insights e o Query Performance Insight, capturar consultas de longa duração, revisar os planos de execução e identificar índices ausentes ou parameter sniffing. Menciona ainda as recomendações de ajuste automático do Azure SQL e as sugestões de índice pelo Azure Advisor, além de otimização de consultas, indexação adequada e a consideração de escalar DTUs ou vCores ou migrar para o Hyperscale.
Considerados em conjunto, os materiais tratam o parameter sniffing como um compromisso do cache de planos: reutilizar um plano compilado economiza trabalho de compilação, mas um plano ajustado a um valor de parâmetro pode não servir a outros. As decisões típicas nesse contexto envolvem examinar os planos de execução, verificar se a distribuição dos dados é enviesada e avaliar o custo de compilação de opções como OPTION (RECOMPILE) frente ao benefício de planos mais adequados.
Overview compiled with AI from M365.FM episodes and the facts on this page. You can improve it with the edit button below.
| Tipo | Concept |
|---|
Perguntas frequentes
Questions marked with AI were written with AI from the facts on this page.
O que é parameter sniffing? (AI)
É o comportamento de mecanismos no estilo SQL Server que compilam um plano de consulta para os valores de parâmetro observados em tempo de compilação e reutilizam o plano em cache. Isso pode gerar desempenho ruim quando valores de parâmetro posteriores diferem muito. Também é chamado de sensibilidade a parâmetros.
Como o OPTION (RECOMPILE) pode afetar problemas de parameter sniffing? (AI)
De acordo com o guia de processamento de consultas da Microsoft, a dica faz o otimizador tratar parâmetros e variáveis locais como constantes em tempo de compilação, que podem ser substituídas por literais, o que pode ajudar com dados altamente variáveis ou enviesados. A consulta é recompilada a cada execução, o que pode ser custoso, por isso o efeito sobre toda a carga de trabalho deve ser medido antes.
Variáveis locais são observadas da mesma forma que os parâmetros? (AI)
Não. A documentação afirma que os valores de variáveis locais não são observados em tempo de compilação. Em vez disso, o otimizador faz estimativas com base em informações de densidade ou, sem estatísticas, usa percentuais fixos estimados para comparações de igualdade e de intervalo.
Community wiki
Short info above, community knowledge below: add practical tips, examples or corrections. New contributions are reviewed first; trusted members publish directly.
Especialistas e empresas para este tópico
Ninguém indicou ainda este tópico. Adicione-o ao seu perfil.
Maintained by Mirko Peters (Microsoft MVP) · Last updated: