SQL Server搭配ODBC 18批量插入性能极差，求优化建议

环境与数据库配置 #

• SQL Server采用简单恢复模式（Simple）
• 已禁用所有索引，且禁用后性能无明显提升
• 服务器配置：2核4GB内存
• 目标表为无聚集索引的堆表

SQL Server版本信息 #

Microsoft SQL Server 2022 (RTM-CU14-GDR) (KB5042578) - 16.0.4140.3 (X64) 
Copyright (C) 2022 Microsoft Corporation
Enterprise Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

已尝试的批量插入方法 #

方法A：Python调用CLR实现SqlBulkCopy #

核心代码：

import clr

clr.AddReference("System")
import System
# ...
bc = System.Data.SqlClient.SqlBulkCopy(...)
#... 构建DataTable
datatable = System.Data.DataTable()
for c in cls.columns():
    datatable.Columns.Add(name, type)
for row in data:
    dotnet_row: System.Data.DataRow = datatable.NewRow()
# ...
    dotnet_row[colname] = rowvalue
    datatable.Rows.Add(dotnet_row)
# ...
bc.DestinationTableName = ...
bc.BatchSize = 10000
bc.WriteToServer.Overloads[System.Data.DataTable](datatable)

性能表现：处理16000条样本数据时，插入速度约2200行/秒。

方法B：使用pandas.to_sql #

性能表现：与方法A大致相当。

方法C：C++直接调用ODBC驱动（参数数组方式） #

核心代码：

TRYODBC(hEnv, SQL_HANDLE_ENV, SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3_80, 0));
// ... 从文件读取数据、建立连接等操作... 
TRYRET1(hdbc, SQL_HANDLE_DBC, SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt));
TRYRET1(hstmt, SQL_HANDLE_STMT, SQLSetStmtAttr(hstmt, SQL_ATTR_USE_BOOKMARKS, SQL_UB_OFF, 0));
// 按列绑定参数
TRYRET1(hstmt, SQL_HANDLE_STMT, SQLSetStmtAttr(hstmt, SQL_ATTR_PARAM_BIND_TYPE, SQL_PARAM_BIND_BY_COLUMN, 0));
// 关键配置：使用参数数组
TRYRET1(hstmt, SQL_HANDLE_STMT, SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER)data.nbRows, 0));
// 插入语句包含TABLELOCK
uint32_t offset = 0;
// 从1开始，列0为书签位
for (size_t i = 1; i <= data.headers_sizes.size(); i++)
{
    int c_type = typemapping[data.types[i - 1]][0];
    int sql_type = typemapping[data.types[i - 1]][1];
    void* begin_array = data.rawData.data() + offset;
    // 将值数组绑定到驱动
    TRYRET1(hstmt, SQL_HANDLE_STMT, SQLBindParameter(hstmt, i, SQL_PARAM_INPUT, c_type, sql_type, 0, 0, begin_array, 0, NULL));
    offset += typesizes[data.types[i - 1]] * data.nbRows;
}
// 执行插入
TRYRET1(hstmt, SQL_HANDLE_STMT, SQLExecute(hstmt));

性能表现：处理同样16000条样本数据时，插入速度约4800行/秒，性能比前两种方法翻倍。

问题 #

以上三种方法的插入性能均未达到预期，是否是服务器配置（2核4GB内存）过低导致？无法使用BCP工具。

分析与解答 #

1. 服务器配置的影响
   2核4GB内存的配置确实会对批量插入性能产生限制：

   • SQL Server在批量插入时需要内存处理数据缓存、事务日志（即使简单恢复模式下也会生成批量日志）、执行计划优化等操作。4GB内存属于较低配置，系统可能因内存不足触发频繁页交换，拖慢插入速度。
   • 2核CPU会成为瓶颈，批量插入涉及数据解析、日志写入、页分配等CPU密集型操作，资源不足会直接限制处理效率。

2. 堆表的额外开销
   目标表为无聚集索引的堆表，会带来额外性能损耗：

   • 堆表插入时易产生转发记录，当原有数据页空间不足，新数据会被放到新页并留下转发指针，后续操作会额外消耗资源。
   • 堆表的统计信息更新频率更高，插入过程中频繁更新统计信息会占用系统资源。

3. 其他优化方向

   • 调整批次大小：方法A的BatchSize设为10000，可尝试增大至50000左右（需结合内存情况避免溢出），减少事务提交次数。
   • 统一启用TABLOCK：确保所有方法都开启表锁（SqlBulkCopy可通过SqlBulkCopyOptions.TableLock启用），降低锁竞争。
   • 临时禁用自动统计更新：插入前禁用目标表的自动统计信息更新，完成后手动更新，避免插入过程中的统计更新开销。
   • 检查磁盘IO性能：批量插入高度依赖磁盘速度，尤其是日志文件所在磁盘，可检查磁盘读写延迟确认是否为IO瓶颈。
   • 调整SQL Server内存配置：将SQL Server最大内存分配设为3GB左右，减少系统内存竞争，让数据库有更多资源用于缓存。

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