MSSQL 2016 AG集群中INSERT INTO...SELECT...执行模式判定与HADR_SYNC_COMMIT等待优化问询

咱们先直接回答你第一个问题：INSERT INTO...SELECT...不是逐行插入，它是一个单事务内的批量插入操作。但正是因为整个批量操作被包裹在一个大事务里，在AG集群要求的全恢复模式下，SQL Server会完整记录每一行的修改日志，导致事务日志量巨大——而同步提交模式下，主副本必须等待辅助副本完全同步这份庞大的日志后才能提交事务，这就是你遇到HADR_SYNC_COMMIT等待的核心原因。

接下来聊聊怎么优化，核心思路是缩小单事务的日志体积，同时尽可能减少同步压力：

一、减少事务日志占用的具体方案 #

1. 拆分批量插入为小批次提交 #

把一次性的大INSERT拆成多个小事务，每个事务只插入一部分数据，这样每个小事务的日志量大幅降低，辅助副本同步的压力也会分散，等待时间自然缩短。

示例代码（根据你的表结构调整过滤条件，避免重复插入）：

DECLARE @BatchSize INT = 1000; -- 可根据实际情况调整批次大小
DECLARE @LastInsertedID INT = 0; -- 假设tableB有自增ID作为增量标识

WHILE 1 = 1
BEGIN
    INSERT INTO tableA (col1, col2, col3, ...)
    SELECT TOP (@BatchSize) col1, col2, col3, ...
    FROM tableB
    WHERE ID > @LastInsertedID; -- 仅插入上次批次之后的新数据

    SET @LastInsertedID = SCOPE_IDENTITY(); -- 更新最后插入的ID边界
    IF @@ROWCOUNT < @BatchSize
        BREAK; -- 没有更多数据时退出循环
END

如果tableB没有自增ID，也可以用时间戳字段（比如CreateTime）作为增量筛选条件，确保每次只处理新增数据，避免重复操作。

2. 启用最小日志记录 #

在全恢复模式下，满足特定条件时可以触发最小日志记录，SQL Server只会记录页分配信息，而不是每行的修改，日志量能减少90%以上。关键是给目标表加TABLOCK提示，同时确保目标表是堆表（无聚集索引）或者聚集索引为空：

INSERT INTO tableA WITH (TABLOCK)
SELECT * FROM tableB;

注意：TABLOCK会对tableA加表级锁，如果你的应用有其他并发读写tableA的场景，需要评估锁的影响。如果tableA有聚集索引且不是空表，这个方法的效果会打折扣，此时优先考虑分批插入。

3. 清理非必要索引 #

tableA上的索引越多，插入时需要维护的索引日志就越多。如果某些非聚集索引不是业务实时必需的，可以暂时禁用这些索引，完成插入后再重建——这能显著减少单事务的日志量。

二、其他你可能忽略的优化方向 #

1. 优化存储过程的执行频率 #

和开发团队沟通：是否真的需要每分钟多次执行？如果可以合并执行（比如每2分钟执行一次，每次处理累积的新增数据），能减少事务的总数量，降低AG同步的整体压力。

2. 增量同步而非全量插入 #

如果当前存储过程每次都全量插入tableB的数据，一定要改成增量同步：通过跟踪tableB的新增数据（比如用自增ID、时间戳），每次只插入上次执行后新增的记录，从根源上减少每次插入的数据量，这对降低日志占用和等待时间效果最明显。

3. 检查AG副本的同步状态 #

虽然你提到辅助副本无磁盘延迟、网络没问题，但还是建议检查一下AG的同步指标：

• 查询sys.dm_hadr_database_replica_states，查看log_send_queue_size（主副本待发送的日志大小）和redo_queue_size（辅助副本待重做的日志大小），如果这两个值持续偏高，说明辅助副本的重做速度跟不上，可能需要优化辅助副本的硬件（比如提升磁盘IO）或者调整重做线程的配置。

4. 考虑延迟事务持久性（谨慎使用） #

SQL Server 2016支持延迟事务持久性，即事务提交时不等待日志写入磁盘，而是异步写入。这能减少主副本的日志写入等待，进而降低HADR_SYNC_COMMIT的等待时间，但要注意：这个特性会带来数据丢失的风险（如果主副本突然宕机，未写入磁盘的日志会丢失），需要结合业务的容错要求评估是否使用。

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