子查询未加TOP或WHERE易致全表扫描并物化巨量中间结果，占用tempdb空间；应检查执行计划中Table Spool节点、补充索引与过滤条件，并用监控定位高分配会话。
子查询没加 TOP 或 WHERE 导致全表扫描生成巨量中间结果
SQL Server 在执行嵌套子查询（尤其是 IN、EXISTS、SELECT ... FROM (subquery)）时，若子查询未有效过滤，优化器可能选择物化中间结果——即把子查询结果暂存到 tempdb 的内部临时对象中。这类对象不显示在 sys.tables，但会持续占用空间，直到批处理结束或连接断开。
典型诱因包括：子查询里漏写 WHERE 条件、误用 SELECT *、关联字段无索引导致无法下推过滤。
- 用
SET STATISTICS XML ON查看执行计划，重点找Compute Scalar后紧跟Table Spool (Eager Spool)或大量Row Count高于预期的Clustered Index Scan - 对子查询手动加
TOP 10000测试是否缓解空间增长；若明显改善，说明原始子查询产出远超必要行数 - 检查子查询中所有
JOIN字段是否都有对应索引，特别是外键列和WHERE中的过滤列
tempdb 中残留的内部临时表如何定位
SQL Server 不会把子查询物化结果暴露为用户可见的临时表（如 #t），但会以 internal objects 形式登记在 sys.dm_db_session_space_usage 和 sys.dm_db_task_space_usage 中。这些对象名称形如 obj-000000000001，无法直接关联到 SQL 文本，需结合会话上下文排查。
- 运行
SELECT session_id, internal_objects_alloc_page_count, internal_objects_dealloc_page_count FROM sys.dm_db_session_space_usage WHERE internal_objects_alloc_page_count > 10000找出高分配会话 - 对目标
session_id执行SELECT text FROM sys.dm_exec_sql_text(sql_handle) FROM sys.dm_exec_requests WHERE session_id = @sid获取当前正在执行的语句 - 注意：如果语句已执行完但连接未关闭，
internal_objects_alloc_page_count可能仍不归零——这是正常行为，空间会在连接释放时回收
用 OPTION (RECOMPILE) 强制重编译可能让问题“消失”，但不是根本解法
当子查询涉及变量（如 WHERE col = @var），SQL Server 可能复用低效的缓存计划，导致本该走索引查找却选了扫描+物化。加 OPTION (RECOMPILE) 能让每次执行都基于实际参数值生成新计划，常使中间结果大幅缩小。但这只是绕过参数嗅探问题，而非修复逻辑缺陷。
- 仅在确认是参数嗅探导致计划退化时使用，不要无差别添加
- 避免在高频小查询上使用，会增加编译开销
- 更稳妥的方式是改写子查询为
JOIN，或拆成显式临时表并建索引，把控制权拿回来
监控 tempdb 分配速率比查“谁占了空间”更实用
等发现 tempdb 爆满再查，往往已错过关键窗口。子查询膨胀通常是突发性、短时性的，残留对象可能几秒内就释放。与其事后翻日志，不如提前部署轻量级监控。
- 每 5 秒采样一次
sys.dm_db_file_space_usage的user_object_pages和internal_object_pages，记录突增时段 - 配合
sys.dm_exec_query_stats查该时段内total_logical_reads或total_worker_time异常高的query_hash - 用
sys.dm_exec_query_plan(query_plan_hash)提取对应计划，聚焦RelOp节点中EstimateRows和ActualRows差异超两个数量级的子树
真正难的不是定位某条语句，而是理解为什么优化器认为物化比流式处理更优——这往往指向统计信息陈旧、数据倾斜或隐式类型转换。这些细节不会出现在错误信息里，但会决定 tempdb 是安静还是喧闹。
