Início / dba / Perguntas / 6502
Accepted
msgisme
Asked: 2011-10-05 07:54:26 +0800 CST

O código cria um plano diferente quando executado ad-hoc em comparação com um procedimento armazenado

  • 772

Eu tenho uma instrução delete que está usando um plano ruim quando executado dentro de um procedimento armazenado, mas está escolhendo um plano muito melhor quando executado ad-hoc.

Reconstruí todos os índices das tabelas usadas pela consulta e eliminei todos os caches. O otimizador ainda escolhe o plano errado para o procedimento armazenado.

Gostaria de saber por que o otimizador está usando um plano de execução diferente para o procedimento armazenado versus o SQL ad-hoc.

sql-server execution-plan

2 respostas

  1. Best Answer

    Suspeitos do costume:

    1. constantes em ad hoc, parâmetros em código.
      O otimizador pode então escolher o melhor plano para as constantes.
      Alterar as constantes = alterar o plano. Um plano parametrizado é reutilizável.

    2. incompatibilidade de tipos de dados no código.
      introduzirá conversões implícitas devido à precedência de tipo de dados.
      por exemplo, coluna varchar comparada ao parâmetro nvarchar.

    3. detecção de parâmetros.
      use mascaramento de parâmetro ou OTIMIZE PARA DESCONHECIDO

    Para testar: execute stored proc, execute sp_updatestats, execute novamente. Isso invalidará os planos em cache, o que é melhor do que limpar todo o cache do plano.

    Você pode desativar o sniffing de várias maneiras. Os 3 principais são

    • RECOMPILE. Isso é bobagem na minha opinião.
    • OPTIMIZE FOR UNKNOWN
    • Máscara de parâmetro

    O mascaramento de parâmetros se parece com isto:

    DECLARE @MaskedParam varchar(10)
SELECT @MaskedParam = @SignaureParam

SELECT...WHERE column = @MaskedParam

    O mascaramento e a OPTIMIZEdica têm o mesmo efeito (talvez por motivos diferentes). Ou seja, o otimizador tem que usar estatísticas e distribuição de dados para avaliar os parâmetros por seus próprios méritos ? , em vez do que eram na última chamada. O otimizador pode recompilar ou não. O SQL Server 2005 adicionou recompilação em nível de instrução para que houvesse menos impacto.

    Agora, por que um plano com parâmetros "sniffed" é "pegajoso" em comparação com parâmetros mascarados/"desconhecidos", não tenho certeza.

    Eu usei o mascaramento de parâmetros desde o SQL Server 2000 para todos, exceto o código mais simples. Observei que isso pode acontecer com códigos mais complexos. E no meu antigo emprego, tenho alguns processos de relatório que eu poderia alterar os padrões de parâmetro do plano. Acho que a abordagem de "culto à carga" foi mais fácil do que uma chamada de suporte.

    • O mascaramento de parâmetros e OPTIMISE FOR UNKNOWNtem o mesmo efeito, tanto quanto eu posso dizer.
      A dica é mais limpa que a máscara, mas foi adicionada com o SQL Server 2008.

    • A detecção de parâmetros ocorre em tempo de compilação.
      WITH RECOMPILEgera um novo plano a cada execução. Isso significa que uma má escolha de padrões influenciará o plano. No meu último trabalho, pude demonstrar isso facilmente com algum código de relatório: a alteração dos padrões dos parâmetros alterou o plano independentemente dos parâmetros fornecidos.

    • Este artigo do MS Connect é interessante: Uso de índice abaixo do ideal no procedimento armazenado (mencionado em uma das respostas do SO abaixo)

    • Bob Beauchemin menciona isso também

    Problemas pendentes

    • O sniffing ainda se aplica a WITH RECOMPILE? Ou seja, se o otimizador sabe descartar o plano ele visa a reutilização?

    • Por que os planos cheirados são "pegajosos"?

    Links do SO:

    • 6

  2. Não se esqueça de que as configurações ANSI que você configurou para o plano de conexão têm um papel na seleção do plano de execução. Quando o aplicativo chama o procedimento armazenado, ele provavelmente tem configurações ANSI diferentes da sua conexão SSMS.

    • 3

relate perguntas