多聚合查询时SQL Server Worktable逻辑读取量异常过高问题排查

当从数百万行数据中查询多个聚合值时，SQL Server性能远低于PostgreSQL，无论是相同查询还是拆分单个聚合的查询，性能差距达数个数量级。通过STATISTICS IO分析发现，Worktable的逻辑读取量极高：处理5000万行数据时（源表仅读取92917页），Worktable逻辑读取量高达1.57亿次，该问题在SQL Server 2008R2至2019各版本中均存在。

复现问题的最简查询 #

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

其中m13和m23为低基数外键，可通过以下脚本模拟数据（当前配置处理1000万行数据的50%）：

数据模拟脚本 #

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

需求 #

寻求无需拆分单个聚合查询，仅通过改写查询促使SQL Server生成合理执行计划的方法，同时希望了解问题根源以优化后续SQL编写。

问题根源 #

SQL Server在处理多个COUNT(DISTINCT)聚合时，默认执行计划会采用嵌套流聚合逻辑：先为第一个COUNT(DISTINCT)构建哈希表去重，再基于该中间结果处理第二个COUNT(DISTINCT)，导致Worktable需要反复读写中间数据。即使是低基数列场景，这种嵌套处理也会产生大量不必要的逻辑读取。而PostgreSQL会采用更高效的单次扫描或并行聚合处理多个去重统计，避免了额外的Worktable开销。

查询改写方案 #

以下几种改写方式可促使SQL Server生成更优执行计划，降低Worktable读取量：

方案1：窗口函数结合聚合 #

SELECT
    MAX(row_count) AS total_count,
    COUNT(DISTINCT m13) AS distinct_m13,
    COUNT(DISTINCT m23) AS distinct_m23
FROM (
    SELECT
        COUNT(*) OVER () AS row_count,
        m13,
        m23
    FROM #T
    WHERE n >= @threshold
) AS sub;

该改写让SQL Server先扫描一次源表获取所有必要数据，再在子查询结果上完成聚合，避免嵌套流聚合带来的Worktable开销。

方案2：CTE预扫描数据 #

WITH pre_scan AS (
    SELECT m13, m23 FROM #T WHERE n >= @threshold
)
SELECT
    (SELECT COUNT(*) FROM pre_scan) AS total_count,
    COUNT(DISTINCT m13) AS distinct_m13,
    COUNT(DISTINCT m23) AS distinct_m23
FROM pre_scan;

通过CTE统一源表扫描逻辑，查询优化器通常会选择单次扫描源表，再分别计算各个聚合值，减少Worktable的使用。

方案3：使用近似去重统计（SQL Server 2019+） #

如果业务允许近似值，SQL Server 2019及以上版本支持APPROX_COUNT_DISTINCT函数，通过哈希算法高效计算去重计数，大幅减少Worktable读写：

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

方案4：强制哈希聚合 #

通过查询提示强制SQL Server使用哈希聚合而非流聚合，避免嵌套处理带来的额外开销：

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

内容的提问来源于stack exchange，提问作者DarthGizka