【正文】 記錄從日志中解析出來(lái)的更新歷史信息。一段CDC的評(píng)估腳本為了評(píng)估CDC功能，我特地寫(xiě)了一段腳本如下：首先創(chuàng)建一個(gè)測(cè)試數(shù)據(jù)庫(kù)；然后激活TestCDC數(shù)據(jù)庫(kù)上的更新捕獲功能；USETestCDCGOEXECsp_cdc_enable_db_change_date_capture。GO執(zhí)行了存儲(chǔ)過(guò)程sp_cdc_enable_db_change_data_capture后，就會(huì)在數(shù)據(jù)庫(kù)TestCDC中看到有一些新的表被創(chuàng)建了，分別是ddl_history，change_tables，captured_columns，index_columns和lsn_time_mapping，并且這5張表都是在cdc架構(gòu)下。然后在TestCDC數(shù)據(jù)庫(kù)中創(chuàng)建測(cè)試表USETestCDCGOCREATETABLE(ProductIDintPRIMARYKEYNOTNULL,ProductNamenvarchar(100),Categorynvarchar(50))GOEXECsp_cdc_enable_table_change_data_capture39。dbo39。,39。Product39。,@role_name=NULL,@supports_net_changes=1。成功提交上述命令后，就可以在數(shù)據(jù)表change_tables，captured_columns和index_columns表中看到相應(yīng)的記錄，其中change_table中一條，capture_column中三條，index_columns中一條。同時(shí)cdc架構(gòu)下有增加了一張新表叫做dbo_Product_CT，這張表的結(jié)構(gòu)和Product表的結(jié)構(gòu)有點(diǎn)相似，Product表中的三列在dbo_Product_CT中都有，同時(shí)dbo_Product_CT表中還增加了_$start_lsn，_$end_lsn，_$seqval，_$operation和_$update_mask五個(gè)新的字段。hU:iB%P%Bamp。X其實(shí)在存儲(chǔ)過(guò)程sp_cdc_enable_table_change_data_capture中有一系列的參數(shù)，在這里我們?yōu)榱撕?jiǎn)化忽略了一個(gè)參數(shù)就是@captured_column_list，這個(gè)參數(shù)可以對(duì)表中特定的某些字段啟用更新跟蹤。在Product表上提交INSERT語(yǔ)句INSERTINTOVALUES(1,N39。ABC39。,N39。A39。)。提交完了這條命令后，就會(huì)在lsn_time_mapping和dbo_Product_CT中分別看到一條新記錄。其中dbo_Product_CT表中的_$operation字段的值是2，_$update_mask字段的值是0x07。_$operation字段是代表DML操作類型，1是delete，2是insert，3是update的舊值，4是update的新值。$update_mask字段是表示一個(gè)字段列表的掩碼，那些在DML操作中被更新了的字段位為1，而沒(méi)有更新的字段位為0。在本例中Product表一共有三列被跟蹤，所以應(yīng)該是一個(gè)三位的二進(jìn)制數(shù)，右邊低位第一位是第一列ProductID，低位第二位是第二列ProductName，第三位就是Category了。因?yàn)檫@是一次INSERT，所以更新涉及到了所有的三列，所以_$update_mask字段就應(yīng)該是0x7了。繼續(xù)在Product表上提交UPDATE語(yǔ)句UPDATESETCategory=N39。B39。WHEREProductID=1。提交完這條命令后，當(dāng)然也會(huì)在lsn_time_mapping和dbo_Product_CT中看到新記錄了。不過(guò)這次lsn_time_mapping中是一條，而dbo_Product_CT中則是兩條。(為什么會(huì)這樣呢？建議大家自己試一下咯，一試就明白了。)其中dbo_Product_CT表中的_$operation字段的值是第一條是3，第二條是4，_$update_mask字段的值兩條都是0x04。在這次操作中我們更新的是第三列，所以_$update_mask字段就應(yīng)該是0x4了。(如果我們更新的是ProductID會(huì)發(fā)現(xiàn)_$update_mask并非是0x1，而同樣是0x7，這估計(jì)是因?yàn)镻roductID是主鍵，更新主鍵應(yīng)該視同一條新的記錄。)再來(lái)一次UPDATEUPDATESETCategory=N39。A39。WHEREProductID=1。提交完這條命令后，在dbo_Product_CT中又看到兩條新記錄了。其中dbo_Product_CT表中的_$operation字段的值是第一條是3，第二條是4，_$update_mask字段的值兩條都是0x04。(看來(lái)CDC確實(shí)會(huì)記錄下數(shù)據(jù)的每次修改。)繼續(xù)在Product表上提交DML語(yǔ)句DELETEWHEREProductID=1。提交完了這條命令后，就會(huì)在lsn_time_mapping和dbo_Product_CT中分別看到一條新記錄。其中dbo_Product_CT表中的_$operation字段的值是1，_$update_mask字段的值是0x07。提交一個(gè)DDL試試看ALTERTABLEADDDescriptionnvarchar(100)。提交完這句命令后，只會(huì)在ddl_history表中看到一條新的記錄。然后再試試DMLUPDATESETDescription=N39。NA39。提交完這句語(yǔ)句后，所有cdc架構(gòu)下的表中都沒(méi)有看到新記錄。說(shuō)明新增的列Description不跟蹤更新了......估計(jì)有人會(huì)說(shuō)(細(xì)心的人哦！)：“這次當(dāng)然看不到新記錄了，因?yàn)樵谇懊娴?步我們已經(jīng)刪除了所有的記錄，因此這次的UPDATE語(yǔ)句沒(méi)有影響到任何記錄，當(dāng)然CDC的表中不會(huì)有任何記錄了。”那么到底對(duì)Description更新會(huì)不會(huì)記錄呢，經(jīng)過(guò)測(cè)試確實(shí)是不記錄的。那么如果我們想對(duì)Description也進(jìn)行更新跟蹤應(yīng)該怎么辦呢？很簡(jiǎn)單的，由另外一個(gè)存儲(chǔ)過(guò)程叫做sp_cdc_disable_table_change_data_capture可以禁用對(duì)某張表的更新跟蹤，可以使用這個(gè)存儲(chǔ)過(guò)程先對(duì)Product表禁用更新跟蹤，然后再重新啟用對(duì)Product表的更新跟蹤就可以了。1最后試一下DROP命令DROPTABLE12.評(píng)估結(jié)束。一定有人問(wèn)，捕獲到的更新怎么用呢，還有一堆系統(tǒng)函數(shù)和存儲(chǔ)過(guò)程可以幫助用戶，但是那段測(cè)試的過(guò)程就不詳細(xì)寫(xiě)了。，這兩個(gè)函數(shù)可以幫助我們獲取dbo_Product_CT表中數(shù)據(jù)，，在精簡(jiǎn)的更新中有一些重復(fù)的更新就會(huì)被合并成一條記錄，比如說(shuō)我們把產(chǎn)品類型由A改為B，然后又改回A。兩個(gè)函數(shù)的范例如下(你會(huì)發(fā)現(xiàn)精簡(jiǎn)結(jié)果集的函數(shù)運(yùn)算相當(dāng)慢，至少在CTP4中是這樣的，不知道以后的版本會(huì)不回有改進(jìn))：SELECT*FROM(0x00000048000001760004,0x00000048000001F70004,39。all39。)。SELECT*FROM(0x00000048000001760004,0x00000048000001F70004,39。all39。)。CDC功能的IO開(kāi)銷很明顯，CDC功能是會(huì)產(chǎn)生一定的IO和存儲(chǔ)開(kāi)銷的，為了評(píng)估CDC功能產(chǎn)生的這些開(kāi)銷。我又進(jìn)行了一段評(píng)測(cè)。整個(gè)評(píng)估的思路是這樣的：創(chuàng)建兩個(gè)數(shù)據(jù)庫(kù)在兩個(gè)數(shù)據(jù)庫(kù)中分別創(chuàng)建一張結(jié)構(gòu)完全相同的表，一個(gè)數(shù)據(jù)庫(kù)啟用CDC功能，而另外一個(gè)禁用CDC功能向兩張表中寫(xiě)入相同行數(shù)的數(shù)據(jù)利用sysindexes來(lái)獲得兩個(gè)數(shù)據(jù)庫(kù)中數(shù)據(jù)表的存儲(chǔ)消耗情況因?yàn)槭窃谔摂M機(jī)中進(jìn)行的測(cè)試，所以選取了比較小的數(shù)據(jù)表(AdventureWorks數(shù)據(jù)庫(kù)中的SalesOrderDetails)，大約有12萬(wàn)行數(shù)據(jù)。評(píng)估的結(jié)果如下：圖2從上面兩張表中可以看到，CDC激活后日志文件的讀會(huì)顯著增加。原因是CDC在寫(xiě)更新跟蹤表時(shí)，需要讀取日志。圖3圖4從上面兩張表中可以看到激活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)，SQLServer在這張表上使用_$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)銷。，這里簡(jiǎn)稱為表1和表2。下面是對(duì)表1和表2作的一些較為深入地剖析：表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)橥ǔＲ粡埍硇枰O(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é)。表1和表2的索引(這個(gè)不太好估算，因?yàn)椴煌谋砭鄞厮饕逆I值密度是不一樣的，一般按照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é)。對(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)境：原始數(shù)據(jù)表的聚簇索引為1個(gè)整型字段，同時(shí)需要監(jiān)控的字段總尺寸為50字節(jié)(約為5個(gè)decimal(19)或5個(gè)char(10))。對(duì)原始表提交100,000個(gè)事務(wù)。產(chǎn)生1,000,000行次數(shù)據(jù)操作，其中UPDATE占60%，INSERT和DELETE占40%。那么最終CDC產(chǎn)生的額外數(shù)據(jù)存儲(chǔ)空間應(yīng)該為(39+4+50)*(1000000*+1000000*)+48*100000=153,600,000個(gè)字節(jié)，約為164MB(假定數(shù)據(jù)頁(yè)填充率為90%)。因此約合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的建議，經(jīng)過(guò)以上測(cè)試，我們可以發(fā)現(xiàn)以下情況：◆CDC激活會(huì)顯著增加日志文件的讀操作?！鬋DC激活后更新跟蹤表會(huì)產(chǎn)生額外的寫(xiě)入，并消耗存儲(chǔ)空間。◆CDC激活后，原數(shù)據(jù)表的聚簇索引尺寸會(huì)影響到CDC產(chǎn)生的IO數(shù)據(jù)量，而原始數(shù)據(jù)表上的非聚簇索引則不會(huì)?！鬋DC激活后，被選定進(jìn)行更新跟蹤的列鍵值屬性同樣會(huì)影響到CDC產(chǎn)生的IO數(shù)據(jù)量和存儲(chǔ)空間。因此如同微軟建議的一樣，在CDC激活的環(huán)境下，應(yīng)該將更新跟蹤表寫(xiě)入與原始表不同的文件組并存放在不同的存儲(chǔ)設(shè)備上，注意控制需要監(jiān)控的數(shù)據(jù)列尺寸，同時(shí)應(yīng)該注意為日志文件選取可提高讀取性能的存儲(chǔ)硬件上，比如RAID10。 專業(yè)整理分享