如何大幅加速SQL Server从链接服务器复制表至本地的操作？

问题场景 #

当前通过链接服务器执行表复制操作，SQL语句如下：

DELETE FROM [LocalTable]
INSERT INTO [LocalTable] 
    SELECT * FROM [LinkedServer].[LinkedDB].[dbo].[RemoteTable]

仅60万条数据就耗时1分钟，且该操作需频繁执行。本地服务器为SQL Server 2008，本地表含3个索引；远程服务器为SQL Server 2022，远程表含6个索引，两者均无主键/外键。

补充信息：

• 数据量400MB，索引数据115MB
• 执行计划占比：索引插入16%、排序12%、表假脱机66%、表插入43%、远程查询15%

一、削减本地索引维护开销 #

• 先禁用/删除索引，插入后重建：每次INSERT都会触发索引更新，60万条数据的多次索引维护开销远大于一次性重建。操作示例：

  -- 禁用本地表所有索引（保留索引结构，后续重建更快）
  ALTER INDEX ALL ON [LocalTable] DISABLE;
  
  DELETE FROM [LocalTable];
  INSERT INTO [LocalTable] SELECT * FROM [LinkedServer].[LinkedDB].[dbo].[RemoteTable];
  
  -- 重建索引
  ALTER INDEX ALL ON [LocalTable] REBUILD;
  

  若能接受索引完全离线，也可直接删除索引，插入完成后重新创建，效率可能更高。
• 用TRUNCATE替代DELETE：TRUNCATE是DDL操作，不记录逐行删除日志，速度远快于DELETE，适合全量清空场景。注意：TRUNCATE无法回滚（非事务环境下），且需要更高权限。

二、优化数据读取与传输 #

• 明确指定需要复制的列：避免使用SELECT *，只复制业务必需的列，减少不必要的数据传输量，同时降低远程表索引对查询的影响。
• 开启批量插入：通过BATCHSIZE参数分批插入，减少单次插入的日志压力和锁竞争。示例：

  INSERT INTO [LocalTable] 
  SELECT * FROM [LinkedServer].[LinkedDB].[dbo].[RemoteTable]
  OPTION (BATCHSIZE = 10000);
  

• 检查链接服务器配置：确保已启用RPC OUT和DATA ACCESS选项，消除数据传输的额外限制。

三、针对执行计划的精准优化 #

• 用OPENQUERY转移查询负载到远程：执行计划中表假脱机占比66%，说明本地需要将远程结果写入tempdb再插入。使用OPENQUERY让远程服务器直接处理查询，仅返回结果集，减少本地tempdb开销：

  DELETE FROM [LocalTable];
  INSERT INTO [LocalTable]
  SELECT * FROM OPENQUERY([LinkedServer], 'SELECT * FROM [LinkedDB].[dbo].[RemoteTable]');
  

• 消除不必要的排序：若远程查询返回的数据带有不必要的排序（业务无要求），可在远程查询语句中去掉排序逻辑；或检查本地表索引是否触发插入时的自动排序，调整索引结构避免额外排序开销。

四、长期高频同步的进阶方案 #

• 改用SQL Server复制机制：如果允许配置，使用快照复制或事务复制替代手动DELETE/INSERT。复制机制支持增量同步（配置合适时），比全量复制高效得多，适合频繁执行的同步需求。
• 升级本地SQL Server版本：SQL Server 2008已停止支持，新版本（如2016+）在链接服务器、批量插入、内存管理等方面有显著性能优化，能从底层提升同步效率。

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