Como posso otimizar um CTE recursivo dentro de um IVTF?

Quanto mais olho para isso, mais dúvidas tenho em relação ao plano de consulta. Provavelmente isso não responderá à sua pergunta na primeira tentativa, mas vamos começar.

um

Primeiro, o XML do plano indica que você está no SQL Server 2022 RTM (16.0.1000.6), o que coloca 11 atualizações cumulativas atrás das atuais. Não estou culpando seus problemas, mas não custa nada estar atualizado.

dos

Em segundo lugar, e mais intrigante: você está na edição Standard ou Enterprise do SQL Server ou Individusuma coluna armazena índice em algum lugar?

A razão pela qual pergunto é porque o otimizador decidiu que um operador em seu plano deveria ser executado em modo lote:

Na ausência de um índice de armazenamento de coluna, isso só acontece na Enterprise Edition com o recurso Intelligent Query Processing, Batch Mode On Row Store.

O que torna isso ainda mais confuso é que seu plano só é executado com um Grau de Paralelismo (DOP) de 2. Limitar as operações do modo em lote a um DOP de 2 é algo que apenas a Standard Edition faz.

Normalmente eu diria que você está em uma caixa com pouca potência, mas o plano de consulta também indica que você tem 12 threads disponíveis para uma consulta paralela. Talvez o DOP esteja limitado a dois lugares que o otimizador desconhece.

EstimatedAvailableDegreeOfParallelism="12"

Isso se torna lamentável no seu caso no Hash Match Aggregate mencionado acima, porque um thread fica parado por cerca de 22 segundos aguardando no HTBUILD.

<Wait WaitType="HTBUILD" WaitTimeMs="22113" WaitCount="2" />

Aqui estão as estatísticas de tempo real para o agregado de correspondência de hash. Observe a alta duração (tempo de relógio) versus tempo de CPU muito baixo.

Uma coisa que você deve tentar é encerrar sua consulta com OPTION(USE HINT('DISALLOW_BATCH_MODE'), MAXDOP 8);.

tres

Deixando tudo isso de lado, sua consulta tem outras esperas bem estranhas.

 <Wait WaitType="PAGELATCH_SH" WaitTimeMs="24798" WaitCount="7034617" />
 <Wait WaitType="PAGELATCH_EX" WaitTimeMs="23758" WaitCount="6969758" />
 

PAGELATCH_SH e PAGELATCH_EX combinados representam aproximadamente 49 segundos de tempo de espera enquanto sua consulta é executada. Aqui estão a maioria dos horários do operador em seu plano antes do agregado de correspondência de hash do modo em lote:

Coloquei setas apontando para dois operadores que usam tempdb para armazenamento, porque às vezes (mas nem sempre, claro) aquelas esperas PAGELATCH_XX de que acabei de falar estão associadas à atividade lá. Não há como contornar esses spools em um plano de execução de expressão de tabela comum recursiva que eu conheça.

Ler os tempos dos operadores é um pouco peculiar. No Modo Linha, os operadores pais mostram não apenas seu tempo, mas também o tempo gasto nos operadores filhos. No Modo Lote cada operador mostra apenas o tempo gasto.

Um dos motivos pelos quais mencionei a possível contenção de tempdb aqui é que seu Lazy Index Spool fica bastante grande, com cerca de 14 milhões de linhas, e como carregá-lo com dados é a única coisa que geraria esperas de PAGELATCH_EX, vale a pena verificar.

está bem então

Dito isso, a estimativa de cardinalidade não é a raiz do seu problema aqui, e usar um MSTVF, que usaria uma variável de tabela, provavelmente não ajudaria na sua situação de tempdb ou na sua situação de estimativa de cardinalidade.

Embora as variáveis ​​de tabela nas versões mais recentes do SQL Server possam obter estimativas de cardinalidade no nível da tabela, elas ainda não obtêm nenhum histograma no nível da coluna criado para elas, mesmo se você adicionar um índice. Eles também causarão uma zona serial no seu plano, sempre que os dados forem modificados neles (no seu caso, na inserção) e quando a variável da tabela retornada for lida pela consulta externa.

Edite sua pergunta para adicionar detalhes sobre a leitura. Tentarei acompanhar conforme o tempo permitir para editar minha resposta de acordo.

quatro

Não tenho seu banco de dados para experimentar, mas posso fazer uma tentativa razoável com minha cópia do banco de dados StackOverflow.

Para começar, adicionei alguns índices:

