Início / dba / Perguntas / 260639
Accepted
Data Dill
Asked: 2020-02-27 07:13:16 +0800 CST

Por que esse proc armazenado causa uma varredura de índice clusterizado, mas procura ao usar OPTION RECOMPILE?

  • 772

Acho que posso saber a resposta com base em minha pesquisa, mas estou procurando confirmação de como/por que o mecanismo compila o plano da maneira que faz com

Parâmetros sendo passados: @ID int ,@OtherID INT

SELECT  b.Column1
        ,b.Column2
        ,b.Column3
        ,b.Column4
        ,b.Column5
        ,c.Column1
        ,b.Column1
        ,e.Column1
FROM    Table1 AS b 
        inner join Table2 AS t
        on b.ID = t.ID
        left join [LINKED SERVER].[DB].dbo.Table3 as c
        on b.ID = c.ID
        left join Table4 AS e
        on b.ID= e.ID
where   (b.ID = @ID or @ID= 0)
        And b.ID = @OtherID
        And b.ID IS NOT NULL
        and e.ID = 1

Agora eu determinei que a causa da varredura de índice é por causa desta linha: where (b.ID = @ID or @ID= 0). Mais especificamente, @ID = 0. Para esclarecer ainda mais, 0 para esse campo de ID não existe como um valor na tabela subjacente, foi simplesmente algo que um desenvolvedor fez para permitir que um usuário recuperasse todos os resultados passando 0 para o parâmetro e, em seguida, verificando se esse parâmetro é 0, como resultado, mais linhas são puxadas de volta (normalmente, você retornaria apenas 1-3 resultados).

Agora, o que é extremamente estranho, é que se eu adicionar OPTION RECOMPILE, o mecanismo é capaz de criar um plano muito melhor ao custo de sobrecarga (tempo de compilação), é claro:

insira a descrição da imagem aqui

O que eu gostaria de saber é como isso é possível. Pelo que li online, usando OPTION RECOMPILE, o mecanismo substituirá literalmente o valor pelo valor real passado para o parâmetro e pode ver muito facilmente que @ID 1234 não é igual a 0. No entanto, se você não usar OPTION RECOMPILEo mecanismo pegará o número total de registros, que é 120.000, e o dividirá pelo número total de possibilidades distintas, 107.000. Isso resulta em cerca de 1,1 linhas estimadas sendo retornadas e confirmei isso observando as propriedades estimadas do plano que tem a verificação de índice, mas por que o mecanismo continuaria a verificação de índice se a estimativa estiver correta? Até atualizei as estatísticas só para ter certeza.

insira a descrição da imagem aqui

sql-server optimization

2 respostas

  1. Best Answer
    b.ID = @ID OR @ID = 0

    O otimizador precisa produzir um plano com uma varredura de índice, porque o plano é armazenado em cache e reutilizado .

    Em uma execução subsequente, o parâmetro @IDpode ser zero. Uma busca de índice não tem valor nesse caso, porque não há IDvalor a ser buscado. Outras vezes, haverá um valor diferente de zero fornecido para @ID, mas o plano em cache precisa funcionar corretamente para todos os valores de parâmetro possíveis.

    Quando OPTION (RECOMPILE)é usado, a Otimização de Incorporação de Parâmetros (PEO) significa que o valor atual para @IDé usado no lugar do parâmetro em cada execução e nenhum plano é armazenado em cache.

    Digamos @IDque seja 1234. Após PEO, o otimizador vê:

    b.ID = 1234 OR 1234 = 0

    Isso é simplificado pela lógica de detecção de contradição para:

    b.ID = 1234

    ...que permite uma busca em ID.

    Para ler mais, consulte meu artigo Parameter Sniffing, Embedding, and the RECOMPILE Options .

    • 2

  2. Seu problema é condição opcional(b.ID = @ID or @ID= 0)

    Se você não quiser usar OPTION(RECOMPILE), você deve dividir sua consulta na condição:

    IF @ID = 0 BEGIN
    SELECT b.Column1, 
           b.Column2, 
           b.Column3, 
           b.Column4, 
           b.Column5, 
           c.Column1, 
           b.Column1, 
           e.Column1
    FROM Table1 AS b
         INNER JOIN Table2 AS t ON b.ID = t.ID
         LEFT JOIN [LINKED SERVER].[DB].dbo.Table3 AS c ON b.ID = c.ID
         LEFT JOIN Table4 AS e ON b.ID = e.ID
    WHERE b.ID = @OtherID
          AND b.ID IS NOT NULL
          AND e.ID = 1;
END ELSE BEGIN
    SELECT b.Column1, 
           b.Column2, 
           b.Column3, 
           b.Column4, 
           b.Column5, 
           c.Column1, 
           b.Column1, 
           e.Column1
    FROM Table1 AS b
         INNER JOIN Table2 AS t ON b.ID = t.ID
         LEFT JOIN [LINKED SERVER].[DB].dbo.Table3 AS c ON b.ID = c.ID
         LEFT JOIN Table4 AS e ON b.ID = e.ID
    WHERE b.ID = @ID
          AND b.ID = @OtherID
          AND b.ID IS NOT NULL
          AND e.ID = 1;
END
    • 1

relate perguntas