探究SQL Server 將交易紀錄寫入Tranaction Log的時間點

2014-09-22

2459
0
2014-09-23

緣由 :

某日閱讀書中關於SQL鏡像運作原理時，書中說明Principal在將交易紀錄由Memory中的Buffer寫入到實體的LDF檔時，另一個執行序同時會將交易紀錄傳送給Mirror端，一旦Mirror端也將該交易紀錄寫入自己的LDF檔後才算完成Principal端的整個交易(Mirroring的同步模式)。

看到上述的文章內容後，我想起百敬老師曾說過交易紀錄在Memory中會保留一塊Cache，所有交易紀錄會先放在該區域然後才寫入LDF實體檔案中。但關於該Cache的大小及寫入的模式，怎麼想都想不起來，好不容易爬到一篇文章就是討論這一方面的內容。參考他的文章後，自己也來實作看看並將過程記錄下來，以免日後又忘了。

此次實驗我們會利用Process Monitor來觀察LDF檔被寫入的狀況，大家可以先到http://technet.microsoft.com/zh-tw/sysinternals/bb896645 下載檔案。

實驗開始嘍

步驟一 : 如下圖我們直接寫入一筆資料到一個資料表中。

步驟二 : 如下圖，根據Process Monitor擷取到的資料，我們可以看見一筆WriteFile事件寫入到E:\SQLLOG\MyDB_log.ldf檔案中，寫入長度為4096 Byte(4KB)。

步驟三 : 這一次我們直接寫入十筆資料到該資料表中，看看Process Monitor會擷取到哪些資料呢。

步驟四 : Process Monitor擷取到10筆WriteFile事件寫入到E:\SQLLOG\MyDB_log.ldf檔案中，每一筆寫入長度都是4096 Byte(4KB)。

步驟五 : 這一次我們故意將Insert的動作包到交易中，且故意不Commit。

步驟六 : 針對沒有Commit的那一筆交易，我們可以發現LDF並沒有被寫入的紀錄。

步驟七: 此時我們回去將剛剛那一筆交易Commit掉。

步驟八: 會發現Commit掉後的交易被寫入LDF檔了。

小結 :

由上述八個步驟我們可以瞭解，只要是Commit的交易都會被寫入到LDF檔中。

步驟九 : 剛剛我們都只是針對單筆交易觀察，接下來對於批次交易，SQL又是如何處理Tranaction Log的寫入呢，如下圖所示，我們在一筆交易中寫入600筆資料。

步驟十 : 如下圖所示Process Monitor只擷取到2筆寫入LDF動作，第一筆寫了61440 Byte(60KB)，而第二筆寫入16384 Bytes(16KB)，所以我們大概可以知道該Cache的大小應該就是60KB，一旦到達60KB就算交易還沒結束，SQL也會強制將Cache中所有交易紀錄寫入到LDF中。

步驟十一 : 這一次我們故意將剛剛步驟九的那個長交易不Commit掉，此時應該只會有一筆寫入動作(因為Cache滿了而被強制寫入的那60KB)，來看看我們的推測是否正確吧。

步驟十二 : 沒錯 ! 如下圖所示，果然只有一筆60KB的寫入紀錄。

步驟十三 : 回去Commit掉剛剛的交易吧。

步驟十四 : 我們在步驟十三Commit掉長交易後，又多了一筆16KB被寫入LDF檔了。

小結 :

由上述步驟九到步驟十四實驗，我們可以知道該Cache的容量大小為60KB。一旦Cache滿了，即使交易尚未結束，SQL也會將Cache中的資料寫入LDF中。

步驟十五 : 此時我突然想到，萬一還有交易紀錄是在Cache中尚未寫進LDF，此時發生Checkpoint事件，所有Dirty Page都被寫入MDF檔，然後SQL Server突然Crush掉。那會不會因為還有尚未寫入LDF的交易紀錄遺失，導致SQL無法利用LDF正確的Undo交易回來呢 ?

因此我繼續利用剛剛的長交易且故意不Commit交易，讓Cache中殘存本次交易中的部分交易紀錄。

步驟十六 : 步驟十五沒Commit的長交易因為60KB的Cache滿了，所以有一筆60KB寫入LDF的紀錄，但根據剛剛的實驗我們知道本交易應該還有16KB的交易紀錄還存放在Cache中尚未寫入LDF檔案內。

步驟十七 : 我們開另一條Session執行Checkpoint動作看看Process Monitor會擷取到哪些資料呢。

步驟十八 : 如下圖紅色圈選處，我們可以發現當Checkpoint事件發生時，Cache中的所有交易紀錄也會被寫入到LDF檔案中，即便那些交易還沒有Commit掉。且多了四筆紀錄Checkpoint的事件寫入。

小結 :

當Checkpoint事件發生時，即使交易尚未結束，SQL也會將Cache中的資料寫入LDF中。

步驟十九 : SQL2014有新增一個叫做Delayed Durability的功能，其目的就是降低Cache寫入到LDF的IO數，大量的IO會影響到SQL的效能。我們可以從步驟一到八的實驗中知道，當我們不是用批次的方式寫入資料到資料表中。而是用大量的單筆交易來塞入資料，那每一筆資料完成交易就會IO一次LDF檔，這是很耗效能的。而Delayed Durability的功能就是要降低LDF檔的IO數，會累積一定的交易紀錄後在一次寫入到LDF檔中來加快速度。當然這樣的做法就是會增加資料不一致的風險嘍。