自适应join是sql server 2022优化器运行时动态切换hash join与nested loops join的机制，因预估偏差、右表无索引或内存溢出导致实际性能下降。
什么是自适应JOIN，它为什么会让查询变慢？
SQL Server 2022 的自适应 JOIN（Adaptive Join）不是手动写的语法，而是查询优化器在运行时动态决定是否从 Hash Join 切换为 Nested Loops Join 的机制。它依赖于“阈值行数”做判断——如果左输入实际行数 ≤ 阈值，就改用 Nested Loops；否则维持 Hash Join。这个切换本身不耗时，但抖动来自：预估行数严重偏差导致选错路径、切换后内存分配失败触发重编译、或 Nested Loops 在右表无索引时退化成嵌套扫描。
怎么确认当前查询真的触发了自适应JOIN？
不能只看执行计划里有没有 Adaptive Join 算子图标——那只是“启用了该功能”，不代表本次执行真发生了切换。要看真实行为：
- 启用实际执行计划（
SET STATISTICS XML ON），执行后在 XML 中搜索<relop logicalop="Adaptive Join"></relop>，再检查其子节点是否有<adaptivethresholdrows></adaptivethresholdrows>和<estimatedjointype></estimatedjointype>（初始预估类型）与<actualjointype></actualjointype>（最终采用类型） - 查
sys.dm_exec_query_stats+sys.dm_exec_sql_text，过滤出含adaptive字样的query_plan，再用XML.value()提取ActualJoinType是否在多次执行中来回变化 - 注意：SSMS 19+ 的图形化执行计划里，自适应算子右上角会标
[Switched]或[Not Switched]，但这个标记只对当前执行有效，无法回溯历史
哪些场景下自适应JOIN大概率翻车？
它不是万能的，三类典型失效点必须人工干预：
-
WHERE条件含非 SARGable 表达式（如YEAR(OrderDate) = 2023、UPPER(name) = 'ABC'），导致左输入行数预估完全失真，自适应阈值失去意义 - 右表连接字段缺失索引，且实际数据量大 → 即使切换到
Nested Loops，每次外层循环都要全表扫右表，性能比 Hash Join 更差 - 查询涉及
tempdb大量排序/哈希（如多列GROUP BY+ORDER BY），而Adaptive Join的内存授予是按预估做的，一旦实际需要更多内存，就会 spill 到磁盘，引发 I/O 毛刺
如何临时禁用或绕过自适应JOIN？
不推荐全局关掉（ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_OPTIMIZER_ROWGOAL = ON 是错的，那是另一个功能），应精准控制：
- 加查询提示：
OPTION (USE HINT('DISABLE_BATCH_MODE_ADAPTIVE_JOINS'))—— 这是最干净的方式，只影响当前语句 - 强制指定连接算法：
INNER LOOP JOIN或INNER HASH JOIN，但需确保你清楚代价模型是否适用 - 更新统计信息并加
WITH FULLSCAN，尤其当表有倾斜数据分布（如 95% 的订单属于 5% 的用户）时，直方图桶不够会导致预估崩坏
真正棘手的是：自适应本身是“诊断友好型”机制，但它把问题从“执行计划固化错误”变成了“同一语句不同时间走不同路径”。排查时最容易忽略的，是没对比过 ActualJoinType 变化的时间点与业务流量峰谷、统计信息自动更新窗口是否重合。
