SQL Server MERGE性能优化问询：4000万行数据合并耗时过长

在配备64GB内存、16核心的高性能数据库服务器上，处理4000万行数据的MERGE操作耗时数小时。目前已通过SqlBulkCopy将数据加载至临时表，再分批执行MERGE以降低对TempDb的影响，现有实现代码及索引配置如下：

现有批量MERGE代码 #

DECLARE @RowID int = 0,
        @RowCount int,
        @Batches int = 0,
        @BatchSize int = 10000

SELECT @RowCount = COUNT(1) 
FROM [someStagingTable]

WHILE @RowID <= @RowCount 
BEGIN
    MERGE INTO [someTargetTable] AS Target
    USING (SELECT * FROM [someStagingTable] 
           WHERE ID BETWEEN @RowID AND @RowID + @BatchSize - 1) AS Source
              ON Target.AccountNumber = Source.AccountNumber

    WHEN MATCHED THEN
        UPDATE 
            SET ...

    WHEN NOT MATCHED BY TARGET THEN
        INSERT ...

    SET @RowID = @RowID + @BatchSize
    SET @Batches = @Batches + 1
 
    COMMIT
END

当前索引配置 #

• [someStagingTable].ID为带聚集索引的int自增列
• [someTargetTable].AccountNumber已建立索引

性能优化建议 #

1. 调整批次大小：当前1万行的批次过小，会产生过多循环和事务开销。建议测试将批次size调整为5万-10万，减少循环次数，提升整体执行效率。
2. 精简staging表查询列：在USING子句中只选择MERGE操作必需的列，避免SELECT *带来的不必要数据传输和内存占用。
3. 优化目标表索引类型：确认[someTargetTable].AccountNumber的索引为唯一非聚集索引，MERGE匹配时唯一索引的查找效率远高于非唯一索引；若AccountNumber是主键，直接用主键匹配性能更优。
4. 临时禁用非必要索引：MERGE过程中频繁的UPDATE/INSERT会触发索引维护，可在操作前禁用目标表非必要的非聚集索引，操作完成后重建，大幅降低IO开销。
5. 替换COUNT(1)减少全表扫描：用SELECT @RowCount = MAX(ID) FROM [someStagingTable]替代COUNT(1)，借助聚集索引直接获取最大值，避免全表统计行数的开销。
6. 调整事务提交粒度：当前每批次单独COMMIT，可尝试合并多个批次为一个事务（例如每10批次提交一次），减少事务日志的频繁写入，但需平衡事务大小与回滚风险。
7. 添加TABLOCK提示：在MERGE语句中对目标表添加WITH (TABLOCK)提示，允许批量操作采用最小日志记录，降低日志IO压力。

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