Início / dba / Perguntas / 15269
Accepted
ivanmp
Asked: 2012-03-20 10:03:58 +0800 CST

Diferença monumental no tempo de execução entre as consultas ao usar a dica de consulta RECOMPILE

  • 772

Tenho duas consultas quase idênticas em execução na mesma instância do SQL Server 2005:

  1. A primeira é a SELECTconsulta original gerada pelo LINQ (eu sei, eu sei... não sou o desenvolvedor do aplicativo, apenas o DBA :).
  2. O segundo é exatamente igual ao primeiro, acrescido de um OPTION (RECOMPILE)no final.

Nada mais foi alterado.

O primeiro leva 55 segundos toda vez que é executado.
O segundo leva 2 segundos.

Ambos os conjuntos de resultados são idênticos.

Por que essa dica geraria um ganho tão dramático no desempenho?

A entrada do Books Online em RECOMPILEnão oferece uma explicação muito detalhada:

Instrui o SQL Server Database Engine a descartar o plano gerado para a consulta após sua execução, forçando o otimizador de consulta a recompilar um plano de consulta na próxima vez que a mesma consulta for executada. Sem especificar RECOMPILE, o Mecanismo de Banco de Dados armazena em cache os planos de consulta e os reutiliza. Ao compilar planos de consulta, a dica de consulta RECOMPILE usa os valores atuais de quaisquer variáveis ​​locais na consulta e, se a consulta estiver dentro de um procedimento armazenado, os valores atuais passados ​​para quaisquer parâmetros.

RECOMPILE é uma alternativa útil para criar um procedimento armazenado que usa a cláusula WITH RECOMPILE quando apenas um subconjunto de consultas dentro do procedimento armazenado, em vez de todo o procedimento armazenado, deve ser recompilado. Para obter mais informações, consulte Recompilando procedimentos armazenados. RECOMPILE também é útil quando você cria guias de plano. Para obter mais informações, consulte Otimizando consultas em aplicativos implantados usando guias de plano.

Como minha consulta tem muitas variáveis ​​locais, meu palpite é que o SQL Server é capaz de otimizá-la (sério) quando uso a OPTION (RECOMPILE)dica de consulta.

Onde quer que eu olhe, as pessoas estão dizendo que isso OPTION (RECOMPILE)deve ser evitado. A explicação para isso geralmente é que, usando essa dica, o SQL Server não é capaz de reutilizar esse plano de execução e, portanto, precisa perder tempo recompilando-o sempre.
(Mas) Dada a gigantesca vantagem de desempenho, estou inclinado a pensar que usar essa dica de consulta desta vez seria uma coisa boa.

Devo usá-lo? Se não, existe uma maneira de forçar o SQL Server a usar um plano de execução melhor sem essa dica e sem alterar o aplicativo?

sql-server sql-server-2005

1 respostas

  1. Best Answer

    Conforme documentado no artigo Estatísticas usadas pelo otimizador de consultas no Microsoft SQL Server 2005

    Se você usar uma variável local em um predicado de consulta em vez de um parâmetro ou literal, o otimizador recorrerá a uma estimativa de qualidade reduzida ou a uma suposição de seletividade do predicado. Use parâmetros ou literais na consulta em vez de variáveis ​​locais

    Quando o otimizador não tiver nenhuma estatística utilizável para uma coluna, ele adivinhará que um =predicado corresponderá a 10% das linhas, BETWEEN9% e qualquer uma delas >, >=, < and <=corresponderá a 30%. Se houver estatísticas de coluna disponíveis, um =predicado será tratado de forma diferente conforme abaixo.

    Mesmo quando variáveis ​​locais são usadas em uma consulta, uma estimativa melhor do que um palpite é usada no caso de predicados de igualdade. A seletividade para condições no formato " @local_variable = column_name" é estimada usando a frequência do valor médio do histograma para column_name. Assim, por exemplo, se a coluna column_name contiver todos os valores exclusivos, uma estimativa de seletividade 1/(number of unique values in column)será usada, o que é preciso.

    Portanto, isso é essencialmente o mesmo que usar for OPTIMIZE FOR (UNKNOWN). Pode muito bem ser mais preciso do que um 10%palpite simples, mas não é adaptado aos valores específicos que você está consultando.

    Para forçar o SQL Server a otimizar uma consulta toda vez que ela for executada e usar os valores das variáveis ​​locais para estimar a cardinalidade e o custo durante a otimização da consulta, adicione a RECOMPILEdica de consulta à consulta.

    Com o uso de RECOMPILEvocê provavelmente está obtendo estimativas de cardinalidade mais precisas e, portanto, um plano diferente com pedidos de junção/tipos de junção mais adequados ao número de linhas retornadas de diferentes partes de sua consulta real.

    • 16

relate perguntas