左外连接查询中临时表#GameIds比表变量@GameIds多出全表扫描问题

你遇到的性能问题本质是临时表的统计信息特性、执行计划缓存机制和你的业务场景适配不当导致，核心忽略的要点如下：

• 临时表元数据与索引定义不合理
  你当前仅为#GameIds的Id字段创建了非聚集索引，既没有明确标记Id的唯一性，也没有使用聚集索引。如果传入的GameId是唯一值，优化器无法从现有索引定义获取「Id不重复」的元数据信息，会出现基数估计偏差；同时非聚集索引在仅查询Id的场景下虽然是覆盖索引，但性能弱于聚集主键索引。
  建议修改建表语句为：

CREATE TABLE #GameIds
(
  Id INT PRIMARY KEY CLUSTERED
)

无需额外创建非聚集索引，即可为优化器提供更准确的基数估计依据。

• 查询条件未同步替换
  你当前贴出的SELECT语句中WHERE子句的EXISTS查询仍然使用原表变量@GameIds，如果实际替换后没有同步修改为#GameIds，会导致临时表完全没有被查询使用，还额外增加了写入临时表的无谓开销。请确认WHERE子句已同步修改为：

WHERE EXISTS (SELECT * FROM #GameIds WHERE Id = divisionGameTeamResult.GameId)

• 执行计划缓存重用导致适配错误
  临时表支持执行计划缓存，如果存储过程第一次执行时传入的@GameIds行数极少，会生成适配小数据量的执行计划；后续传入大数据量时会直接复用该缓存计划，反之亦然，都会出现计划与实际数据规模不匹配的问题，进而触发全表扫描、内存授予偏差。
  建议在最终的SELECT语句末尾添加重编译提示，每次执行时根据当前#GameIds的真实数据量生成最优计划：

SELECT ... -- 你的原有查询逻辑
OPTION (RECOMPILE)

• 连接算法选择阈值不匹配
  你原来使用表变量时，SQL Server（2019及以下版本默认配置）对表变量的基数估计固定为1行，因此一定会选择嵌套循环连接，走内层表的聚集索引查找，刚好匹配你的业务数据场景。而临时表会根据真实行数选择连接算法，如果#GameIds的行数刚好达到优化器切换为哈希连接/合并连接的阈值，就会出现大表全表扫描的情况，反而性能劣化。
  如果确认你的场景下#GameIds行数始终较少，嵌套循环连接性能更优，可以强制查询使用嵌套循环：

SELECT ... -- 你的原有查询逻辑
OPTION (LOOP JOIN)

• 统计信息更新不及时
  临时表的统计信息自动更新阈值为「行数变化≥20% 或 行数≥500行时变化≥500行」，如果每次传入的@GameIds行数波动极大，统计信息未及时更新会导致基数估计严重偏差，进而触发内存授予警告、执行计划错误。可以在插入完#GameIds数据后手动更新统计信息：

INSERT INTO #GameIds SELECT Id FROM @GameIds
UPDATE STATISTICS #GameIds

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