SQL Server 2019批量删除存储过程过慢的优化咨询

• 调整批量大小与执行间隔
  批量100-500虽比一次性删除顺畅，但CPU耗尽说明单次批量负载仍过高。建议将批量缩小至50-100，每批执行后加入短暂等待（如WAITFOR DELAY '00:00:01'），避免CPU持续满负载。同时监控CPU使用率，找到删除速度与资源占用的平衡点。

• 关闭不必要的数据库特性

  • 禁用自动统计信息更新：大量删除操作会频繁触发统计信息更新，额外消耗CPU。执行ALTER DATABASE [你的数据库名] SET AUTO_UPDATE_STATISTICS OFF;，删除完成后改回ON。
  • 禁用相关触发器：若存在针对存储过程删除的触发器，会大幅增加开销。先禁用触发器（如DISABLE TRIGGER [触发器名] ON DATABASE;），删除完成后重新启用。
  • 暂停全文索引：若数据库使用全文索引，删除对象时同步更新索引会消耗资源。执行ALTER FULLTEXT INDEX ON [关联表名] STOP POPULATION;，删除完成后重启索引更新。

• 优化删除脚本逻辑
  SSMS生成的脚本可能存在冗余，自定义脚本时按架构分批处理，使用DROP PROCEDURE IF EXISTS简化语法，减少解析开销。示例脚本：

  DECLARE @BatchSize INT = 50;
  DECLARE @SQL NVARCHAR(MAX);
  
  WHILE EXISTS (SELECT 1 FROM sys.procedures WHERE is_ms_shipped = 0)
  BEGIN
      SELECT @SQL = STRING_AGG('DROP PROCEDURE IF EXISTS ' + QUOTENAME(s.name) + '.' + QUOTENAME(p.name), ';')
      FROM (
          SELECT TOP (@BatchSize) s.name, p.name
          FROM sys.procedures p
          JOIN sys.schemas s ON p.schema_id = s.schema_id
          WHERE p.is_ms_shipped = 0
          ORDER BY p.create_date
      ) AS Batch;
  
      EXEC sp_executesql @SQL;
      WAITFOR DELAY '00:00:01'; -- 可根据CPU负载调整等待时长
  END
  

• 调整SQL Server资源配置

  • 设置CPU亲和性：4核服务器可将SQL Server绑定到2-3个核心，留1个核心给系统进程，避免CPU被完全耗尽。在SSMS中右键服务器→属性→处理器，勾选目标核心。
  • 强制单线程执行批量操作：在批量删除脚本中加入OPTION (MAXDOP 1)，避免并行执行引发的CPU竞争，结合MaxDop=2（4核服务器的合理默认值）使用。

• 优化事务日志配置

  • 切换为简单恢复模式：完整恢复模式下，大量删除会生成海量事务日志，增加IO与CPU开销。执行ALTER DATABASE [你的数据库名] SET RECOVERY SIMPLE;，删除完成后恢复原模式。
  • 临时收缩事务日志：若日志文件过大影响写入效率，执行DBCC SHRINKFILE (N'你的数据库日志文件名', 100);（调整目标大小为合理值），注意此为临时操作，不可频繁使用。

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