Início / dba / Perguntas / 246734
Accepted
binki
Asked: 2019-09-02 22:29:54 +0800 CST

Por que essa CTE recursiva com um parâmetro não usa um índice quando usa um literal?

  • 772

Estou usando um CTE recursivo em uma estrutura de árvore para listar todos os descendentes de um nó específico na árvore. Se eu escrever um valor de nó literal na minha WHEREcláusula, o SQL Server parece realmente aplicar o CTE apenas a esse valor, fornecendo um plano de consulta com baixas contagens de linhas reais, etc .:

plano de consulta com valor literal

No entanto, se eu passar o valor como parâmetro, ele parece realizar (spool) o CTE e depois filtrá-lo após o fato :

plano de consulta com valor de parâmetro

Eu poderia estar lendo os planos errado. Não notei um problema de desempenho, mas estou preocupado que a realização do CTE possa causar problemas com conjuntos de dados maiores, especialmente em um sistema mais ocupado. Além disso, normalmente componho essa travessia em si mesma: percorro os ancestrais e volto para os descendentes (para garantir que reuni todos os nós relacionados). Devido à forma como meus dados são, cada conjunto de nós “relacionados” é bastante pequeno, então a realização do CTE não faz sentido. E quando o SQL Server parece perceber o CTE, ele está me dando alguns números bastante grandes em suas contagens “reais”.

Existe uma maneira de fazer com que a versão parametrizada da consulta funcione como a versão literal? Eu quero colocar o CTE em uma visualização reutilizável.

Consulta com literal:

