使用 Batch Size 提高 Transaction 性能

Article
11/21/2012

我经常看到客户测试SQL Server 的插入(INSERT)或者批量导入大量数据的性能表现。其中有测试大量INSERT的TSQL脚本如下：

use myDB

createtable t1(id intprimarykeynotnullidentity (1,1),name varchar(200),memo char(500));

truncatetable t1

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

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的写快多了）等。

使用 Batch Size 提高 Transaction 性能

Additional resources