基于游标分批删除数据的T-SQL存储过程优化咨询

问题场景 #

需要清理[staging].[test].[p_location]表中100-200万条历史数据，筛选条件为JRC_POLICY_TERM_DT < '1995-01-01'且jrc_policy_status = 'T'。该表是7*24小时运行的核心业务表，要求清理操作不能影响业务的毫秒级响应，绝对不能出现全表锁或长时间锁表的情况。

原尝试的两种方案均存在缺陷：

1. 游标逐条删除：锁粒度虽小，但效率极低，清理百万级数据耗时过长
2. 直接DELETE TOP(1000)：速度快，但会触发全表锁定，直接阻塞核心业务

优化后的分批删除存储过程 #

ALTER PROCEDURE [schema].[purge_data] 
    @batch_size INT = 1000,  -- 每批次删除行数，可根据实际负载调整
    @max_delete_count INT,   -- 计划删除的总行数上限
    @delay_ms INT = 500      -- 每批次删除后的延迟毫秒数，预留资源给核心业务
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;

-- 可选：若数据库已启用READ_COMMITTED_SNAPSHOT，可进一步降低锁竞争
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

DECLARE @deleted_count INT = 0;
DECLARE @total_deleted INT = 0;

WHILE @total_deleted < @max_delete_count
BEGIN
    BEGIN TRANSACTION;

    DELETE TOP (@batch_size)
    FROM [staging].[test].[p_location]
    WHERE JRC_POLICY_TERM_DT < CAST('19950101 00:00:00.000' AS DATETIME)
      AND jrc_policy_status = 'T';

    SET @deleted_count = @@ROWCOUNT;
    SET @total_deleted += @deleted_count;

    COMMIT TRANSACTION;

    -- 无数据可删时提前终止循环
    IF @deleted_count = 0
        BREAK;

    -- 每批次后加入短延迟，避免资源被清理操作独占
    WAITFOR DELAY '00:00:00.' + RIGHT('000' + CAST(@delay_ms AS VARCHAR(3)), 3);
END

PRINT '数据清理完成，共删除 ' + CAST(@total_deleted AS VARCHAR(10)) + ' 条记录';
GO

关键优化说明 #

• 批量删除替代游标：每批次固定删除N条（默认1000），既保证清理效率，又缩短锁的持有时间，不会阻塞核心业务
• 小事务控制：每批次用独立事务，避免大事务引发的日志膨胀和长时间锁占用
• 动态资源预留：通过@delay_ms参数设置延迟，让数据库资源优先分配给核心业务，高峰期可适当延长延迟
• 索引优化：必须给筛选字段创建组合索引，避免全表扫描，缩小锁的范围

  CREATE NONCLUSTERED INDEX IX_p_location_Purge ON [staging].[test].[p_location]
  (jrc_policy_status, JRC_POLICY_TERM_DT)
  INCLUDE (JRC_policy_number, jrc_part_range_nbr); -- 包含删除所需列，避免键查找
  

• 隔离级别适配：若数据库允许，启用READ_COMMITTED_SNAPSHOT选项，让读操作不受写锁阻塞，进一步降低业务影响
• 参数动态调整：根据生产负载灵活调整@batch_size和@delay_ms——低峰期调大批次、缩短延迟，高峰期调小批次、延长延迟

注意事项 #

• 优先在业务低峰期执行清理操作
• 执行前务必备份目标数据，避免误删
• 实时监控SQL Server的锁等待、CPU及IO使用率，根据实际情况调整参数

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