如何在不超载SQL Server的情况下迁移600万行数据表

一、精细化批量拆分与资源控速 #

• 缩小单批数据量：别硬扛10万行，试试1-5万的小批量，测试找到能把DTU稳定压在70%以下的最优值。
• 批量间加缓冲延迟：每迁完一批就停1-2秒，用WAITFOR DELAY '00:00:01'实现，给数据库留够资源回收的时间。
• 按有序主键分批：避免全表扫描，用自增ID或唯一索引切分数据块，示例代码：

  DECLARE @BatchSize INT = 20000;
  DECLARE @LastID INT = 0;
  DECLARE @MaxID INT = (SELECT MAX(ID) FROM SourceTable);
  
  WHILE @LastID < @MaxID
  BEGIN
      INSERT INTO TargetTable (Col1, Col2, ...)
      SELECT Col1, Col2, ...
      FROM SourceTable
      WHERE ID > @LastID AND ID <= @LastID + @BatchSize;
  
      SET @LastID = @LastID + @BatchSize;
      WAITFOR DELAY '00:00:01'; -- 延迟时间按需调
  END
  

• 单批单提交：别把所有批量塞进一个大事务，每批单独提交，减少日志和锁的压力。

二、临时减载目标表的额外开销 #

• 禁用非聚集索引再重建：迁移前先关掉目标表的非聚集索引，写完数据再重建，避免每一行写入都维护索引：

  ALTER INDEX ALL ON TargetTable DISABLE;
  -- 迁移完执行
  ALTER INDEX ALL ON TargetTable REBUILD;
  

• 只迁需要的字段：别用SELECT *，明确列名，减少数据传输量。
• 临时禁用约束和触发器：外键检查、触发器执行都会耗资源，先关再开：

  -- 禁用外键
  ALTER TABLE TargetTable NOCHECK CONSTRAINT ALL;
  -- 禁用触发器
  DISABLE TRIGGER ALL ON TargetTable;
  -- 迁移完成后恢复
  ALTER TABLE TargetTable CHECK CONSTRAINT ALL;
  ENABLE TRIGGER ALL ON TargetTable;
  

三、用轻量工具和特性替代常规INSERT #

• 用bcp命令行工具：比Python脚本或INSERT SELECT更轻量化，支持分批导进导出：

  # 分批导出源表
  bcp YourDB.dbo.SourceTable out "C:\temp\data.bcp" -S YourServer -U User -P Pwd -n -b 20000
  # 分批导入目标表
  bcp YourDB.dbo.TargetTable in "C:\temp\data.bcp" -S YourServer -U User -P Pwd -n -b 20000 -k
  

• 给INSERT SELECT加并行限制：用OPTION (MAXDOP 1)强制单线程执行，避免抢占过多CPU：

  INSERT INTO TargetTable (Col1, Col2)
  SELECT Col1, Col2 FROM SourceTable
  WHERE ID BETWEEN @StartID AND @EndID
  OPTION (MAXDOP 1);
  

• 分区切换（如果适用）：如果源表和目标表结构、分区方案完全一致，分区切换几乎零耗时，资源消耗极低——前提是表已经按合适的键做了分区。

四、调度与资源隔离 #

• 选低峰时段执行：凌晨业务最闲的时候跑迁移，哪怕负载高一点也不影响正常业务。
• 用资源调控器限流：给迁移任务单独分配资源池，限制它能占用的CPU、内存比例，避免吃光DTU。

五、优化日志生成 #

• 临时切换恢复模式：如果目标库允许，迁移前切到简单恢复模式，减少日志量，迁完再切回原模式：

  ALTER DATABASE YourDB SET RECOVERY SIMPLE;
  -- 迁移完成后恢复
  ALTER DATABASE YourDB SET RECOVERY FULL;
  

• 完整模式下定期备份日志：避免日志文件膨胀，同时释放日志空间。

内容的提问来源于stack exchange，提问作者Rafaella Guimarães