SQL Server存储过程与直接执行的性能差异问题排查求助

可能的原因及对应解决方法 #

• 统一存储过程与脚本的SET选项
  存储过程编译时会绑定创建/首次执行时的会话SET选项，而直接运行脚本时的SET配置可能不同（比如SET ANSI_NULLS、SET QUOTED_IDENTIFIER这类关键选项），这会导致执行计划出现巨大差异。直接在存储过程开头显式设置和脚本完全一致的SET选项：

  SET ANSI_NULLS ON;
  SET QUOTED_IDENTIFIER ON;
  -- 补充其他和你执行脚本时一致的SET配置
  

• 拆分大批量操作为小批次执行
  40000条插入/更新放在单个存储过程里，会导致事务日志负载过高，且优化器难以生成高效的执行计划。可以按批次拆分操作，比如每1000条作为一个处理单元，降低单次事务的压力：

  DECLARE @BatchSize INT = 1000;
  DECLARE @CurrentBatchStart INT = 1;
  
  WHILE @CurrentBatchStart <= 40000
  BEGIN
      -- 在此处编写对应批次的插入/更新逻辑
      SET @CurrentBatchStart += @BatchSize;
  END
  

• 强制存储过程每次执行重新编译
  即使无参数，SQL Server仍可能复用旧的执行计划。可以在创建存储过程时指定WITH RECOMPILE，确保每次执行都生成适配当前环境的计划：

  CREATE PROCEDURE dbo.YourStaticDataUpdateProc
  WITH RECOMPILE
  AS
  -- 原存储过程的40000条插入/更新操作
  GO
  

  或者调用时临时指定：

  EXEC dbo.YourStaticDataUpdateProc WITH RECOMPILE;
  

• 检查事务日志的性能瓶颈
  存储过程执行时的日志写入延迟可能是关键问题。确认日志文件有足够的预分配空间，避免频繁自动增长；同时可以将单一大事务拆分为多个小事务，减少日志的一次性写入压力。

• 验证存储过程的编译上下文
  检查数据库兼容性级别、是否存在隐式临时表/表变量等差异。比如存储过程编译时的兼容性级别和直接运行脚本时不同，也会导致执行计划偏差，可将存储过程的兼容性设置与数据库保持一致。

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