WHILE循环首次迭代内存不足，手动执行单条查询正常（SQL Server 2019）

问题背景 #

SQL Server 2019环境下，单条指定institution_name（比如'ABC'）的INSERT语句能正常运行，但改成WHILE循环用变量筛选时，第一次迭代就报错：

无法为数据库'TEMPDB'分配新页，因为文件组'DEFAULT'中磁盘空间不足。

手动复制多份语句修改WHERE条件执行都没问题，调整临时表数据类型也没解决问题。

核心原因：参数嗅探导致执行计划不匹配 #

手动写字符串字面量时，SQL Server能精准生成适配该值的最优执行计划——比如直接利用institution_name上的索引快速筛选数据，数据量小，TEMPDB压力自然低。但使用变量时，SQL会在循环启动前就生成执行计划，此时变量尚未赋值，它会默认生成针对全表的执行计划，直接扫描大表导致TEMPDB瞬间被撑爆。

可落地的解决方案 #

1. 强制每次迭代重新生成执行计划 #

在循环内的查询末尾添加OPTION (RECOMPILE)，让SQL Server每次迭代都根据当前变量值生成最优计划：

DECLARE @INSTITUTION varchar(10)
DECLARE @COUNTER int
SET @COUNTER = 0
DECLARE @LOOKUP table (temp_val varchar(10), temp_id int)

INSERT INTO @LOOKUP (temp_val, temp_id)
VALUES ('ABC', 1), ('DEF', 2), ('GHI', 3)

WHILE @COUNTER < 3
BEGIN
    SET @COUNTER = @COUNTER + 1

    SELECT @INSTITUTION = temp_val 
    FROM @LOOKUP 
    WHERE temp_id = @COUNTER;

    WITH my_cte AS 
    (
        SELECT [columns]
        FROM mytable a
        INNER JOIN bigtable b ON a.institution_name = b.institution_name
                              AND a.personID = b.personID
        WHERE a.institution_name = @INSTITUTION
          AND b.institution_name = @INSTITUTION
    )
    INSERT INTO results (personID, institution_name, ...)
    SELECT personID, institution_name, [some aggregations]
    FROM my_cte
    GROUP BY personID, institution_name
    OPTION (RECOMPILE); -- 强制重新编译生成适配当前变量的计划
END

2. 改用游标处理（可选） #

游标逐行处理每个机构值，默认每次迭代都会根据当前值生成合理的执行计划，避免执行计划不匹配的问题：

DECLARE @INSTITUTION varchar(10)

DECLARE institution_cursor CURSOR FOR
SELECT temp_val FROM @LOOKUP ORDER BY temp_id

OPEN institution_cursor
FETCH NEXT FROM institution_cursor INTO @INSTITUTION

WHILE @@FETCH_STATUS = 0
BEGIN
    WITH my_cte AS 
    (
        SELECT [columns]
        FROM mytable a
        INNER JOIN bigtable b ON a.institution_name = b.institution_name
                              AND a.personID = b.personID
        WHERE a.institution_name = @INSTITUTION
          AND b.institution_name = @INSTITUTION
    )
    INSERT INTO results (personID, institution_name, ...)
    SELECT personID, institution_name, [some aggregations]
    FROM my_cte
    GROUP BY personID, institution_name

    FETCH NEXT FROM institution_cursor INTO @INSTITUTION
END

CLOSE institution_cursor
DEALLOCATE institution_cursor

3. 统一数据类型避免隐式转换 #

由于mytable.institution_name是varchar(10)，bigtable.institution_name是nvarchar(10)，隐式转换可能导致索引失效。将变量和临时表字段统一改为nvarchar(10)：

DECLARE @INSTITUTION nvarchar(10) -- 同步改为nvarchar
DECLARE @LOOKUP table (temp_val nvarchar(10), temp_id int) -- 临时表字段也同步修改

额外优化建议 #

• 给mytable和bigtable创建(institution_name, personID)的复合索引，能大幅提升筛选和连接效率，减少TEMPDB的负载。
• 检查TEMPDB的文件设置，比如调整自动增长步长或直接扩容数据文件，避免后续再次出现空间不足问题。

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