无直接应用访问权限下加速SQL Server批量插入的方案

背景 #

• 基于专有语言开发的应用，通过ODBC连接SQL Server 2019，ORM为黑盒，无法修改插入逻辑，仅能从数据库端优化
• 业务场景：通过预编译语句向CostTable批量插入数千条数据
• 表结构：

CREATE TABLE [dbo].[CostTable](
    [Deleted] [bit] NOT NULL,
    [TimeEntry] [datetime] NOT NULL,
    [UserEntry] [int] NOT NULL,
    [GUID] [varchar](50) NOT NULL,
    [Costs1] [decimal](18, 2) NULL,
    [Costs2] [decimal](18, 2) NULL,
    [Costs3] [decimal](18, 2) NULL,
    [Costs4] [decimal](18, 2) NULL,
    [Costs5] [decimal](18, 2) NULL,
    [Costs6] [decimal](18, 2) NULL,
    [Costs7] [decimal](18, 2) NULL,
 CONSTRAINT [PK_CostTable] PRIMARY KEY NONCLUSTERED 
(
    [GUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

• 额外信息：表上存在若干非主键索引；主键为应用生成的字母数字串，无法修改为数值型

1. 关闭检查约束再插入，之后开启能提升效率吗？ #

确实能带来一定性能提升，但具体收益取决于约束的数量和复杂程度。检查约束会在每条数据插入时做验证，批量插入数千条数据时，反复验证会额外消耗CPU资源。

但操作时要注意几个关键点：

• 必须保证插入的所有数据都符合约束规则，否则重新开启约束时会报错，导致整个操作失败
• 操作期间需避免其他写入操作，防止不符合约束的脏数据进入表中
• 具体操作步骤：

  -- 关闭所有检查约束
  ALTER TABLE [dbo].[CostTable] NOCHECK CONSTRAINT ALL;
  -- 让应用执行批量插入操作
  -- 重新开启并验证约束（此步骤会扫描全表检查所有数据，耗时可能较长，需权衡）
  ALTER TABLE [dbo].[CostTable] CHECK CONSTRAINT ALL;
  

2. 其他可行的优化方案 #

索引优化（见效最明显的方向之一） #

• 临时禁用非主键索引：批量插入时，每个非主键索引都会逐行更新，这是最大的性能瓶颈之一。可以先禁用所有非聚集非主键索引，插入完成后再重建——重建索引的开销远小于插入时逐行维护索引的开销，尤其是索引数量较多时。
  注意：主键为非聚集索引，不能禁用（禁用会导致主键失效），需单独针对非主键索引操作：

  -- 禁用某一非主键索引（示例为时间字段索引）
  ALTER INDEX IX_CostTable_TimeEntry ON [dbo].[CostTable] DISABLE;
  -- 应用执行插入后，重建索引
  ALTER INDEX IX_CostTable_TimeEntry ON [dbo].[CostTable] REBUILD;
  

• 开启主键的顺序插入优化：如果应用生成的GUID是有顺序的（比如用NEWSEQUENTIALID()或类似逻辑生成），可以开启OPTIMIZE_FOR_SEQUENTIAL_KEY = ON，减少主键插入时的页竞争：

  ALTER TABLE [dbo].[CostTable] 
  ALTER CONSTRAINT PK_CostTable 
  WITH (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON);
  

数据库配置调整 #

• 切换恢复模式到BULK_LOGGED：如果业务允许暂时放弃完整的事务日志可恢复性，批量插入期间将数据库恢复模式改为BULK_LOGGED，SQL Server会对批量操作做最小日志记录，大幅减少日志IO开销。插入完成后记得改回原模式（比如FULL），并立即执行一次完整备份：

  ALTER DATABASE YourDBName SET RECOVERY BULK_LOGGED;
  -- 执行批量插入
  ALTER DATABASE YourDBName SET RECOVERY FULL;
  BACKUP DATABASE YourDBName TO DISK = 'D:\Backup\YourDB_Full.bak';
  

• 调整事务日志的大小和增长规则：避免日志文件频繁自动增长（每次增长会阻塞IO）。将日志文件初始值设为足够大的数值，增长步长改为固定大小（比如1GB），不要使用百分比增长。
• 服务器配置微调：如果服务器CPU核心充足，可以适当调大max degree of parallelism；另外开启optimize for ad hoc workloads（如果该表的查询多为临时查询）。

表结构与存储优化 #

• 开启数据压缩：给表开启页压缩或行压缩，减少数据体积，降低IO开销。压缩会增加少量CPU消耗，但如果是IO瓶颈场景，收益非常明显：

  ALTER TABLE [dbo].[CostTable] REBUILD WITH (DATA_COMPRESSION = PAGE);
  

• 清理冗余列：如果某些Costs列几乎不被使用，在业务允许的前提下可以删除，减少每行数据的大小，提升插入和存储效率。

3. ODBC驱动的调整或更换 #

更换驱动 #

建议替换掉SQL Server Native Client 11.0，改用ODBC Driver 17 for SQL Server或更高版本。新版本驱动的优势包括：

• 针对批量处理做了更多性能优化
• 支持TLS 1.2+加密，更安全且性能更优
• 修复了旧驱动的部分性能bug
• 支持MultiSubnetFailover等现代特性（如果使用了集群环境）

调整驱动参数 #

在ODBC连接字符串中添加以下参数，可进一步优化性能：

• Packet Size=8192（或更大值，比如32768）：增大网络数据包大小，减少网络交互次数，适合批量数据传输
• AutoTranslate=No：如果应用与数据库字符集一致，关闭自动字符转换，节省CPU资源
• UseFMTONLY=No：避免不必要的元数据查询
• Pooling=Yes;Max Pool Size=50：确保连接池开启，减少连接建立与销毁的开销（Max Pool Size可根据并发量调整）
• ApplicationIntent=ReadWrite：明确告知SQL Server为读写操作，帮助优化执行计划

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