Inexplicably huge read counts for 'Worktable' when multiple aggregates involved

Question

A query that selects multiple aggregates from a couple million rows shows mysteriously poor performance, orders of magnitude slower than the same query in Postgres, and orders of magnitude slower than calculating each aggregate in its own separate SQL query.

STATISTICS IO reveals an inexplicably high count for logicals reads from 'Worktable', something like 157 million logicals reads when processing 50 million rows (92917 pages read from the source table). The behaviour is exactly the same in all SQL Server versions and editions that I have access to, from SQL Server 2008R2 through SQL Server 2019.

The simplest query exhibiting this behaviour which I could find looks like this:

select count(*), count(distinct m13), count(distinct m23) from foo

m13 and m23 are low-cardinality foreign keys, which are simulated by taking the modulo of the record number in the following repro script (currently configured for processing 50% of 10,000,000 rows):

-- note: modify the cross join in the CTE for X in order to achieve the desired table size
if object_id('tempdb..#T') is null begin
    create table #T (n int primary key clustered, m13 tinyint not null, m23 tinyint not null);
    with -- ISNULL() removes the stain of perceived nullability
        D as (select isnull(value, 0) as value from (values (0), (1), (2),(3),(4),(5),(6),(7),(8),(9)) v (value)),
        H as (select isnull(d1.value * 10 + d0.value, 0) as value from D d1 cross join D d0),
        K as (select isnull(h.value * 10 + d.value, 0) as value from H h cross join D d),
        M as (select isnull(k1.value * 1000 + k0.value, 0) as value from K k1 cross join K k0),
        -- using ROW_NUMBER() here to allow easy composition via cross join, for freely choosing the target table size
        X as (select isnull(row_number() over (order by (select null)), 0) as value from D cross join M)
    insert into #T
        select value, value % 13, value % 23 from X;
end;
    
-- dial the desired percentage of rows to skip via the factor here
declare @threshold int = (select floor(0.50 * max(n) + 1) from #T);

select compatibility_level, @@version as "@@version" from sys.databases where name = db_name();
select '@threshold = ' + cast(@threshold as varchar);

set statistics time on;
set statistics io on;

select count(*)                                           from #T where n >= @threshold;
select count(*), count(distinct m13)                      from #T where n >= @threshold;
select count(*), count(distinct m13), count(distinct m23) from #T where n >= @threshold;

-- logical reads from 'Worktable' for @threshold at 50%:
--     10094 for 1e4 (   12 pages read from #T)
--    100942 for 1e5 (   96 pages read from #T)
--   1306626 for 1e6 (  933 pages read from #T)
--  14891285 for 1e7 ( 9295 pages read from #T)
-- 157450233 for 1e8 (92917 pages read from #T)

print char(13) + '### now the same in piece-meal fashion ... ###';

declare @cnt int, @m13 int, @m23 int = (select count(distinct m23) from #T where n >= @threshold);

select @cnt = count(*), @m13 = count(distinct m13) from #T where n >= @threshold;

select @cnt, @m13, @m23;

set statistics io off;
set statistics time off;

Is there a way to goad SQL Server towards reasonable execution plans by rewriting the query, instead of issuing separate queries for individual aggregates? My goal here is to understand where I led the SQL engine down a dark path and how I can write better SQL in the future.

Answer 1

On 2019 I get a plan with a batch mode hash aggregate operator that calculates the multiple count distincts side by side (I had missed that this was now possible TBH).

If I change the compat mode to 2014 I do get a worse plan.

And considerably worse with the n >= @threshold predicate as it still has the separate subtree for each aggregate but now loads all the rows matching the predicate into a common subexpression spool first. This is what is causing the high logical worktable reads (reads for these are rows not pages) - this runs in serial with a considerably higher elapsed time.

Is there a way to goad SQL Server towards reasonable execution plans by rewriting the query?

Assuming you are stuck on an older version then one workaround would be to do the following - to first collapse down to the 299 distinct pairs ("m13 and m23 are low-cardinality foreign keys") and then only spool those

WITH T
     AS (SELECT COUNT(*) AS Count,
                m13,
                m23
         FROM   #T
         where n >= 5000001
         GROUP  BY m13,
                   m23)
SELECT SUM(Count),
       count(DISTINCT m13),
       count(DISTINCT m23)
FROM   T 

The additional time added by the subtrees aggregating the 299 spooled rows is now minimal and it doesn't really matter that the plan becomes serial to the left of the spools.

Or an alternative, and potentially better, way along similar lines would be to collapse down to the 299 and then use DENSE_RANK to count the distinct values (subtracting 1 if any nulls were present as the distinct count should not include these)

WITH T1
     AS (SELECT COUNT(*) AS Count,
                m13,
                m23,
                DENSE_RANK() OVER (ORDER BY m13) m13_rnk,
                DENSE_RANK() OVER (ORDER BY m23) m23_rnk
         FROM   #T
         where n >= 5000001
         GROUP  BY m13,
                   m23)
SELECT SUM(Count),
       m13DistinctCount = MAX(m13_rnk) - MAX(IIF(m13 IS NULL,1,0)) ,
       m23DistinctCount = MAX(m23_rnk) - MAX(IIF(m23 IS NULL,1,0))
FROM T1

The plan for this now has no spools at all

Answer 2

Batch-mode aggregates are able to calculate multiple DISTINCT columns without reading the data multiple times.

To force batch-mode, the easiest way is to use a dummy clustered columnstore table

CREATE TABLE dbo.DummyCCI(dummy int);
CREATE CLUSTERED COLUMNSTORE INDEX DummyCCI ON dbo.DummyCCI;

Then you can place the following anywhere in your query

LEFT JOIN dbo.DummyCCI ON 1 = 0

The table does not appear anywhere in the query plan, it is completely elided due to the logically false condition. But it means the compiler begins to consider batch-mode aggregates, which it normally chooses as they are faster in most cases.