淺析SQL2008的Change Data Capture功能(4)_Mssql數(shù)據(jù)庫(kù)教程

推薦：如何使用SQL Server嵌套子查詢
很多SQL Server程序員對(duì)子查詢（subqueries）的使用感到困惑，尤其對(duì)于嵌套子查詢（即子查詢中包含一個(gè)子查詢）。現(xiàn)在，就讓我們追本溯源地探究這個(gè)問(wèn)題。 有兩種子查詢類型：標(biāo)準(zhǔn)和相關(guān)。標(biāo)

從上面兩張表中可以看到激活CDC后數(shù)據(jù)文件的寫(xiě)入和日志文件寫(xiě)入都會(huì)顯著增加，不過(guò)需要考慮到CDC激活后會(huì)需要多寫(xiě)一張表，在本例中就是dbo_SalesOrderDetails_CT，所以這種增加是可以理解的。當(dāng)然在生產(chǎn)環(huán)境中并不會(huì)對(duì)數(shù)據(jù)表的所有列進(jìn)行CDC監(jiān)控，所以激活CDC對(duì)IO寫(xiě)入的影響還需要針對(duì)不同情況進(jìn)行分析。

圖5

從上面這張圖可以看出，CDC激活后會(huì)生產(chǎn)數(shù)據(jù)表不會(huì)消耗更多的存儲(chǔ)空間，但是更新跟蹤表會(huì)需要俄外的存儲(chǔ)空間。另外可以發(fā)現(xiàn)的一點(diǎn)是，在本例中dbo_SalesOrderDetail_CT表消耗的空間比SalesOrderDetail表多，這是因?yàn)樵赿bo_SalesOrderDetail_CT表中加入了一些額外的字段，例如_$start_lsn和_$end_lsn，同時(shí)注意觀察dbo_SalesOrderDetail_CT表會(huì)發(fā)現(xiàn)，SQL Server在這張表上使用_$start_lsn、_$end_lsn和_$seqval三個(gè)字段作為聚簇索引，而SalesOrderDetail表上原來(lái)的聚簇索引(SalesOrderID，SalesOrderDetailID)再加上_$start_lsn、_$end_lsn和_$seqval三個(gè)字段則被創(chuàng)建為一個(gè)非聚簇索引，所以這就導(dǎo)致了dbo_SalesOrderDetail_CT表需要消耗比原始表更多的空間，不過(guò)原始數(shù)據(jù)表上的非聚簇索引不會(huì)在CDC跟蹤表上被創(chuàng)建，這也就說(shuō)明了原始數(shù)據(jù)表聚簇索引的大小也會(huì)對(duì)CDC引發(fā)的IO產(chǎn)生影響。

CDC對(duì)存儲(chǔ)的消耗

為了進(jìn)一步理解CDC功能對(duì)存儲(chǔ)的消耗，特別整理了一下CDC的數(shù)據(jù)開(kāi)銷。首先CDC功能對(duì)數(shù)據(jù)庫(kù)存儲(chǔ)空間產(chǎn)生顯著影響的兩張表是cdc._CT表和cdc.lsn_time_mapping表，這里簡(jiǎn)稱為表1和表2。

下面是對(duì)表1和表2作的一些較為深入地剖析：

1、表1和表2的數(shù)據(jù)

表1主要由3個(gè)binary(10)字段、1個(gè)int字段、1個(gè)varbinary(128)字段以及所有被選定更新跟蹤的原始表字段構(gòu)成。因此表1每行數(shù)據(jù)的尺寸大概是在30 4 5 (因?yàn)橥ǔＲ粡埍硇枰�監(jiān)控的字段會(huì)在16個(gè)以內(nèi)，所以暫定為2bytes的binary然后加上varbinary數(shù)據(jù)2個(gè)bytes的固定開(kāi)銷)，也就是 39 x(假定原始表需要監(jiān)控的字段鍵總尺寸為x個(gè)字節(jié))個(gè)字節(jié)。

表2則有1個(gè)binary(10)字段、2個(gè)datetime字段和1個(gè)varbinary(10)字段構(gòu)成。因此表2每行數(shù)據(jù)應(yīng)該是20 16 12 = 48個(gè)字節(jié)。

2、表1和表2的索引(這個(gè)不太好估算，因?yàn)椴煌�的表聚簇索引的鍵值密度是不一樣的，一般按照1/4的數(shù)據(jù)尺寸估算，只有多沒(méi)有少啦！)

