浅析SQL2008的Change Data Capture功能

 

　　CDC功能的IO开销

　　很明显，CDC功能是会产生一定的IO和存储开销的，为了评估CDC功能产生的这些开销。我又进行了一段评测。

　　整个评估的思路是这样的：

　　1、创建两个数据库
　　2、在两个数据库中分别创建一张结构完全相同的表，一个数据库启用CDC功能，而另外一个禁用CDC功能
　　3、向两张表中写入相同行数的数据
　　4、视图sys.dm_io_virtual_file_stats来获得两个数据库文件上的
　　5、利用sysindexes来获得两个数据库中数据表的存储消耗情况

　　因为是在虚拟机中进行的测试，所以选取了比较小的数据表(AdventureWorks数据库中的SalesOrderDetails)，大约有12万行数据。

　　评估的结果如下：

图2

　　从上面两张表中可以看到，CDC激活后日志文件的读会显著增加。原因是CDC在写更新跟踪表时，需要读取日志。

图3

图4

　　从上面两张表中可以看到激活CDC后数据文件的写入和日志文件写入都会显著增加，不过需要考虑到CDC激活后会需要多写一张表，在本例中就是dbo_SalesOrderDetails_CT，所以这种增加是可以理解的。当然在生产环境中并不会对数据表的所有列进行CDC监控，所以激活CDC对IO写入的影响还需要针对不同情况进行分析。

图5

　　从上面这张图可以看出，CDC激活后会生产数据表不会消耗更多的存储空间，但是更新跟踪表会需要俄外的存储空间。另外可以发现的一点是，在本例中dbo_SalesOrderDetail_CT表消耗的空间比SalesOrderDetail表多，这是因为在dbo_SalesOrderDetail_CT表中加入了一些额外的字段，例如_$start_lsn和_$end_lsn，同时注意观察dbo_SalesOrderDetail_CT表会发现，SQL Server在这张表上使用_$start_lsn、_$end_lsn和_$seqval三个字段作为聚簇索引，而SalesOrderDetail表上原来的聚簇索引(SalesOrderID，SalesOrderDetailID)再加上_$start_lsn、_$end_lsn和_$seqval三个字段则被创建为一个非聚簇索引，所以这就导致了dbo_SalesOrderDetail_CT表需要消耗比原始表更多的空间，不过原始数据表上的非聚簇索引不会在CDC跟踪表上被创建，这也就说明了原始数据表聚簇索引的大小也会对CDC引发的IO产生影响。

　　CDC对存储的消耗

　　为了进一步理解CDC功能对存储的消耗，特别整理了一下CDC的数据开销。首先CDC功能对数据库存储空间产生显著影响的两张表是cdc._CT表和cdc.lsn_time_mapping表，这里简称为表1和表2。

　　下面是对表1和表2作的一些较为深入地剖析：

　　1、表1和表2的数据

　　表1主要由3个binary(10)字段、1个int字段、1个varbinary(128)字段以及所有被选定更新跟踪的原始表字段构成。因此表1每行数据的尺寸大概是在30 4 5 (因为通常一张表需要监控的字段会在16个以内，所以暂定为2bytes的binary然后加上varbinary数据2个bytes的固定开销)，也就是 39 x(假定原始表需要监控的字段键总尺寸为x个字节)个字节。

　　表2则有1个binary(10)字段、2个datetime字段和1个varbinary(10)字段构成。因此表2每行数据应该是20 16 12 = 48个字节。

　　2、表1和表2的索引(这个不太好估算，因为不同的表聚簇索引的键值密度是不一样的，一般按照1/4的数据尺寸估算，只有多没有少啦！)

　　表1的3个binary(10)字段构成了聚簇索引，同时3个binary(10)字段加上原始数据表的聚簇索引构成一个非聚簇索引，同上面一样，我们假定原始表聚簇索引键是x个字节，那么表1的非聚簇索引每行是(30 y(假定原始表聚簇索引键尺寸为y个字节) 4(指向聚簇索引的内部指针))个字节。

　　而表2中的binary(10)字段构成了聚簇索引，其中1个datetime字段构成了非聚簇索引。因此表2的非聚簇索引每行是8 4 = 12个字节。

　　3、对原始数据表的一行数据进行UPDATE操作，会在表1中添加2行数据，而DELETE操作和INSERT操作则会增加1行数据；而对于表2则是每笔事务增加1行数据。

　　因此我们作如下假定，典型的OLTP环境：

　　1、原始数据表的聚簇索引为1个整型字段，同时需要监控的字段总尺寸为50字节(约为5个decimal(19)或5个char(10))。
　　2、对原始表提交100,000个事务。
　　3、产生1,000,000行次数据操作，其中UPDATE占60%，INSERT和DELETE占40%。
　　4、那么最终CDC产生的额外数据存储空间应该为(39 4 50) * (1000000 * 1.2 1000000 * 0.4) 48 * 100000 = 153,600,000个字节，约为164MB(假定数据页填充率为90%)。
　　5、因此约合200MB左右。

　　经过这样的对比我们可以知道，CDC在生产环境特别是OLTP环境对存储空间的影响不算太明显的，当然这个还要取决于DBA在原始数据表上选取多少字段进行监控，以及这些字段的数据尺寸，同时还有原始数据表的聚簇索引键值密度。另外需要说明的是表1和表2都是由一个异步的进程通过读取日志来完成的，因此表1和表2的数据刷新和原始数据表的刷新会有一定的延时。

　　对部署CDC的建议ITPUB个人空间，经过以上测试，我们可以发现以下情况：

　　◆CDC激活会显著增加日志文件的读操作。
　　◆CDC激活后更新跟踪表会产生额外的写入，并消耗存储空间。
　　◆CDC激活后，原数据表的聚簇索引尺寸会影响到CDC产生的IO数据量，而原始数据表上的非聚簇索引则不会。
　　◆CDC激活后，被选定进行更新跟踪的列键值属性同样会影响到CDC产生的IO数据量和存储空间。ITPUB个人空间

　　因此如同微软建议的一样，在CDC激活的环境下，应该将更新跟踪表写入与原始表不同的文件组并存放在不同的存储设备上，注意控制需要监控的数据列尺寸，同时应该注意为日志文件选取可提高读取性能的存储硬件上，比如RAID10。

第1页:浅析SQL2008的Change Data Capture功能(1)   第2页:浅析SQL2008的Change Data Capture功能(2)