你的位置:首页 > 数据库

[数据库]导数中的最小化日志记录:测试和分析


测试和分析

依据上文件最小化日志的判断逻辑,对常见的BULK INSERT和INSERT INTO...SELECT做测试和分析

  创建测试环境和基准

--创建表tb_source并插入10000条数据use mastergocreate database test;alter database test set recovery bulk_logged with no_wait;gouse testgocreate table tb_source (id int,val char(100));insert into tb_sourceselect top(10000) ROW_NUMBER() over (order by sysdatetime()),'HeHe' from master..spt_values a,master..spt_values bgo 创建基准表tb_benchmark,将tb_source的数据导出到文件,再导入到基准表。然后获取最小化日志的统计做为测试基准。create table tb_benchmark (id int,val char(100));/**CMD中导出数据:C:\Users\Administrator>bcp test.dbo.tb_source out D:\ss\source.csv -S. -T -c**/--导入数据并并统计日志create table tb_benchmark (id int,val char(100));bulk insert tb_benchmarkfrom 'd:\ss\source.csv'with (tablock) ;SELECT COUNT(*)AS numrecords, CAST((COALESCE(SUM([Log Record LENGTH]), 0))  / 1024. / 1024. AS NUMERIC(12, 2)) AS size_mbFROM sys.fn_dblog(NULL, NULL) AS DWHERE AllocUnitName = 'dbo.tb_benchmark' OR AllocUnitName LIKE 'dbo.tb_benchmark.%';SELECT Operation, Context, AVG([Log Record LENGTH]) AS AvgLen, COUNT(*) AS CntFROM sys.fn_dblog(NULL, NULL) AS DWHERE AllocUnitName = 'dbo.tb_benchmark' OR AllocUnitName LIKE 'dbo.tb_benchmark.%'GROUP BY Operation, Context, ROUND([Log Record LENGTH], -2)ORDER BY AvgLen, Operation, Context;

Env&Benchmark

 

clipboard

从结果可以看出插入10000行,只产生了170条日志。没有大于行大小(>104)的日志记录。确定是最小化日志记录。

1.bulk insert非空堆表

  前面的基准测试可以看到空堆表的insert,最小化日志记录成功。非空的话,向基准表再导入一次数据。

clipboard[1]

从结果看,最小日志也是成立的。

2. bulk insert空聚集表

create table tb_btree1 (id int ,val char(100))create clustered index cix_tb_btree1on tb_btree1 (id)gobulk insert tb_btree1from 'd:\ss\source.csv'with (tablock)

bulk insert into empty btree

 

clipboard[2]

这个最小化也是成立的,日志记录多于空堆表的情况。

使用TF-610,而不使用tablock:

create table tb_btree2 (id int ,val char(100))create clustered index cix_tb_btree2on tb_btree2 (id)godbcc traceon(610)bulk insert tb_btree2from 'd:\ss\source.csv'

 

clipboard[3]

这种情况下不并完全是最小化日志记录。从测试来看空聚集索引使用tablock产生的日志量会更少一些。这里为什么会有71行插入是完整日志记录的呢?一个表至少有一个数据页,向已有的数据页上插入行是完整日志记录,新分配的页是最小日志记录。至于为什么是71行,下面3.非空聚集表中一起分析。

3. bulk insert非空聚集表

  先创建表tb_btree,然后向其中插入60条记录。观察完整日志记录的情况。

create table tb_btree (id int ,val char(100))create clustered index cix_tb_btreeon tb_btree (id)godeclare @i int=0while @i<60beginset @i=@i+1insert into tb_btree values(@i,'HaHa')end

 

clipboard[4]

插入60条记录,有64条日志记录。聚集索引日志60条,平均长度212(大于104)。

然后再向tb_tree中插入数据,对比日志情况。注意:导入数据我是从ID=61开始导入的。因为原表中有ID=[1,60]的行了,如果导入数据重复,会发生行移动和页拆分等操作,这样就会增加很多额外的日志,不便分析。

