含大量空nvarchar列的表插入性能缓慢，如何优化？

这个问题我之前帮不少同行排查过，临时表和正式表插入性能差这么多，核心肯定是两者的运行环境和表结构属性差异导致的——毕竟临时表默认是轻量级配置，几乎没有额外的“负担”。咱们一步步拆解问题，逐个排查优化：

1. 先查非主键索引（最常见的元凶） #

你说移除主键后性能没改善，但别忘了：除了主键的聚集索引，正式表可能还有其他非聚集索引、覆盖索引之类的。每插入一行数据，数据库都要维护所有索引的结构，百万级数据量下，这个开销会被放大几十倍。而临时表默认没有额外索引，自然快很多。

优化方案：

• 插入前先禁用所有非主键索引，插入完成后再重建索引：

  -- 禁用索引
  ALTER INDEX ALL ON [你的正式表名] DISABLE;
  -- 执行插入操作
  -- ...
  -- 重建索引
  ALTER INDEX ALL ON [你的正式表名] REBUILD;
  

• 如果某些索引不是业务必需的，直接删除可以一劳永逸。

2. 检查约束与触发器 #

正式表可能配置了外键约束、唯一约束、CHECK约束，或者有INSERT触发器（比如同步数据到其他表、记录操作日志）。这些逻辑会在每一行插入时执行验证/额外操作，对批量插入的性能影响极大，而临时表默认没有这些配置。

优化方案：

• 临时禁用约束/触发器，插入后再启用：

  -- 禁用外键约束
  ALTER TABLE [你的正式表名] NOCHECK CONSTRAINT ALL;
  -- 禁用触发器
  DISABLE TRIGGER ALL ON [你的正式表名];
  -- 执行插入操作
  -- ...
  -- 启用约束（注意要验证数据合法性）
  ALTER TABLE [你的正式表名] CHECK CONSTRAINT ALL;
  -- 启用触发器
  ENABLE TRIGGER ALL ON [你的正式表名];
  

• 如果触发器是行级逻辑，改成批量处理逻辑（比如用INSERTED表批量操作），能大幅降低开销。

3. 优化事务日志的影响 #

正式表所在的数据库如果是完整恢复模式，每一次插入都会把完整的操作记录写入事务日志，而且如果日志文件太小，会频繁触发自动增长（每次增长都会阻塞操作）。而临时表的日志存在tempdb，默认是简单恢复模式，日志开销小很多。

优化方案：

• 插入前临时切换到简单恢复模式（如果业务允许的话），插入完成后切回完整模式：

  ALTER DATABASE [你的数据库名] SET RECOVERY SIMPLE;
  -- 执行插入操作
  -- ...
  ALTER DATABASE [你的数据库名] SET RECOVERY FULL;
  

• 预先增大事务日志文件的大小，避免自动增长；把日志文件放在高速存储（比如SSD）上，提升写入速度。

4. 用批量插入的优化提示 #

默认情况下，正式表的插入可能没有启用批量优化，而临时表会自动适配。你可以给插入语句加TABLOCK提示，让数据库采用批量日志模式，减少锁的开销：

SET NOCOUNT ON; -- 减少返回的行数统计，提升性能
INSERT INTO [你的正式表名] WITH (TABLOCK)
(列1, 列2, ..., 列20)
SELECT 列1, 列2, ..., 列20 FROM [临时表或数据源];

TABLOCK会让数据库获取表级锁，避免行级锁的竞争，同时启用批量日志，大幅降低日志开销。

5. 检查表的存储配置 #

正式表的填充因子（Fill Factor）如果设置得过低，会导致索引页的空间利用率不足，插入时频繁触发页拆分；或者表所在的文件组空间不足，频繁自动扩展。而临时表默认填充因子是100%，且tempdb通常配置了足够的预分配空间。

优化方案：

• 插入前临时把填充因子改成100%，插入完成后改回原来的设置：

  ALTER INDEX ALL ON [你的正式表名] REBUILD WITH (FILLFACTOR = 100);
  -- 执行插入操作
  -- ...
  ALTER INDEX ALL ON [你的正式表名] REBUILD WITH (FILLFACTOR = 原来的数值);
  

• 预先给正式表所在的数据文件分配足够的空间，避免自动扩展的阻塞。

6. 更新表的统计信息 #

如果正式表的统计信息过时，数据库可能会生成低效的插入执行计划，而临时表是新建的，统计信息完全新鲜。

优化方案：

UPDATE STATISTICS [你的正式表名] WITH FULLSCAN;

最后总结排查顺序 #

建议按这个优先级排查：

1. 加TABLOCK提示试试（最快验证）
2. 检查是否有非主键索引、触发器、约束
3. 优化事务日志配置
4. 调整存储填充因子和文件空间

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