定位哈希聚合溢出应查sys.dm_db_session_space_usage中internal_objects_alloc_page_count,其值超10万时大概率存在内存不足导致的tempdb溢出,需结合执行计划XML确认SpillLevel或Warning标记,并检查统计信息是否过期及启用TF9410优化内存估算。

查 internal_objects_alloc_page_count 定位高占用会话
哈希聚合溢出到 tempdb 时,真正吃空间的是 internal_objects_alloc_page_count,不是用户对象。这个值在 sys.dm_db_session_space_usage 里,它反映排序、哈希、游标等内部操作分配的页数。
执行以下语句快速抓出嫌疑会话:
SELECT session_id, user_objects_alloc_page_count, internal_objects_alloc_page_count FROM sys.dm_db_session_space_usage WHERE internal_objects_alloc_page_count > 100000;
重点关注 internal_objects_alloc_page_count 显著高于 10 万的会话——这类会话大概率正在执行哈希聚合且内存不足。
- 如果结果为空,说明当前没有活跃的哈希溢出,问题可能已过去或尚未触发
- 若返回多个会话,优先排查
session_id对应的 SQL 文本和等待类型 - 注意:该 DMV 统计的是会话生命周期累计值,需结合
sys.dm_exec_requests看实时状态
结合执行计划 XML 确认 Hash Aggregate 溢出节点
光看会话占用不够,得确认是不是 Hash Match(Aggregate) 在作祟,以及它是否真的溢出了。关键不是“有没有哈希”,而是“有没有溢出”。
用下面语句把高占用会话的执行计划拉出来:
SELECT r.session_id, t.text, qp.query_plan FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) qp WHERE r.session_id IN (/* 上一步查出的 session_id */);
在返回的 XML 中搜索:<relop. match>,再检查该节点是否有以下特征:</relop.>
-
EstimateRows远小于实际行数(比如预估 100 行,实际扫描百万行) -
EstimatedAvailableMemoryKB明显低于所需(例如显示 2048KB,但聚合中间结果需 50MB) - 节点属性含
SpillLevel="1"或出现Warning="1"(表示已溢出)
没有这些标记,说明哈希还在内存中完成,不构成问题。
验证是否因统计信息过期导致估算偏差
哈希溢出常是“估算失准”的副产品。优化器以为 GROUP BY 字段只有几百个不同值,结果实际有上百万——它按小内存申请,自然撑爆。
先查相关表的统计信息最后更新时间:
SELECT name, stats_date(object_id, stats_id) AS last_updated
FROM sys.stats
WHERE object_id = OBJECT_ID('YourTableName');若 last_updated 是几个月前,或表数据量增长超 20%,就极可能是元凶。
- 强制更新:
UPDATE STATISTICS YourTableName WITH FULLSCAN, NORECOMPUTE - 避免用
WITH SAMPLE,采样易加剧偏差,尤其对高基数列 - 更新后清空计划缓存:
DBCC FREEPROCCACHE(谨慎在线上执行)
更新后重跑查询,观察执行计划中 EstimateRows 是否更贴近真实值,Hash Match(Aggregate) 节点是否消失或转为 Stream Aggregate。
临时启用 KB3167159 修复并验证效果
SQL Server 2019 已内置 KB3167159 的修复逻辑,但默认不激活。必须手动开启跟踪标志 9410 才生效——它让优化器在哈希聚合时更保守地估算内存需求。
全局启用(重启后仍有效):
DBCC TRACEON(9410, -1);
或仅对当前会话启用(更安全,适合测试):
DBCC TRACEON(9410);
启用后,再次运行原查询,对比执行计划中的 GrantedMemoryKB 和 UsedMemoryKB:
- 若
GrantedMemoryKB显著上升(比如从 4MB → 64MB),且SpillLevel消失,说明修复起效 - 若仍溢出,说明问题根源不在内存估算,可能是 tempdb 配置不合理或查询本身设计缺陷
- 注意:该标志对所有哈希操作生效,上线前务必在非生产环境压测
真正难处理的,是那些既没索引支撑、又无法改写逻辑、还必须走哈希聚合的报表查询——这时候加内存、调 tempdb 文件数、甚至拆分聚合步骤,比单纯开 traceflag 更实在。

















