浅析SQL2008的Change Data Capture功能(4)_Mssql数据库教程

推荐：如何使用SQL Server嵌套子查询
很多SQL Server程序员对子查询（subqueries）的使用感到困惑，尤其对于嵌套子查询（即子查询中包含一个子查询）。现在，就让我们追本溯源地探究这个问题。 有两种子查询类型：标准和相关。标

从上面两张表中可以看到激活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。

分享：SQL多表格查询合并至单一声明的常用方式
在对跨多个表格的数据进行组合时，有时很难搞清楚要使用哪一个SQL句法。我将在这里对将多个表格中的查询合并至单一声明中的常用方式进行阐述。 在这篇文章中的样本查询符合SQL92 ISO标准。不