CREATE PROCEDURE #c AS BEGIN;
    WITH descendants AS (SELECT
         t.ParentId Id
        ,t.Id DescendantId
    FROM #tree t
    WHERE t.ParentId IS NOT NULL
    UNION ALL SELECT
         d.Id
        ,t.Id DescendantId
    FROM descendants d
    JOIN #tree t ON d.DescendantId = t.ParentId)
    SELECT d.*
    FROM descendants d
    WHERE d.Id = 24
    ORDER BY d.Id, d.DescendantId;
END;
GO
EXEC #c;

Consulta com parâmetro:

CREATE PROCEDURE #c (@Id BIGINT) AS BEGIN;
    WITH descendants AS (SELECT
         t.ParentId Id
        ,t.Id DescendantId
    FROM #tree t
    WHERE t.ParentId IS NOT NULL
    UNION ALL SELECT
         d.Id
        ,t.Id DescendantId
    FROM descendants d
    JOIN #tree t ON d.DescendantId = t.ParentId)
    SELECT d.*
    FROM descendants d
    WHERE d.Id = @Id
    ORDER BY d.Id, d.DescendantId;
END;
GO
EXEC #c 24;

Código de configuração:

DECLARE @count BIGINT = 100000;
CREATE TABLE #tree (
     Id BIGINT NOT NULL PRIMARY KEY
    ,ParentId BIGINT
);
CREATE INDEX tree_23lk4j23lk4j ON #tree (ParentId);
WITH number AS (SELECT
         CAST(1 AS BIGINT) Value
    UNION ALL SELECT
         n.Value * 2 + 1
    FROM number n
    WHERE n.Value * 2 + 1 <= @count
    UNION ALL SELECT
         n.Value * 2
    FROM number n
    WHERE n.Value * 2 <= @count)
INSERT #tree (Id, ParentId)
SELECT n.Value, CASE WHEN n.Value % 3 = 0 THEN n.Value / 4 END
FROM number n;
sql-server optimization

2 respostas

  1. Best Answer

    A resposta de Randi Vertongen aborda corretamente como você pode obter o plano desejado com a versão parametrizada da consulta. Esta resposta complementa isso, abordando o título da pergunta, caso você esteja interessado nos detalhes.

    SQL Server reescreve expressões de tabela comum (CTEs) recursivas de cauda como iteração. Tudo, desde o Lazy Index Spool , é a implementação em tempo de execução da tradução iterativa. Eu escrevi um relato detalhado de como esta seção de um plano de execução funciona em resposta a Usando EXCEPT em uma expressão de tabela comum recursiva .

    Você deseja especificar um predicado (filtro) fora do CTE e fazer com que o otimizador de consulta empurre esse filtro para dentro da recursão (reescrito como iteração) e o aplique ao membro âncora. Isso significa que a recursão começa apenas com os registros que correspondem ParentId = @Id.

    Esta é uma expectativa bastante razoável, quer seja usado um valor literal, variável ou parâmetro; no entanto, o otimizador só pode fazer coisas para as quais as regras foram escritas. As regras especificam como uma árvore de consulta lógica é modificada para obter uma transformação específica. Eles incluem lógica para garantir que o resultado final seja seguro - ou seja, ele retorna exatamente os mesmos dados que a especificação da consulta original em todos os casos possíveis.

    A regra responsável por enviar predicados em uma CTE recursiva é chamada SelOnIterator- uma seleção relacional (= predicado) em um iterador que implementa a recursão. Mais precisamente, esta regra pode copiar uma seleção para a parte âncora da iteração recursiva:

    Sel(Iter(A,R)) -> Sel(Iter(Sel(A),R))

    Esta regra pode ser desabilitada com a dica não documentada OPTION(QUERYRULEOFF SelOnIterator). Quando isso é usado, o otimizador não pode mais enviar predicados com um valor literal para a âncora de uma CTE recursiva. Você não quer isso, mas ilustra o ponto.

    Originalmente, essa regra se limitava a trabalhar apenas em predicados com valores literais. Também pode ser feito para trabalhar com variáveis ​​ou parâmetros especificando OPTION (RECOMPILE), já que essa dica habilita a Otimização de Incorporação de Parâmetros , em que o valor literal de tempo de execução da variável (ou parâmetro) é usado ao compilar o plano. O plano não é armazenado em cache, portanto, a desvantagem disso é uma nova compilação em cada execução.

    Em algum momento, a SelOnIteratorregra foi aprimorada para trabalhar também com variáveis ​​e parâmetros. Para evitar alterações inesperadas no plano, isso foi protegido pelo sinalizador de rastreamento 4199, nível de compatibilidade do banco de dados e nível de compatibilidade do hotfix do otimizador de consulta. Este é um padrão bastante normal para melhorias do otimizador, que nem sempre são documentadas. As melhorias são normalmente boas para a maioria das pessoas, mas sempre há uma chance de que qualquer mudança introduza uma regressão para alguém.

    Quero colocar o CTE em uma visualização reutilizável

    Você pode usar uma função com valor de tabela embutida em vez de uma exibição. Forneça o valor que você deseja enviar como parâmetro e coloque o predicado no membro âncora recursivo.

    Se preferir, habilitar o sinalizador de rastreamento 4199 globalmente também é uma opção. Há muitas alterações no otimizador cobertas por esse sinalizador, portanto, você precisaria testar cuidadosamente sua carga de trabalho com ele ativado e estar preparado para lidar com regressões.

    • 12

  2. Embora no momento eu não tenha o título do hotfix real, o melhor plano de consulta será usado ao habilitar os hotfixes do otimizador de consulta em sua versão (SQL Server 2012).

    Alguns outros métodos são:

    • Usando OPTION(RECOMPILE)então a filtragem acontece antes, no valor literal.
    • No SQL Server 2016 ou superior, os hotfixes anteriores a esta versão são aplicados automaticamente e a consulta também deve ser executada de forma equivalente ao melhor plano de execução.

    Correções do otimizador de consultas

    Você pode habilitar essas correções com

    • Traceflag 4199 antes do SQL Server 2016
    • ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES=ON;a partir do SQL Server 2016. (não é necessário para sua correção)

    A filtragem @idé aplicada anteriormente aos membros recursivos e âncora no plano de execução com o hotfix habilitado.

    O traceflag pode ser adicionado no nível da consulta:

    OPTION(QUERYTRACEON 4199)

    Ao executar a consulta no SQL Server 2012 SP4 GDR ou SQL Server 2014 SP3 com Traceflag 4199, o melhor plano de consulta é escolhido:

    ALTER PROCEDURE #c (@Id BIGINT) AS BEGIN;
    WITH descendants AS (SELECT
         t.ParentId Id
        ,t.Id DescendantId
    FROM #tree t 
    WHERE t.ParentId IS NOT NULL
    UNION ALL 
    SELECT
         d.Id
        ,t.Id DescendantId
    FROM descendants d
    JOIN #tree t ON d.DescendantId = t.ParentId)
    SELECT d.*
    FROM descendants d
    WHERE d.Id = @Id
    ORDER BY d.Id, d.DescendantId
    OPTION( QUERYTRACEON 4199 );

END;
GO
EXEC #c 24;

    Plano de consulta no SQL Server 2014 SP3 com traceflag 4199

    Plano de consulta no SQL Server 2012 SP4 GDR com traceflag 4199

    Plano de consulta no SQL Server 2012 SP4 GDR sem traceflag 4199

    O principal consenso é habilitar o traceflag 4199 globalmente ao usar uma versão anterior ao SQL Server 2016. Depois fica aberto para discussão se habilitá-lo ou não. AQ/A sobre isso aqui .

    Nível de compatibilidade 130 ou 140

    Ao testar a consulta parametrizada em um banco de dados com compatibility_level= 130 ou 140, a filtragem acontece mais cedo:

    insira a descrição da imagem aqui

    Devido ao fato de que as correções 'antigas' do traceflag 4199 estão habilitadas no SQL Server 2016 e superior.

    OPÇÃO (RECOMPILAR)

    Mesmo que um procedimento seja usado, o SQL Server poderá filtrar o valor literal ao adicionar arquivos OPTION(RECOMPILE);.

    ALTER PROCEDURE #c (@Id BIGINT) AS BEGIN;
    WITH descendants AS (SELECT
         t.ParentId Id
        ,t.Id DescendantId
    FROM #tree t 
    WHERE t.ParentId IS NOT NULL
    UNION ALL 
    SELECT
         d.Id
        ,t.Id DescendantId
    FROM descendants d
    JOIN #tree t ON d.DescendantId = t.ParentId)
    SELECT d.*
    FROM descendants d
    WHERE d.Id = @Id
    ORDER BY d.Id, d.DescendantId
OPTION(
RECOMPILE )

END;
GO

    insira a descrição da imagem aqui

    Plano de consulta no SQL Server 2012 SP4 GDR com OPTION(RECOMPILE)

    • 10

relate perguntas