使用 Batch Size 提高 Transaction 性能
我经常看到客户测试SQL Server 的插入(INSERT)或者批量导入大量数据的性能表现。其中有测试大量INSERT的TSQL脚本如下：
use myDB
go
createtable t1(id intprimarykeynotnullidentity (1,1),name varchar(200),memo char(500));
go
truncatetable t1
go
declare @i int
set @i=0
declare @beginTime datetime,@endTime datetime
set @beginTime=GETDATE()
while(@i<200000)
begin
insertinto t1(name,memo)values('test'+str(RAND()*100000000),'memo'+str(RAND()*100000000))
set @i=@i+1
end
set @endTime=GETDATE()
select @endTime-@beginTime
上面这个脚本测试插入200000行数据需要的时间。这个脚本有问题么?
语法没有问题，但是写法却不够优化。如果你使用performance monitor
来观察，就会发现在数据插入期间log flushes/sec的次数非常高。在我的机器上达到5000。Log flushes发生一次，意味着SQL server 需要写入事务日志（transaction log）一次。每秒5000次的日志写动作导致磁盘大量的写操作。正是这个磁盘写操作影响了上面的batch的性能。我上面脚本在我的机器上使用了大概40秒左右的时间。
如何改进这个脚本的性能呢？如何减少log flushes/sec从而减少磁盘的写操作？ 答案是使用Batch Size如下面脚本所示。
truncatetable t1
go
declare @i int
set @i=0
declare @beginTime datetime,@endTime datetime
set @beginTime=GETDATE()
declare @batchSize int
set @batchSize=1000
while(@i<200000)
begin
if (@i%@batchSize=0)
begin
if (@@TRANCOUNT>0)COMMITTRAN
BEGINTRAN
end
insertinto t1(name,memo)values('test'+str(RAND()*100000000),'memo'+str(RAND()*100000000))
set @i=@i+1
end
if (@@TRANCOUNT>0)COMMITTRAN
set @endTime=GETDATE()
select @endTime-@beginTime
黄色高亮是我改进的地方。我在同样的机器上跑了一下，奇迹发生了，它只使用了7秒多一点。快了5倍多！如果在看下performance
Log里面的log flushes/sec，已经减少到700左右。我的测试图如下所示：
上面的这个小实验充分说明了batch size的重要性。它把很多小的transaction合并成一个大的合适的 transaction来减少磁盘写操作，从而获得极大性能提升。Batch size究竟多大才是最佳的呢？这个取决您的机器，需要你自己测试。
很多地方需要设置batch size提高性能。比如如果你使用游标（cursor）操作数据，记得使用batch Size。在bcp里面，也需要指定batchsize来提高性能：
bcp/?
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
[-d database name]
如果使用bulk insert，也记得使用batch size
bulkinsert t1 from'\t.bcp'
with (
fire_triggers,
datafiletype='native',
tablock,
batchsize=1000
)
当然，性能优化里面batchSize并不是唯一需要考虑的因素。还需要考虑其他方面，比如并发开几个窗口运行bcp，使用合适的磁盘的RAID类型（RAID10比RAID5的写快多了）等。
Comments
- Anonymous
January 11, 2013
这样测试是不公平的，因为客户的实际使用环境并不是这样的。我要是用Hekaton不写日志不是更快。 - Anonymous
November 27, 2013
bulk insert t1 from't.bcp'刚才测试了bcp文件是导入不了的 - Anonymous
December 12, 2013
这篇文章只是提供了一种思路供大家参考，很有意义。说什么公平不公平、不写日志什么的就理解偏激了