SQL Server大表批量迁移插入过慢且未达设定批次量求助

我们有一张写入频繁的表，其INT类型Identity主键即将超出取值范围。我编写脚本将记录批量插入到结构完全一致但主键为BIGINT类型的新表中，新表仅保留主键作为唯一索引/约束，用于识别待迁移记录。

当前脚本运行速度极慢，每次查看sp_who2时进程均处于挂起状态，存在CXPACKET或CXCONSUMER等待。脚本中的WAITFOR用于让其他进程在批次间隙提交事务。经计算，每分钟仅能迁移9k-13k条记录（设定批次为50k），目前已迁移2.5亿条，待迁移总量为26亿且仍在增长。幸运的是，按原表写入速度，我们还有数月缓冲期，但迁移进度偏紧。我已尝试调整批次大小和MAXDOP，但速度无改善。

当前迁移脚本 #

DECLARE @RowsMoved INT = 1
        , @IsOkToRun BIT = 1


WHILE @IsOkToRun = 1 AND @RowsMoved > 0
    BEGIN
        
         WAITFOR DELAY '00:00:00.125'

        SET IDENTITY_INSERT dbo.NEWTrackingColumnChange ON

        BEGIN TRAN

        INSERT INTO dbo.NEWTrackingColumnChange(TrackingColumnChangeId
                                              , TableName
                                              , ColumnName
                                              , SessionId
                                              , StatementId
                                              , PrimaryKey
                                              , OldValue
                                              , NewValue
                                              , ContextReferenceId
                                              , ContextReferenceType
                                              , CreatorId
                                              , CreateDate
                                              )
        SELECT TOP 50000 TCC.TrackingColumnChangeId
                      , TCC.TableName
                      , TCC.ColumnName
                      , TCC.SessionId
                      , TCC.StatementId
                      , TCC.PrimaryKey
                      , TCC.OldValue
                      , TCC.NewValue
                      , TCC.ContextReferenceId
                      , TCC.ContextReferenceType
                      , TCC.CreatorId
                      , TCC.CreateDate
        FROM dbo.TrackingColumnChange AS TCC WITH (NOLOCK)
        LEFT JOIN dbo.NEWTrackingColumnChange AS NTCC ON NTCC.TrackingColumnChangeId = TCC.TrackingColumnChangeId
        WHERE NTCC.TrackingColumnChangeId IS NULL

        SET @RowsMoved = @@ROWCOUNT

        COMMIT TRAN

        SET IDENTITY_INSERT dbo.NEWTrackingColumnChange OFF

        SELECT @IsOkToRun = CAST(PropertyValue AS BIT)
        FROM dbo.ConfigSettings 
        WHERE PropertyName = 'TCCTableMoveEnabled'

    END

查询计划核心问题：左连接新表判断未迁移记录的逻辑，随着新表数据量增大，匹配开销指数级上升，加上并行执行引发CXPACKET/CXCONSUMER等待，导致迁移效率极低。

优化方案 #

1. 改用范围扫描替代左连接判断未迁移记录 #

原脚本的左连接匹配逻辑会随着新表数据增长越来越慢，换成基于主键范围的增量迁移，利用原表主键的有序性实现高效扫描：

• 用变量记录已迁移的最大TrackingColumnChangeId
• 每次仅迁移原表中大于该值的批次数据
• 迁移后更新最大ID，实现持续增量迁移

修改后的核心逻辑示例：

DECLARE @RowsMoved INT = 1
        , @IsOkToRun BIT = 1
        , @LastMigratedId BIGINT = 0 -- 首次运行设为0，后续改为已迁移的最大ID

WHILE @IsOkToRun = 1 AND @RowsMoved > 0
    BEGIN
        WAITFOR DELAY '00:00:00.125'

        SET IDENTITY_INSERT dbo.NEWTrackingColumnChange ON

        BEGIN TRAN

        INSERT INTO dbo.NEWTrackingColumnChange(TrackingColumnChangeId
                                              , TableName
                                              , ColumnName
                                              , SessionId
                                              , StatementId
                                              , PrimaryKey
                                              , OldValue
                                              , NewValue
                                              , ContextReferenceId
                                              , ContextReferenceType
                                              , CreatorId
                                              , CreateDate
                                              )
        SELECT TOP 50000 TCC.TrackingColumnChangeId
                      , TCC.TableName
                      , TCC.ColumnName
                      , TCC.SessionId
                      , TCC.StatementId
                      , TCC.PrimaryKey
                      , TCC.OldValue
                      , TCC.NewValue
                      , TCC.ContextReferenceId
                      , TCC.ContextReferenceType
                      , TCC.CreatorId
                      , TCC.CreateDate
        FROM dbo.TrackingColumnChange AS TCC WITH (NOLOCK)
        WHERE TCC.TrackingColumnChangeId > @LastMigratedId
        ORDER BY TCC.TrackingColumnChangeId -- 按主键排序保证范围扫描高效

        SET @RowsMoved = @@ROWCOUNT

        -- 更新已迁移的最大ID
        SELECT @LastMigratedId = MAX(TrackingColumnChangeId) 
        FROM dbo.NEWTrackingColumnChange WITH (NOLOCK)

        COMMIT TRAN

        SET IDENTITY_INSERT dbo.NEWTrackingColumnChange OFF

        SELECT @IsOkToRun = CAST(PropertyValue AS BIT)
        FROM dbo.ConfigSettings 
        WHERE PropertyName = 'TCCTableMoveEnabled'

    END

2. 解决并行等待问题 #

• 针对该查询添加OPTION (MAXDOP 1)，强制串行执行，避免并行引发的CXPACKET/CXCONSUMER等待
• 检查服务器cost threshold for parallelism设置，适当调高阈值，让小开销查询不触发并行

3. 其他优化点 #

• 按需调整或移除WAITFOR DELAY：若原表写入压力不大，延迟会拖慢迁移速度
• 确保新表主键为聚集索引：聚集索引的顺序插入效率远高于非聚集索引，且不会产生碎片
• 迁移期间禁用新表非必要约束/索引：完成后再重建，减少写入时的索引维护开销
• 尝试INSERT ... WITH (TABLOCK)：对新表启用批量插入优化，提升写入速度（需确保无其他进程同时写入新表）

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