SQL Server 2022中BULK INSERT ORDER提示失效及额外排序问题排查

我使用SQL Server 2022 (RTM-GDR) (KB5021522) - 16.0.1050.5 (X64)，通过Java的SQLServerBulkCopy类执行批量导入工作，最终以INSERT BULK流的方式将数据传入数据库。

表结构及批量命令 #

CREATE TABLE dbo.dummy (
    id          INT              NOT NULL,
    anotherInt  INT              NULL,
    ix          INT              NULL,
    ix2         INT              NULL,
    ix3         INT              NULL,
    z           NUMERIC(19,12)   NOT NULL,
    CONSTRAINT PK__dummy__51AEE9A1E72E320E PRIMARY KEY CLUSTERED(
        id, z
    ) WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT BULK dummy ([id] INT , [z] NUMERIC(19, 12) ) WITH (TABLOCK, ORDER (id ASC,z ASC))

问题现象 #

我已预先将批量导入的数据按照表的聚集主键顺序排序，且在INSERT BULK命令中指定了ORDER选项，但查询计划中仍出现Sort运算符：

• 带Sort运算符的执行计划：计划包含Sort步骤，对导入数据重新按(id ASC, z ASC)排序
• 无Sort运算符的执行计划：计划直接执行批量插入，无额外排序步骤

存在以下矛盾点：

• 有时SQL Server会跳过排序，但多数情况不会
• 传入未排序数据时会报错，说明SQL Server确实在验证顺序
• 将数据写入文件后用BULK IMPORT并指定ORDER时，能稳定跳过Sort步骤

疑问 #

为何会出现额外的Sort步骤？如何避免这个额外步骤？

原因分析 #

1. 流式数据的顺序信任问题：SQLServerBulkCopy传递的是流式数据，SQL Server无法提前确认数据是否严格符合ORDER指定的顺序。相比BULK IMPORT读取文件时可通过预校验或统计信息确认排序性，流式数据的顺序只能通过抽样或强制排序来保证，优化器因此倾向于添加Sort运算符做保守验证。
2. 元数据传递缺失：Java的SQLServerBulkCopy在发送INSERT BULK命令时，可能未正确将数据已排序的元信息传递给SQL Server，导致优化器无法信任外部数据的排序状态，强制添加Sort步骤。
3. 成本估算策略：优化器会对比Sort成本与直接插入可能产生的页分裂等开销，若无法获取数据流的顺序统计信息，会默认选择执行Sort以避免后续性能问题。

解决方法 #

• 配置SQLServerBulkCopy的OrderHint：在Java代码中通过SQLServerBulkCopyOptions明确指定排序提示，确保驱动向SQL Server传递数据已排序的元信息：

SQLServerBulkCopyOptions copyOptions = new SQLServerBulkCopyOptions();
copyOptions.setOrderHint("id ASC, z ASC");
try (SQLServerBulkCopy bulkCopy = new SQLServerBulkCopy(connection, copyOptions)) {
    bulkCopy.setDestinationTableName("dbo.dummy");
    bulkCopy.addColumnMapping("id", "id");
    bulkCopy.addColumnMapping("z", "z");
    bulkCopy.writeToServer(dataReader);
}

• 优化批量导入配置：保持TABLOCK选项的同时，设置合理的批量大小，减少小批量导入导致的优化器保守决策；TABLOCK能降低锁竞争，让优化器更愿意跳过Sort。
• 临时禁用自动统计更新：导入前临时关闭目标表的自动统计更新，避免优化器因统计信息过时选择排序，完成后恢复：

ALTER TABLE dbo.dummy SET AUTO_UPDATE_STATISTICS OFF;
-- 执行批量导入操作
ALTER TABLE dbo.dummy SET AUTO_UPDATE_STATISTICS ON;

• 备选方案：使用BULK INSERT：若流式导入无法稳定跳过Sort，可将数据写入临时文件后执行BULK INSERT，这是目前能确保跳过Sort的可靠方式。

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