表1的3個(gè)binary(10)字段構(gòu)成了聚簇索引，同時(shí)3個(gè)binary(10)字段加上原始數(shù)據(jù)表的聚簇索引構(gòu)成一個(gè)非聚簇索引，同上面一樣，我們假定原始表聚簇索引鍵是x個(gè)字節(jié)，那么表1的非聚簇索引每行是(30 y(假定原始表聚簇索引鍵尺寸為y個(gè)字節(jié)) 4(指向聚簇索引的內(nèi)部指針))個(gè)字節(jié)。

而表2中的binary(10)字段構(gòu)成了聚簇索引，其中1個(gè)datetime字段構(gòu)成了非聚簇索引。因此表2的非聚簇索引每行是8 4 = 12個(gè)字節(jié)。

3、對(duì)原始數(shù)據(jù)表的一行數(shù)據(jù)進(jìn)行UPDATE操作，會(huì)在表1中添加2行數(shù)據(jù)，而DELETE操作和INSERT操作則會(huì)增加1行數(shù)據(jù)；而對(duì)于表2則是每筆事務(wù)增加1行數(shù)據(jù)。

因此我們作如下假定，典型的OLTP環(huán)境：

1、原始數(shù)據(jù)表的聚簇索引為1個(gè)整型字段，同時(shí)需要監(jiān)控的字段總尺寸為50字節(jié)(約為5個(gè)decimal(19)或5個(gè)char(10))。
2、對(duì)原始表提交100,000個(gè)事務(wù)。
3、產(chǎn)生1,000,000行次數(shù)據(jù)操作，其中UPDATE占60%，INSERT和DELETE占40%。
4、那么最終CDC產(chǎn)生的額外數(shù)據(jù)存儲(chǔ)空間應(yīng)該為(39 4 50) * (1000000 * 1.2 1000000 * 0.4) 48 * 100000 = 153,600,000個(gè)字節(jié)，約為164MB(假定數(shù)據(jù)頁(yè)填充率為90%)。
5、因此約合200MB左右。

經(jīng)過(guò)這樣的對(duì)比我們可以知道，CDC在生產(chǎn)環(huán)境特別是OLTP環(huán)境對(duì)存儲(chǔ)空間的影響不算太明顯的，當(dāng)然這個(gè)還要取決于DBA在原始數(shù)據(jù)表上選取多少字段進(jìn)行監(jiān)控，以及這些字段的數(shù)據(jù)尺寸，同時(shí)還有原始數(shù)據(jù)表的聚簇索引鍵值密度。另外需要說(shuō)明的是表1和表2都是由一個(gè)異步的進(jìn)程通過(guò)讀取日志來(lái)完成的，因此表1和表2的數(shù)據(jù)刷新和原始數(shù)據(jù)表的刷新會(huì)有一定的延時(shí)。

對(duì)部署CDC的建議ITPUB個(gè)人空間，經(jīng)過(guò)以上測(cè)試，我們可以發(fā)現(xiàn)以下情況：

◆CDC激活會(huì)顯著增加日志文件的讀操作。
◆CDC激活后更新跟蹤表會(huì)產(chǎn)生額外的寫(xiě)入，并消耗存儲(chǔ)空間。
◆CDC激活后，原數(shù)據(jù)表的聚簇索引尺寸會(huì)影響到CDC產(chǎn)生的IO數(shù)據(jù)量，而原始數(shù)據(jù)表上的非聚簇索引則不會(huì)。
◆CDC激活后，被選定進(jìn)行更新跟蹤的列鍵值屬性同樣會(huì)影響到CDC產(chǎn)生的IO數(shù)據(jù)量和存儲(chǔ)空間。ITPUB個(gè)人空間

因此如同微軟建議的一樣，在CDC激活的環(huán)境下，應(yīng)該將更新跟蹤表寫(xiě)入與原始表不同的文件組并存放在不同的存儲(chǔ)設(shè)備上，注意控制需要監(jiān)控的數(shù)據(jù)列尺寸，同時(shí)應(yīng)該注意為日志文件選取可提高讀取性能的存儲(chǔ)硬件上，比如RAID10。

分享：SQL多表格查詢合并至單一聲明的常用方式
在對(duì)跨多個(gè)表格的數(shù)據(jù)進(jìn)行組合時(shí)，有時(shí)很難搞清楚要使用哪一個(gè)SQL句法。我將在這里對(duì)將多個(gè)表格中的查詢合并至單一聲明中的常用方式進(jìn)行闡述。 在這篇文章中的樣本查詢符合SQL92 ISO標(biāo)準(zhǔn)。不