SQL Server从Dev库复制表到QA库的高效迁移方法咨询

1. 优先使用原生批量复制工具bcp #

bcp是SQL Server官方提供的最低开销批量数据导入导出工具，性能远高于可视化的导入导出向导，660万行数据通常可在半小时内完成迁移（取决于磁盘和内网带宽），操作步骤如下：

• 在Dev环境导出符合过滤条件的数据到本地二进制文件：
  执行命令：
  bcp "SELECT * FROM [你的Dev库名].[Schema名].[Table_Name] WITH (NOLOCK) WHERE [ColumnDate] > 2018 OR [Code] in ('A', 'B', 'C','D')" queryout D:\temp\table_data.dat -S Dev服务器地址 -d Dev库名 -U 账号 -P 密码 -N -b 10000
  参数说明：-N使用原生数据格式避免编码转换开销，-b 10000指每1万行提交一次，避免事务日志暴涨。
• 在QA环境导入生成的dat文件：
  执行命令：
  bcp [你的QA库名].[Schema名].[Table_Name] in D:\temp\table_data.dat -S QA服务器地址 -d QA库名 -U 账号 -P 密码 -N -b 10000 -h "TABLOCK"
  参数说明：-h "TABLOCK"使用表锁减少锁竞争开销，大幅提升写入速度。

2. 可视化操作可选优化SSIS包 #

导入导出向导本质是生成临时SSIS包执行迁移，你可以将向导生成的包保存后做以下调整，性能可提升3~5倍：

• 调整数据流任务参数：将默认缓冲区大小从10MB调整为100MB左右，单次处理行数提升到10000行以上
• 目标OLE DB组件选择「快速加载」模式，勾选表锁选项，确认数据无问题的前提下可临时关闭约束检查
• 拆分原有过滤条件避免OR导致的索引失效，拆分为两个并行执行的数据流：
  第一个数据流查询：SELECT * FROM [Table_Name] WITH (NOLOCK) WHERE [ColumnDate] > 2018
  第二个数据流查询：SELECT * FROM [Table_Name] WITH (NOLOCK) WHERE [Code] in ('A', 'B', 'C','D') AND [ColumnDate] <=2018

3. 临时调整数据库配置降低迁移开销 #

• 迁移前将QA库的恢复模式暂时改为简单模式，减少事务日志写入量，迁移完成后再改回原有配置
• 提前删除QA目标表上的非聚集索引、触发器，数据导入完成后再重建，比边导入边维护索引快至少3倍
• 确认Dev库的ColumnDate和Code字段已建立联合索引，避免过滤查询时全表扫描拖慢导出速度

4. 同内网环境可选链接服务器直接插入 #

如果Dev和QA数据库在内网互通、网络延迟很低，可以在QA库创建指向Dev库的链接服务器，直接执行插入语句：

INSERT INTO [QA库].[Schema].[Table_Name] WITH (TABLOCK)
SELECT * 
FROM [Dev链接服务器名].[Dev库].[Schema].[Table_Name] WITH (NOLOCK)
WHERE [ColumnDate] > 2018 OR [Code] in ('A', 'B', 'C','D')
OPTION (MAXDOP 8)

通过MAXDOP参数开启多线程并行查询插入，速度也远高于默认的导入导出向导。

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