根本原因是SQL Server执行深度嵌套子查询时将中间结果物化到tempdb，尤其在多次引用、含聚合/排序或统计信息滞后时反复占用空间。应改用命名CTE+OPTION(RECOMPILE)、临时表建索引或JOIN替代IN/子查询。
为什么嵌套子查询会撑爆 tempdb？
根本原因是 SQL Server 在执行深度嵌套的子查询（尤其是含 GROUP BY、ORDER BY、窗口函数或重复引用同一子查询）时，常将中间结果物化到 tempdb。如果外层查询反复引用未命名的子查询（如多个 (SELECT ...) 出现在同一语句中），优化器可能为每次引用都生成独立的临时结果集，导致 tempdb 空间被多次占用且无法及时释放。
典型触发场景包括：报表类查询中多层 ROW_NUMBER() OVER (...) IN (SELECT ...) 套用、CTE 未加 OPTION (RECOMPILE) 且被多次引用、或子查询返回大量行后又被外层做 JOIN 或聚合。
用 CTE + 显式命名 + OPTION (RECOMPILE) 控制物化时机
CTE 本身不保证物化，但 SQL Server 在某些情况下（如被多次引用、含排序/聚合）会自动将其写入 tempdb。关键在于主动引导优化器——用带明确别名的 CTE 替代匿名子查询，并在必要时加 OPTION (RECOMPILE) 让优化器基于实际参数值重估行数，避免因统计信息滞后导致错误选择“先全量物化再过滤”的低效计划。
- 把反复出现的子查询提取为命名 CTE，例如：
WITH base_data AS ( SELECT id, value, category FROM dbo.orders WHERE order_date >= '2026-01-01' ) SELECT * FROM base_data b1 WHERE b1.id IN (SELECT id FROM base_data b2 WHERE b2.category = 'A');
- 若该 CTE 实际只被引用一次，但执行计划仍显示大量
WorktableI/O，尝试在语句末尾加OPTION (RECOMPILE) - 避免在 CTE 内部使用
SELECT *，只选真正需要的列；宽表物化开销呈线性增长
用临时表替代深层嵌套，显式控制生命周期
当嵌套超过 3 层，或子查询结果集稳定（比如固定筛选条件下的维度聚合），临时表比 CTE 更可控：你能加索引、分批插入、显式 DROP，且优化器对 #temp 表的统计信息更准确。
- 用
SELECT ... INTO #filtered_orders替代最内层耗资源的子查询 - 立刻在 #temp 表上建索引：
CREATE INDEX IX_#filtered_orders_category ON #filtered_orders(category);
- 后续所有 JOIN / WHERE 都基于 #temp 表，避免重复计算原表
- 记得在存储过程末尾执行
DROP TABLE #filtered_orders（尽管作用域结束会自动清理，但显式释放更稳妥）
检查并拦截高风险模式：SELECT + 子查询出现在 WHERE 或 SELECT 列表
这类写法最容易引发隐式物化，尤其当子查询含聚合或 TOP/N，SQL Server 往往无法下推谓词，被迫先算完子查询再关联。
- 禁止写类似：
SELECT name, (SELECT COUNT(*) FROM logs l WHERE l.user_id = u.id) AS log_count FROM users u—— 改用LEFT JOIN+GROUP BY - 把
WHERE col IN (SELECT ...)改成EXISTS，特别是子查询无聚合时：WHERE EXISTS (SELECT 1 FROM ref r WHERE r.id = u.ref_id) - 监控执行计划中是否出现
Table Spool (Eager Spool)或大量Worktable扫描 —— 这是 tempdb 暴涨的直接信号
真正难处理的不是嵌套本身，而是嵌套与参数嗅探、过时统计信息、缺失索引三者叠加；哪怕改用临时表，如果没在关键字段建索引，照样会把 tempdb 写满。上线前务必用真实数据量 + 实际参数跑 SET STATISTICS IO ON 看 tempdb 的逻辑读和页分配数。
