如何提升Azure SQL中SqlBulkCopy性能并优化DTU与日志写入占比？

问题背景 #

本地数据库插入100,000条记录耗时约2秒，切换到Azure SQL后耗时大幅增加且DTU占比很高。测试使用DTU购买计划的Azure SQL，结果如下：

• 10 DTU：37秒
• 20 DTU：24秒
• 50 DTU：18秒
• 100 DTU：15秒

尽管提升DTU后耗时有所降低，但avg_log_write_percent仍处于高位，DTU占比也很高。当前使用的代码如下：

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()))
{
    Stopwatch stopWatch = new Stopwatch();
    stopWatch.Start();
    connection.Open();

    var transaction = connection.BeginTransaction();
    using (var sqlBulk = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, transaction))
    {
        // SET BatchSize value.
        sqlBulk.BatchSize = 150000;
        sqlBulk.BulkCopyTimeout = 60;
        sqlBulk.DestinationTableName = "Master_Transaction";
        sqlBulk.WriteToServer(dataTable);

        transaction.Commit();
    }
    stopWatch.Stop();
    TimeSpan ts = stopWatch.Elapsed;
    Console.WriteLine($"database running time {ts}");
}

备注：目标表带有聚集索引。

问题解答 #

1. 如何提升Azure SQL中SqlBulkCopy的性能？ #

通过修改代码实现

• 调整BatchSize：当前设置的150000大于实际数据量100000，建议将BatchSize调整为10000-50000区间，分批次写入可分散日志写入压力，避免单批次日志量过大导致的性能瓶颈。
• 优化事务与SqlBulkCopyOptions：
  • 添加SqlBulkCopyOptions.UseInternalTransaction，让每个批次自动提交事务，替代手动长事务，减少日志持久化的开销；
  • 若无需跨操作事务，可移除手动BeginTransaction，依赖SqlBulkCopy默认的批量事务逻辑。
• 明确字段映射：手动指定ColumnMappings，避免自动映射的字段匹配开销，确保DataTable与目标表字段完全对应。
• 临时禁用非必要索引与约束：批量插入前禁用目标表的非聚集索引、外键约束（插入完成后重新启用），减少写入时的索引维护和约束检查成本（需提前确保数据完整性）。

通过配置Azure资源实现

• 临时升级DTU：批量插入任务执行期间临时提升DTU规格，完成后降级，平衡性能需求与成本支出。
• 切换至vCore购买模型：vCore模型支持独立调整计算、内存、存储资源，可针对性提升IOPS和日志写入吞吐量，更适配批量操作场景。
• 升级存储层级：将标准存储替换为高级存储，提升存储IO性能，降低日志写入延迟。
• 暂停异地复制（若适用）：批量插入期间暂停异地复制或只读副本的日志同步，减少额外的日志传输开销，完成后恢复同步。

2. 能否降低DTU最大值并减少avg_log_write_percent？ #

可以，通过以下手段实现：

• 优化日志生成量：采用上述代码优化方案（分批次写入、临时禁用非聚集索引等），减少单批次日志输出，降低avg_log_write_percent。
• 开启DTU自动缩放：配置Azure SQL的自动缩放规则，设置合理的最小/最大DTU阈值，让系统在批量插入时自动扩容，任务完成后自动缩容，既满足性能需求又降低常态下的DTU最大值。
• 确保日志压缩开启：Azure SQL默认开启日志压缩，该功能可减少日志的存储空间占用和实际写入量。
• 使用分区表：将目标表按聚集索引键进行分区，批量插入时仅操作对应分区，减少索引维护范围和日志生成量。

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