根本原因是临时表默认位于tempdb磁盘且无索引,导致I/O高、逻辑读飙升;SQL Server对本地临时表索引创建有隐式限制,需用GO分隔批处理或改用内存优化表变量。

为什么OUTPUT INTO临时表会变慢?
根本原因不是OUTPUT INTO本身,而是临时表默认落在tempdb磁盘上,且未建索引。当INSERT/UPDATE量大、或后续要多次JOIN/SELECT该临时表时,I/O和缺少索引会导致逻辑读飙升——尤其在高并发场景下,tempdb争用会进一步放大延迟。
必须加索引,但不能在CREATE TABLE后立刻建
SQL Server对本地临时表(#temp)的索引创建有隐式限制:如果在同一个批处理中先CREATE TABLE #temp,再CREATE INDEX,部分版本(如SQL Server 2016及更早)可能无法生效,或导致执行计划不重编译,索引被忽略。
实操建议:
- 把临时表创建和索引定义拆到独立的存储过程或显式批处理中(用
GO分隔) - 优先使用
CREATE TABLE #temp (...) WITH (DATA_COMPRESSION = PAGE)减少页数(若数据重复率高) - 对常用于JOIN或WHERE的字段,建非聚集索引;若只按插入顺序扫描,可省略索引
- 避免在
#temp上建主键约束——它会强制创建唯一聚集索引,开销大且未必需要
改用内存优化表变量替代#temp(SQL Server 2014+)
当临时数据量可控(通常MEMORY_OPTIMIZED表变量比#temp快一个数量级,因为它绕过tempdb、无锁、无日志。
注意前提和写法:
- 数据库必须启用内存优化功能:
ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_ACCELERATED_PLAN = ON - 不能直接
DECLARE @t TABLE,必须先建类型:CREATE TYPE dbo.tvp_output AS TABLE(... INDEX ix_hash HASH (id) WITH (BUCKET_COUNT = 100000)) WITH (MEMORY_OPTIMIZED = ON) - 声明变量:
DECLARE @output_data dbo.tvp_output -
OUTPUT INTO @output_data即可,无需INTO #temp
关键点:哈希索引的BUCKET_COUNT应设为预期唯一键数量的1.5–2倍;设太高浪费内存,太低引发哈希冲突降速。
OUTPUT INTO后立即DROP,别等会话结束
很多人以为“本地临时表会自动清理”,就放任不管。但#temp实际占用tempdb空间直到批处理结束——若中间还执行了大量其他语句,tempdb空间不会及时释放,可能触发tempdb增长或争用。
正确做法:
- 在
OUTPUT INTO #temp之后、且不再需要它时,立刻执行DROP TABLE #temp - 若后续还需查一次,也建议查完立刻
DROP,而不是留到存储过程末尾 - 避免在长事务中持有临时表——它会延长
tempdb事务日志保留时间
最易被忽略的是:嵌套存储过程中,父过程创建的#temp在子过程里仍可见,但子过程里的DROP不会影响父过程作用域;这种跨层引用容易导致意外残留。


















