Parâmetro Sniffing (ou Spoofing) no SQL Server

Um tempo atrás eu tive uma consulta que eu corri bastante para um dos meus usuários. Ele ainda estava sendo desenvolvido e ajustado, mas finalmente estabilizou e rodou rapidamente, então criamos um procedimento armazenado a partir dele.

Até agora, tão normal.

O procedimento armazenado, no entanto, era lento. Nenhuma diferença material entre a consulta e o proc, mas a mudança de velocidade era enorme.

[Em segundo plano, estamos executando o SQL Server 2005.]

Um DBA local amigável (que não trabalha mais aqui) deu uma olhada no procedimento armazenado e disse “spoofing de parâmetro!” ( Edit: embora pareça que possivelmente é também conhecido como “parâmetro sniffing”, o que pode explicar a escassez de accesss do Google quando eu tentei pesquisá-lo.)

Nós abstraímos alguns dos stored procedures para um segundo, envolvemos a chamada para este novo proc interno no externo pré-existente, chamado de externo e, ei, presto, foi tão rápido quanto a consulta original.

Então, o que dá? Alguém pode explicar o spoofing de parâmetro?

Crédito de bônus por

  • destacando como evitá-lo
  • sugerindo como reconhecer possíveis causas
  • discutir estratégias alternativas, por exemplo, statistics, índices, chaves, para mitigar a situação

   

FYI – você precisa estar ciente de outra coisa quando estiver trabalhando com o SQL 2005 e procs armazenados com parâmetros.

O SQL Server irá compilar o plano de execução do proc armazenado com o primeiro parâmetro usado. Então, se você executar isso:

usp_QueryMyDataByState 'Rhode Island' 

O plano de execução funcionará melhor com os dados de um pequeno estado. Mas se alguém se virar e correr:

 usp_QueryMyDataByState 'Texas' 

O plano de execução projetado para dados do tamanho de Rhode-Island pode não ser tão eficiente com dados do tamanho do Texas. Isso pode produzir resultados surpreendentes quando o servidor é reiniciado, porque o plano de execução recém-gerado será direcionado para qualquer parâmetro usado primeiro – não necessariamente o melhor. O plano não será recompilado até que haja um grande motivo para fazê-lo, como se as statistics fossem reconstruídas.

É aqui que entram os planos de consulta, e o SQL Server 2008 oferece vários novos resources que ajudam os DBAs a fixar um determinado plano de consulta a longo prazo, independentemente de quais parâmetros forem chamados primeiro.

Minha preocupação é que, quando você reconstrói seu procedimento armazenado, você forçou o plano de execução a recompilar. Você o chamou com seu parâmetro favorito e, claro, foi rápido – mas o problema pode não ter sido o procedimento armazenado. Pode ter sido que o procedimento armazenado foi recompilado em algum ponto com um conjunto incomum de parâmetros e, portanto, um plano de consulta ineficiente. Você pode não ter corrigido nada e pode enfrentar o mesmo problema na próxima vez em que o servidor for reiniciado ou o plano de consulta for recompilado.

Sim, eu acho que você quer dizer sniffing de parâmetro, que é uma técnica que o otimizador do SQL Server usa para tentar descobrir valores de parâmetros / intervalos para que ele possa escolher o melhor plano de execução para sua consulta. Em algumas instâncias, o SQL Server faz um trabalho insatisfatório no sniffing de parâmetro e não escolhe o melhor plano de execução para a consulta.

Acredito que este artigo do blog http://blogs.msdn.com/queryoptteam/archive/2006/03/31/565991.aspx tenha uma boa explicação.

Parece que o DBA no seu exemplo escolheu a opção # 4 para mover a consulta para outro sproc para um contexto processual separado.

Você também pode ter usado o with recompile no sproc original ou usado a opção optimize para o parâmetro.

Uma maneira simples de acelerar isso é reatribuir os parâmetros de input aos parâmetros locais no início do sproc, por exemplo

 CREATE PROCEDURE uspParameterSniffingAvoidance @SniffedFormalParameter int AS BEGIN DECLARE @SniffAvoidingLocalParameter int SET @SniffAvoidingLocalParameter = @SniffedFormalParameter --Work w/ @SniffAvoidingLocalParameter in sproc body -- ... 

Na minha experiência, a melhor solução para o sniffing de parâmetros é o ‘Dynamic SQL’. Duas coisas importantes a notar é que 1. você deve usar parâmetros em sua consulta sql dinâmica 2. você deve usar sp_executesql (e não sp_execute), que salva o plano de execução para cada valor de parâmetro

O sniffing de parâmetro é uma técnica que o SQL Server usa para otimizar o plano de execução de consulta para um procedimento armazenado. Quando você chama o procedimento armazenado pela primeira vez, o SQL Server examina os valores de parâmetro da sua chamada e decide quais índices usar com base nos valores do parâmetro.

Portanto, quando a primeira chamada não contém parâmetros muito comuns, o SQL Server pode selecionar e armazenar um plano de execução abaixo do ideal em relação às seguintes chamadas do procedimento armazenado.

Você pode contornar isso por qualquer

  • usando WITH RECOMPILE
  • copiando os valores de parâmetro para variables ​​locais dentro do procedimento armazenado e usando os locais em suas consultas.

Eu até ouvi dizer que é melhor não usar os stored procedures, mas enviar as suas perguntas diretamente para o servidor. Recentemente me deparei com o mesmo problema em que ainda não tenho uma solução real. Para algumas consultas, a cópia para as vars locais ajuda a voltar ao plano de execução correto; para algumas consultas, o desempenho é reduzido com as vars locais.

Eu ainda tenho que fazer mais pesquisas sobre como o SQL Server armazena em cache e reutiliza (sub-ótimos) planos de execução.

Eu tive problema semelhante. O plano de execução do meu procedimento armazenado levou de 30 a 40 segundos. Eu tentei usar o SP Statements na janela de consulta e demorou alguns ms para executar o mesmo. Então eu trabalhei declarando variables ​​locais dentro do procedimento armazenado e transferindo os valores dos parâmetros para variables ​​locais. Isso tornou a execução do SP muito rápida e agora o mesmo SP é executado dentro de alguns milissegundos, em vez de 30 a 40 segundos.

Muito simples e ordenado, o otimizador de consultas usa o antigo plano de consulta para consultas em execução com freqüência. mas, na verdade, o tamanho dos dados também está aumentando, então, nesse momento, é necessário um novo plano otimizado e, ainda assim, o otimizador de consultas usando o antigo plano de consulta. Isso é chamado de sniffing de parâmetro. Eu também criei um post detalhado sobre isso. Por favor, visite este url: http://www.dbrnd.com/2015/05/sql-server-parameter-sniffing/

Alterar o procedimento de armazenamento para executar como um lote deve aumentar a velocidade.

Seleção de arquivo em lote, ou seja:

 exec ('select * from order where order id ='''+ @ordersID') 

Em vez do procedimento armazenado normal, selecione:

 select * from order where order id = @ordersID 

Basta passar o parâmetro como nvarchar e você deve obter resultados mais rápidos.