Return to Answer

quatro

I don't have your database to experiment with, but I can make a reasonable go at things with my copy of the StackOverflow database.

To get things going, I added a couple indexes:

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);

And a function similar to yours:

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
);

There is only about a 30 second difference in timing for me, but the reason why is both in your query plans and mine. Here are some screenshots of parallel row distribution in your two new example query plans:

Of course, that issue wasn't obvious in the original query plan(s) you shared, because Batch Mode in Standard Edition was limiting you to a DOP of 2. In a fully row mode plan, where you can use a much higher DOP, the issue becomes clear.

You can learn a whole lot more about this in Adam Machanic's session on row goals.

There are the Actual Time Statistics for the Hash Match Aggregate. Note the high duration (wall clock time) vs. very low CPU time.

Here are the Actual Time Statistics for the Hash Match Aggregate. Note the high duration (wall clock time) vs. very low CPU time.