resource_semaphore等待高不等于自适应join内存不足，因其本身不额外申请内存，仅动态切换已有哈希/嵌套循环策略；真正内存消耗来自编译阶段由估算行数决定的哈希联接分支。

为什么RESOURCE_SEMAPHORE等待高不等于自适应JOIN内存不足

自适应JOIN本身不额外申请内存；它只是在已有哈希/嵌套循环计划基础上，根据运行时第一输入的实际行数，动态切换联接策略。真正消耗授予内存的是哈希联接（Hash Join）分支——而该分支是否启用、申请多少内存，由优化器在编译阶段基于估算行数决定，不是自适应逻辑实时分配的。

常见误判：看到大量RESOURCE_SEMAPHORE等待，就认为是自适应JOIN“吃内存”。实际上，这往往暴露的是旧计划缓存未清、兼容性级别未升到150、或哈希分支预估严重失真（比如MSTVF返回50万行却按100行估算），导致授予内存远低于实际需求。

• 自适应JOIN只在批处理模式（Batch Mode）下生效，行模式（Row Mode）下压根不触发，此时你看到的永远是Nested Loops，也根本不会申请哈希内存
• 必须满足内侧估算行数与实际偏差 > 2 倍且总行数 > 10000，才可能激活自适应切换逻辑
• 即使切换发生，内存已在初始计划编译时“锁定”，运行时不会重新申请

如何确认执行计划里真有自适应JOIN及其内存来源

打开SET STATISTICS XML ON，执行含CROSS APPLY或OUTER APPLY的子查询语句，然后在XML执行计划中搜索Adaptive节点。注意：它一定依附于一个Hash Match运算符，且该Hash Match的EstimatedRows和ActualRows差异巨大（比如估算100，实际20万）。

授予内存大小藏在RelOp节点的EstimateRows和MemoryFractions属性里，但更直接的方式是查sys.dm_exec_query_stats结合sys.dm_exec_sql_text：

SELECT 
  qs.total_grant_kb,
  qs.last_grant_kb,
  qs.min_grant_kb,
  qs.max_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 '%CROSS APPLY%';

重点看last_grant_kb——这才是该计划最后一次执行时真实拿到的授予内存（KB单位）。

兼容性级别和QUERY_STORE是硬门槛，不是可选项

ALTER DATABASE [your_db] SET COMPATIBILITY_LEVEL = 150必须显式执行，实例级默认值无效。改完后不执行DBCC FREEPROCCACHE，旧计划仍复用，你永远看不到自适应行为。

交错执行（Interleaved Execution）对MSTVF子查询至关重要，但它依赖QUERY_STORE = ON才能收集首次执行的真实行数反馈。没开QUERY_STORE，MSTVF还是按100行硬编码估算，外层JOIN计划照样崩。

• 检查QUERY_STORE状态：SELECT actual_state_desc FROM sys.database_query_store_options
• 启用它：ALTER DATABASE [your_db] SET QUERY_STORE = ON
• 列存储索引或trace flag 11256是启用批处理模式的前提，没有它们，自适应JOIN就是摆设

最常被忽略的细节：内存授予是“计划级”而非“JOIN级”

SQL Server给整个执行计划申请一次授予内存，再按各运算符权重分发。一个计划里如果有排序+哈希连接+多个APPLY，total_grant_kb是它们共享的池子。你调大某个JOIN的预估行数，可能反而挤占了排序所需内存，导致spill到tempdb——这时RESOURCE_SEMAPHORE没涨，PAGEIOLATCH_SH却飙升。

所以别盯着单个JOIN调内存，要整体看计划里所有内存消费者（Sort、Hash Match、Window Spool）。用sys.dm_exec_query_plan_stats（需启用QUERY_PLAN_STATS数据库范围配置）对比estimated_rows和actual_rows，找到那个偏差最大的节点，它才是内存误估的根源。