SQL Server 列存储索引性能总结（7）——导入数据到列存储索引的Delta Store

接上文：SQL Server 列存储索引性能总结（6）——列存储等待信息，这一篇还是本人工作中的难点，列存储导入速度的问题，我们都知道有索引的表导入肯定比堆表慢，可是究竟为什么呢？对于列存储来说，很可能就是Delta Store的原因。

  列存储索引有推荐的最佳导入行数，过小会导致数据先进入Delta Store（行组，row group），这个是行存储的B 树结构，然后通过Tuple Mover对数据进行编码和压缩最终成为片段（segment），这时候才能说进入了真正的列存储索引中，才能真正发回去列存储索引的优势。
  在我目前的项目中，导入数据是个头痛的事情，数据量不是非常大，但是表的列很多，4~500列。导致即使是纯粹导进堆表，最高可能也就50000行每秒，更别说聚集列存储索引，而且加上公司使用固定工具带来的不合理的导入方式。
  从外部导入数据到数据库时，不管是导入列存储还是堆表，最好的方式还是使用各种大容量导入工具比如BCP/Bulk insert API等，可以使得数据能够直接进入片段而不用先到Delta store然后再进行转换。
  当然，在现实世界中很多时候没法真的尽如人意。言归正传，在没有办法使用Bulk insert的时候，到底发生了什么？下面创建一个单列的表，然后插入6700万数据用于创建足够的行组。这个数不是随便选的，后面会看到理由。

环境准备

本系列的脚本由于有时效性，所以建议先看完再执行，需要连续执行。
  首先初始化一批数据，这个可以在TempDB里面做：

--创建表
create table dbo.Test(id int not null );
--创建聚集列存储索引
create clustered columnstore index CCI on dbo.Test;
--导入刚好够一个片段的数据，也就是1048576行
declare @i as int;
set @i = 1;

begin tran
while @i <= 1048576
begin
	insert into dbo.Test
		values (@i);

	set @i = @i + 1;
end;
commit;

  在我的虚拟机上面运行了 46秒，对于这个数量级而言也还算可以。这个时候片段已经满了，接下来循环插入6次，让数据量达到6700万。这里并不是循环这么多次，而是对原有的数据复制一份然后追加，另外在跑下面脚本的时候，打开另外一个窗口同时执行接下来的命令查看元组的信息，可以看到Closed部分是越来越多：

declare @i as int;
declare @max as int;
set @i = 1;

while @i <= 6
begin
	select @max = isnull(max(id),0) from dbo.Test;

	insert into dbo.Test
		select id + @max from dbo.Test;
		
	set @i = @i + 1;
end;

--另外一个窗口执行
select state, state_description, count(*) as 'RowGroup Count'
from sys.column_store_row_groups
where object_id = object_id('test')
group by state, state_description
order by state;

  另外你还可能看到总共下面4种状态：OPEN/CLOSED/COMPRESSED/TOMBSTONE：

  第一个导入命令执行了46秒，第二个导入6600万数据的命令在本机上用了13分钟 ，在执行完毕之后，再次使用上面命令检查行组信息，可以看到1一个OPEN状态，63个是CLOSED状态，也就是说还是Delta Store。也就是说几乎所有的数据还是再B-Tree结构即行存储结构上。这将很难达到列存储所能达到的I/O性能。

Tuple Mover

简介

  前面提到过，Tuple Mover是一个后台进程，默认每5分钟自动运行一次，它会检查已关闭的Delta Store然后转换成片段。Delta Store这个咋一看上去并没有什么问题，但是请想象一下，如果本身就是行存储（没有压缩的堆表，它自己并不是索引，同时最大只有1048576行），那跟传统索引有什么不同？而且还要额外加一步转换成片段，开销自然就会上升，前面说过转换过程不仅资源利用率会上升，而且会锁住对应的行组直到转换完成，这个过程不阻塞读但是阻塞增删改。这对常规的ETL工作而言是比较大的挑战。
  很多环境下，确实没有办法总能产生10~100万每批的理想导入量。那么这个时候最好的方式就是先进入行存储模式（堆或者b-tree），然后创建聚集列存储索引。
  不用担心创建聚集列存储索引会产生非常大的日志增长，因为CREATE INDEX使用最小日志记录的方式，而且即使是完整日志模式，聚集列存储索引不会产生大量的一行一行的日志。

运行模式

  Tuple Mover在Delta Store存够1048576行数据之后，会自动或者被动发起转换，在索引重建过程是不启动的。同时要注意，也是我所在项目遇到的问题——宽行表（也就是表有很多列）会导致转换非常耗时，因为不能从并行运行中获益。细节一点来说，在两个行组被压缩之间，会有15秒的空隙，这个时间并不用来等待某些资源，那究竟发生了什么事呢？

1. 同一时间只压缩每个表的一个行组（假设同时又多个表需要压缩）
2. 完成后进入休眠。
3. 开始第一步中的下一个行组
4. 再进入休眠
5. 重复上面步骤直到全部完成。

  Tuple Mover不会再大量的Bulk insert时持续跟踪，所以合理的情况是直接插入到片段也就是已压缩的格式，或者通过索引重建或重组来触发。Tuple Mover的设计初衷更多的是针对普通的insert命令（INSERT INTO … SELECT除外）。

建议

  如果插入后又大量的delta store，TUple Mover只会大概每分钟处理5~6个片段，有时候甚至更少。如果索引有成百上千的CLOSED delta store，就需要使用REBUILD操作。再这种情况下处理时间还是非常大的，有时候可以达到小时级别。
  但是如果后续需要大量查询，这种时间还是必要的，毕竟数百个delta store并不能真正使用到列存储的优点(压缩，仅针对列，片段消除)，同时数据也会以“假”的列模式运行，因为在执行过程中delta store是不能被识别的。
  那么如果真的出现大量的Delta Store的时候该怎么做呢？最好的方式是rebuild，因为这样会创建全局的Dictionary （global dictionary）。次优的方法是REORGANIZE 索引，这个操作会使用所有可用的资源来加快速度。
  这一步可以在导入过程中运行：

select state, state_description, count(*) as 'RowGroup Count'
from sys.column_store_row_groups
where object_id = object_id('Test')
group by state, state_description
order by state;

waitfor delay '00:01:00';

select state, state_description, count(*) as 'RowGroup Count'
from sys.column_store_row_groups
where object_id = object_id('Test')
group by state, state_description
order by state;

  结果可以看到第一次只有2个Row Groups是Compressed的，但是1分钟之后，已经有8个了。由于表非常小，转换可以瞬间完成，同时由于在启动执行到执行结束实际上并非真的是60秒整，所以这个过程实际上是6个而不是60秒/15秒每个=4个这种简单的计算，如果你不相信，可以在导入数据过程中多执行几次。

应对方案

  前面说到，如果用单纯的Insert来导入数据，时间开销最多的部分往往在Tuple Mover对Delta Store的压缩转换。对此，可以对聚集列存储索引进行重建和重组。它们可以运行在分区级别，同时可以两个Tuple Mover一起进行（自动和手动）。
  说到这里，大概应该也知道为什么我的项目导入慢，其实问题很简单：

1. 列太多：普遍4~500列。
2. 导入方式：外部导入使用某商业软件，拼接每行数据成为一条insert into xx values()，然后以1000行一个批提交，delta store非常多。

  通过使用BCP命令，可以从大概50000行/秒提升到13万行/秒。可见对于数据行多的表，以传统的insert into并不高效。

  前面多次提到Dictionary， 那么下一章我们来看看这个是什么东西。
  下一篇：SQL Server 列存储索引性能总结（8）——列存储中的Dictionary