?

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

?

创建测试环境和基准

?

--创建表tb_source并插入10000条数据

use master

go

create database test;

alter database test set recovery bulk_logged with no_wait;

go

use test

go

create table tb_source (id int,val char(100));

insert into tb_source

select top(10000) ROW_NUMBER() over (order by sysdatetime()),'HeHe' from master..spt_values a,master..spt_values b

go

?

创建基准表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_benchmark

from '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_mb

FROM sys.fn_dblog(NULL, NULL) AS D

WHERE AllocUnitName = 'dbo.tb_benchmark' OR AllocUnitName LIKE 'dbo.tb_benchmark.%';

SELECT Operation, Context,

? AVG([Log Record LENGTH]) AS AvgLen, COUNT(*) AS Cnt

FROM sys.fn_dblog(NULL, NULL) AS D

WHERE AllocUnitName = 'dbo.tb_benchmark' OR AllocUnitName LIKE 'dbo.tb_benchmark.%'

GROUP BY Operation, Context, ROUND([Log Record LENGTH], -2)

ORDER BY AvgLen, Operation, Context;

?

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

?

1.bulk insert非空堆表

?

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

?

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

?

2. bulk insert空聚集表

?

create table tb_btree1 (id int ,val char(100))

create clustered index cix_tb_btree1

on ?tb_btree1 (id)

go

bulk insert tb_btree1

from 'd:\ss\source.csv'

with (tablock)

?

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

?

使用TF-610，而不使用tablock：

?

create table tb_btree2 (id int ,val char(100))

create clustered index cix_tb_btree2

on ?tb_btree2 (id)

go

dbcc traceon(610)

bulk insert tb_btree2

from 'd:\ss\source.csv'

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

?

3. bulk insert非空聚集表

?

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

?

create table tb_btree (id int ,val char(100))

create clustered index cix_tb_btree

on ?tb_btree (id)

go

declare @i int=0

while @i<60

begin

set @i=@i+1

insert into tb_btree values(@i,'HaHa')

end

?

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

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

?

dbcc traceon(610)

bulk insert tb_btree

from 'd:\ss\source.csv'

with (ORDER(ID),FIRSTROW=61)

?

与前面对比，可以看出新插入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

?