dbcc traceon(610)bulk insert tb_btreefrom 'd:\ss\source.csv'with (ORDER(ID),FIRSTROW=61)

 

clipboard[5]

与前面对比,可以看出新插入9960行数据,只新增了913条日志。有意思的是,索引叶级页插入(LOP_INSERT_ROWS&LCX_CLUSTERED)增加了11条,这11条是完整日志记录的,其它行插入是最小化日志插入的。

这是为什么呢?

  这是因为原来tb_btree中只有一个数据页,且只存放了60行数据。而这个数据页上最多只能存放71行数据。也就是,在已经存在的数据页中插入数据是完整日志记录的,新分配的数据页插入数据是最小化日志记录的

为什么最多只能存放71条数据?

  直接通过DBCC PAGE查看对应数据页最直观。或者通过理论来计算:

  通过dbcc showcontig ('tb_btree') with tableresults得到行大小为111。假设页上可以存N行数据,

  则:页头+偏移矩阵+行容量<=8KB-->96+2*N+111*N<=8192-->N<=71.65-->N=71

4. INSERT INTO...WITH(TABLOCK)...SELECT,向堆表中插入数据

--空堆表的情况,确认是最小化日志。

create table tb_heap1 (id int ,val char(100)) ;INSERT INTO tb_heap1 with(tablock) select * from tb_source;

 

clipboard[6]

--非空堆表,将上面的数据再插入一遍即可。确认是最小化日志。

INSERT INTO tb_heap1 with(tablock) select * from tb_source;

 

clipboard[7]

5. INSERT INTO...SELECT...ODER BY(...),向聚集表中插入数据

--空的聚集表create table tb_cix (id int ,val char(100))create clustered index cix_tb_cixon tb_cix (id)godbcc traceon(610)insert into tb_cix select * from tb_source order by id

 

clipboard[8]

同样,已有的数据页上是完整日志记录,其它是最小化日志记录。

--非空聚集表

--先插入70行数据,是完整日志记录的。create table tb_cix2 (id int ,val char(100))create clustered index cix_tb_cix2on tb_cix2 (id)godeclare @i int=0while @i<70beginset @i=@i+1;insert into tb_cix2 values(@i,'HoHo')end

 

clipboard[9]

--再从tb_source插入ID>70的9930行dbcc traceon(610)insert into tb_cix2 select * from tb_source where id>70 order by id

 

clipboard[10]

可以看到只新了一条完整日志的记录,其它是最小化日志记录的。

6. INSERT INTO...SELECT...ODER BY(...),并行导入聚集表

前文提到SQL 2008之后结合键范围锁,不会锁定整个表,只锁定某部分的键值区间,其它操作可以并行访问此区间外的数据。

这里我打开三个session,同时插入三个区间的数据[1,1000],[3000,5000],[7000,9000]

create table tb_cix3 (id int ,val char(100))create clustered index cix_tb_cix3on tb_cix3 (id)go--session 1dbcc traceon(610)insert into tb_cix3 select * from tb_source where id between 1 and 1000 order by id--session 2dbcc traceon(610)insert into tb_cix3 select * from tb_source where id between 3000 and 5000 order by idoption (querytraceon 2332)--session 3dbcc traceon(610)insert into tb_cix3 select * from tb_source where id between 7000 and 9000 order by idoption (querytraceon 2332)

 

并行导入最好用ETL工具实现,特别是数据排序这一步。我在测试时,踩到一个坑:

当第一个insert 完成后,其它的insert的oder by会失效,造成无法最小化日志。除了第一个被执行的insert外,其它的执行计划中不会有SORT操作符。只好使用TF-2332,强制数据修改操作进行排序,也就是querytraceon 2332。还有就是数据量太少,都是瞬间完成,不好控制并发。

clipboard[11]

7. 在SSIS实际导数中的一个应用简单例子

  这是实际项目中导数Destination的设置。一个聚集表导到另一个聚集表,实现了最小化日志。目标实例启用了TF-610,FastLoadOptions设置为根据目标表聚集索引键进行排序。

clipboard[12]

clipboard[13]