如何在SQL Server中定位Hash JOIN引发的内存等待LATCH？-SQL-PHP中文网

pageiolatch_*和hash warning等待升高通常由hash join内存不足导致tempdb溢出引发，需结合reserved_memory_allocation_ext、tempdb分配闩锁及执行计划中warning="1"等多维度交叉验证。

如何在sql server中定位hash join引发的内存等待latch？

查 `sys.dm_os_wait_stats` 里 PAGEIOLATCH_* 和 HASH Warning 相关等待

Hash JOIN 大量消耗内存时，若工作区不足，SQL Server 会把中间哈希表溢出到 tempdb，触发物理 I/O——这直接表现为 PAGEIOLATCH_SH 或 PAGEIOLATCH_EX 等待飙升。但注意：这些等待本身不说明是 Hash JOIN 引起的，得结合上下文交叉验证。

优先运行以下查询，重点关注过去一小时累计值（避免重启后清零干扰）：

SELECT wait_type, waiting_tasks_count, wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type IN ('PAGEIOLATCH_SH', 'PAGEIOLATCH_EX', 'SOS_SCHEDULER_YIELD', 'RESERVED_MEMORY_ALLOCATION_EXT')
ORDER BY wait_time_ms DESC;

如果 RESERVED_MEMORY_ALLOCATION_EXT 高，说明大量线程在争抢查询执行内存；若同时 PAGEIOLATCH_* 占比超 15%，基本可锁定是哈希溢出导致的 I/O 压力。别只看单次快照，连续采样 3–5 分钟再对比趋势。

用 `sys.dm_exec_query_stats` + `sys.dm_exec_sql_text` 找出高内存授予且带 Hash Match 的语句

Hash JOIN 的内存压力源头一定是某条 SQL。关键不是看执行次数，而是看它申请了多少内存、实际用了多少、有没有溢出警告。

执行以下语句，过滤掉 trivial 执行计划，聚焦 Hash Match 运算符：

SELECT 
  qs.sql_handle,
  qs.total_grant_kb,
  qs.total_used_grant_kb,
  qs.total_ideal_grant_kb,
  st.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.text LIKE '%HASH%' 
  AND qs.total_grant_kb > 102400 -- 超 100MB 授予才值得深挖
  AND qs.last_execution_time > DATEADD(minute, -10, GETDATE())
ORDER BY qs.total_grant_kb DESC;

拿到 sql_handle 后，用 sys.dm_exec_query_plan 提取执行计划 XML，搜索 <relop. match>，确认是否出现 <code>Warning="1"（表示已溢出）或 GrantedMemory="..." UsedMemory="..." 差值巨大。

曦灵数字人

曦灵数字人是一款AI文本写作工具，百度推出的AI数字人和视频创作平台。

下载

监控 tempdb 文件 I/O 和分配页争用，确认 Hash 溢出路径是否成瓶颈

Hash JOIN 溢出后写入 tempdb，所有线程都往同一文件、同一 SGAM/PFS 页面分配空间，极易引发 PAGELATCH_UP（如 PFS 页面）或 PAGELATCH_EX（如 GAM 页面）争用——这才是真正的“内存等待 LATCH”根因，不是缓冲池闩锁，而是分配结构闩锁。

检查 tempdb 数据文件分布和争用：

确保 tempdb 至少有 4–8 个等大小数据文件（不要 1 个），文件路径分散到不同物理磁盘
运行 DBCC SQLPERF('sys.dm_os_wait_stats') 查 PAGELATCH_UP 是否集中在 tempdb 的 2:1:1（PFS）、2:1:2（GAM）、2:1:3（SGAM）
用 sys.dm_db_file_space_usage 看 user_object_reserved_page_count 是否在高峰时段突增

如果 PAGELATCH_UP 等待对象是 tempdb 的 PFS 页面，且 waiting_tasks_count 持续 > 1000/分钟，基本可断定是 Hash JOIN 溢出引发的分配闩锁风暴。

为什么不能只靠 Execution Plan 中的 “Warning” 判断？

执行计划里的 Warning="1" 只代表编译时预估内存不足，但运行时是否真溢出，取决于当时可用内存、并行度、其他查询竞争——所以计划里没 Warning，不代表没溢出；有 Warning，也不代表一定发生了 I/O。

更可靠的方式是组合验证：

运行时看 sys.dm_exec_query_memory_grants 中 grant_time 和 used_memory_kb 是否远小于 requested_memory_kb
查 sys.dm_io_virtual_file_stats 确认 tempdb 数据文件的 io_stall_read_ms 和 num_of_reads 是否同步激增
用 sys.dm_exec_session_wait_stats 按会话查，锁定具体 session_id 的 PAGEIOLATCH_* 和 RESERVED_MEMORY_ALLOCATION_EXT 累计值