为什么SQL中 INSERT INTO SELECT会导致TempDB空间占满？

INSERT INTO SELECT 本身不直接占满TempDB，但会因目标表聚集索引、源数据无序、快照隔离或触发器等隐式触发排序、哈希及版本存储，导致TempDB空间激增。

INSERT INTO SELECT 本身不直接“占满” TempDB，但它触发的底层操作（排序、哈希、版本存储）会大量申请 TempDB 空间——尤其当目标表有索引、源数据未排序、或事务开启快照隔离时。

为什么 INSERT INTO SELECT 会隐式触发排序和哈希？

SQL Server 在执行 INSERT INTO SELECT 时，若目标表存在聚集索引且源数据未按该索引键排序，引擎必须先对输入流排序（即使你没写 ORDER BY），否则无法保证物理插入顺序。这个排序过程发生在 TempDB 中。

• 常见触发场景：INSERT INTO orders_with_idx (id, amount) SELECT id, amount FROM staging_orders，而 orders_with_idx.id 是聚集主键，但 staging_orders 没有索引或乱序
• 用 SET STATISTICS XML ON 查执行计划，看到 <RelOp NodeId="X" PhysicalOp="Sort"> 或 SpillLevel="1" 就是证据
• 如果目标表还有非聚集索引，SQL Server 还会为每个索引维护单独的排序/合并路径，进一步放大 TempDB 压力

触发器或快照隔离让 INSERT INTO SELECT 变成“空间黑洞”

哪怕语句本身简单，只要所在事务启用了 READ COMMITTED SNAPSHOT 或 SNAPSHOT 隔离级别，或者目标表上有 AFTER INSERT 触发器，SQL Server 就必须为每一行维护行版本 —— 所有版本数据都存进 TempDB 的 version store。

• 查当前最大版本事务：SELECT session_id, transaction_id, elapsed_time_seconds, max_version_chain_traversed FROM sys.dm_tran_active_snapshot_database_transactions
• 如果 max_version_chain_traversed > 100，说明某条插入正在拖着大量旧版本不释放
• 触发器内再嵌套查询（比如日志记录查用户表），会二次放大版本链和内部对象分配

临时表 + INSERT INTO SELECT 是最危险组合

很多人用 SELECT INTO #tmp 或 INSERT INTO #tmp SELECT ... 做中间聚合，却忘了临时表默认无索引 —— 后续所有 JOIN / WHERE / ORDER BY 都被迫走全表扫描 + 新一轮 TempDB 排序/哈希。

• 错例：SELECT * INTO #staging FROM big_table WHERE flag = 1 → 后续 JOIN 时爆 TempDB
• 正解：显式建表 + 立即建索引：CREATE TABLE #staging (id INT, status VARCHAR(20)); CREATE CLUSTERED INDEX IX_staging_id ON #staging (id);
• 别依赖 SELECT INTO 的“自动建表”便利性，它等于主动放弃索引控制权

怎么快速定位是不是这条语句在撑爆 TempDB？

别猜，直接查实时分配源头。重点看 user_objects_alloc_page_count 和 internal_objects_alloc_page_count 是否集中在某个 session 上。

• 运行这句找罪魁：SELECT su.session_id, es.login_name, es.host_name, es.program_name, SUM(su.user_objects_alloc_page_count + su.internal_objects_alloc_page_count) * 8 / 1024 AS MB_used FROM sys.dm_db_session_space_usage su JOIN sys.dm_exec_sessions es ON su.session_id = es.session_id GROUP BY su.session_id, es.login_name, es.host_name, es.program_name ORDER BY MB_used DESC
• 拿到 session_id 后，用 DBCC INPUTBUFFER(<sid>) 看它正在执行什么语句
• 如果发现是 INSERT INTO ... SELECT，再结合执行计划确认是否含 Sort / Hash Match / Version Store 节点

真正卡住人的不是语法本身，而是“没索引的临时表 + 未排序的大结果集 + 快照隔离”三者叠加——这时候加再多磁盘空间也救不了，得从数据流路径上拆解。