Início / dba / Perguntas / 104686
Accepted
Martin Smith
Asked: 2015-06-22 08:17:56 +0800 CST

Por que referenciar uma variável em um predicado de junção força loops aninhados?

  • 772

Me deparei com esse problema recentemente e não consegui encontrar nenhuma discussão sobre ele online.

A consulta abaixo

DECLARE @S VARCHAR(1) = '';

WITH T
     AS (SELECT name + @S AS name2,
                *
         FROM   master..spt_values)
SELECT *
FROM   T T1
       INNER JOIN T T2
         ON T1.name2 = T2.name2;

Sempre obtém um plano de loops aninhados

insira a descrição da imagem aqui

A tentativa de forçar o problema com INNER HASH JOINou INNER MERGE JOINdicas produz o seguinte erro.

O processador de consulta não pôde produzir um plano de consulta devido às dicas definidas nesta consulta. Reenvie a consulta sem especificar nenhuma dica e sem usar SET FORCEPLAN.

Encontrei uma solução alternativa que permite o uso de junções de hash ou mesclagem - envolvendo a variável em um agregado. O plano gerado tem um custo significativamente menor (19,2025 vs 0,261987)

DECLARE @S2 VARCHAR(1) = '';

WITH T
     AS (SELECT name + (SELECT MAX(@S2)) AS name2,
                *
         FROM   spt_values)
SELECT *
FROM   T T1
       INNER JOIN T T2
         ON T1.name2 = T2.name2;

insira a descrição da imagem aqui

Qual é a razão para este comportamento? e existe uma solução alternativa melhor do que a que encontrei? (que talvez não exija as ramificações extras do plano de execução)

sql-server performance

2 respostas

  1. Best Answer

    Eu tentei sua consulta em uma instância do SQL 2012 e o sinalizador de rastreamento 4199 parece corrigir o problema. Com ele ativado, obtenho uma junção de mesclagem por um custo total de 0,24 e nenhuma das ramificações extras.

    O artigo da base de conhecimento específico para este problema é Problemas de desempenho ocorrem quando o predicado de junção em sua consulta tem colunas de referência externa no SQL Server 2005 ou no SQL Server 2008

    insira a descrição da imagem aqui

    Para qualificar ainda mais, o TF 4199 habilita todas as correções do otimizador. Veja este link para mais informações. A ativação de tudo de uma vez pode ter efeitos colaterais estranhos; portanto, se você encontrar uma correção específica, talvez seja melhor ativar a correção por conta própria.

    Você pode ativar um sinalizador de rastreamento por consulta usando OPTION (QUERYTRACEON 4199);

    • 13

  2. Pergunta antiga, mas vendo que a resposta não era super definitiva, pensei em postar uma solução alternativa que encontrei. Não sei por que o otimizador de consulta se recusa a HASH, mas acho que não gosta MERGEporque não tem entrada classificada. Em 2012/14,

    DECLARE @S VARCHAR(1) = '';

    WITH T
        AS (SELECT TOP (2147483647)
                name + @S AS name2,
                *
            FROM   master..spt_values
            ORDER BY name + @S)
    SELECT *
    FROM   T T1
           INNER JOIN T T2
             ON T1.name2 = T2.name2;

    produz o seguinte plano:

    insira a descrição da imagem aqui

    Forçar TOPe ORDER BYno cte parece dar ao otimizador conhecimento suficiente sobre o conjunto de dados para executar o MERGE JOIN.

    • 0

relate perguntas