CREATE UNIQUE INDEX 
    HierarychyHelper 
ON dbo.Posts 
    (ParentId, Id, OwnerUserId) 
INCLUDE
    (Score) 
WITH 
    (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

CREATE UNIQUE INDEX 
    HierarychyHelper 
ON dbo.Comments 
    (PostId, Id) 
INCLUDE
    (Score, UserId) 
WITH 
    (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

E uma função semelhante à sua:

CREATE OR ALTER FUNCTION
    dbo.HierarchyHelper
(
    @PostId integer
)
RETURNS table 
WITH SCHEMABINDING
AS
RETURN
(
    WITH 
        postparent AS 
    (
        SELECT 
            p.Id, 
            p.ParentId, 
            p.OwnerUserId,
            p.Score
        FROM dbo.Posts AS p WITH(FORCESEEK)
        WHERE p.Id = @PostId
      
        UNION ALL
      
        SELECT 
            p2.Id, 
            p2.ParentId, 
            p2.OwnerUserId,
            p2.Score
        FROM postparent pp
        JOIN dbo.Posts AS p2 WITH(FORCESEEK)
          ON pp.Id = p2.ParentId
      
        UNION ALL
      
        SELECT 
            c.Id, 
            c.PostId, 
            c.UserId,
            c.Score
        FROM postparent pp
        JOIN dbo.Comments AS c WITH(FORCESEEK)
          ON  pp.Id = c.PostId
    )
    SELECT
        pp.Id,
        pp.ParentId,
        pp.OwnerUserId,
        pp.Score
    FROM postparent AS pp
);
GO

And then a starting query that results in a Lazy Spool that gets about 14 million rows and change in it. I have some hints here to better match your environment.

SELECT
    c = COUNT_BIG(*)
FROM dbo.Posts AS p
CROSS APPLY dbo.HierarchyHelper(p.Id) AS hh
WHERE p.CreationDate >= '20130501'
OPTION
(
    USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_160'), 
    MAXDOP 8
);

That query takes about 1 minute for me. Part of the problem is that recursive common table expressions don't generally qualify for parallel plans, and that parallel plans involving Nested Loops joins really just run DOP copies of the serial plan. Because of that, they're really sensitive to uneven row distributions on parallel threads.

The reason why the query with a TOP in it runs so much faster is because the Top operator introduces a serial zone in the query plan, which requires a Distribute Streams Exchange operator to move the rows back into a parallel portion of the plan.

I can duplicate the behavior by copying your queries:

SELECT
    c = COUNT_BIG(*)
FROM dbo.Posts AS p
CROSS APPLY dbo.HierarchyHelper(p.Id) AS hh
WHERE p.CreationDate >= '20130501'
OPTION
(
    USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_160'), 
    MAXDOP 8
);


SELECT
    c = COUNT_BIG(*)
FROM 
(
    SELECT TOP (2147483647) 
        p.* 
    FROM dbo.Posts AS p 
    WHERE p.CreationDate >= '20130501'
) AS p
CROSS APPLY dbo.HierarchyHelper(p.Id) AS hh
OPTION
(
    USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_160'), 
    MAXDOP 8
);

Há apenas uma diferença de tempo de cerca de 30 segundos para mim, mas o motivo está nos seus planos de consulta e nos meus. Aqui estão algumas capturas de tela da distribuição de linhas paralelas em seus dois novos exemplos de planos de consulta:

É claro que esse problema não era óbvio nos planos de consulta originais que você compartilhou, porque o modo em lote na Standard Edition estava limitando você a um DOP de 2. Em um plano de modo totalmente em linha, onde você pode usar um DOP muito mais alto , a questão fica clara.

Você pode aprender muito mais sobre isso na sessão de Adam Machanic sobre metas de linha .

Como você governou tudo além dos ITVFs, nunca obterá uma boa estimativa de cardinalidade com isso. Simplesmente não há como saber em tempo de compilação quantas linhas você obterá. As restrições do ITVF excluem quaisquer truques úteis, como OPTION (RECOMPILE)tabelas temporárias. Dito isto, não vejo nenhuma razão para você ter descartado funções com valor de tabela com múltiplas instruções. Você já pensou em usar variáveis ​​de tabela e OPTION (RECOMPILE)?

Desafio do quadro: pare de usar CTEs recursivos para esta tarefa. hierarquiaid é a solução moderna. Melhor ainda, evite completamente o SQL, se puder. Este é um trabalho para um banco de dados gráfico.