SQL Server日誌作用以及為什麼先寫日誌後寫資料？

今天在看Oracle的BackupGroundProcess，裡邊有一段是寫到為什麼先寫日誌後寫資料的：

LGWR, on the other hand, does lots of sequential writes to the redo log. This is an important distinction and one of the reasons that Oracle has a redo log and the LGWR process as well as the DBWn process. Scattered writes are significantly
slower than sequential writes. By having the SGA buffer dirty blocks and the LGWR process do large sequential writes that can re-create these dirty buffers, we achieve an increase in performance.

 

其實SQL Server也是一樣，每一個SQL Server的資料庫都會按照其修改資料(insert,update,delete)的順序將對應的日誌記錄到記錄檔.SQL Server使用了Write-Ahead logging技術來保證了交易記錄的原子性和持久性.而這項技術不僅僅保證了ACID中的原子性(A)和持久性(D),還大大減少了IO操作，把對資料的修改提交到磁碟的工作交給lazy-writer和checkpoint.

預寫式日誌（Write-Ahead Logging (WAL)）

SQL Server使用了WAL來確保了事務的原子性和持久性.實際上，不光是SQL Server,基本上主流的關聯式資料庫包括oracle,mysql,db2都使用了WAL技術.

WAL的核心思想是:在資料寫入到資料庫之前，先寫入到日誌.

因為對於資料的每筆修改都記錄在日誌中，所以將對於資料的修改即時寫入到磁碟並沒有太大意義，即使當SQL Server發生意外崩潰時，在恢複(recovery)過程中那些不該寫入已經寫入到磁碟的資料會被復原(RollBack),而那些應該寫入磁碟卻沒有寫入的資料會被重做(Redo)。從而保證了持久性(Durability)

但WAL不僅僅是保證了原子性和持久性。還會提高效能.

硬碟是通過旋轉來讀取資料,通過WAL技術，每次提交的修改資料的事務並不會馬上反映到資料庫中，而是先記錄到日誌.在隨後的CheckPoint和lazy Writer中一併提交,如果沒有WAL技術則需要每次提交資料時寫入資料庫:

而使用WAL合并寫入，會大大減少磁碟IO：

也許你會有疑問，那每次對於修改的資料還是會寫入記錄檔.同樣消耗磁碟IO。上篇文章講過，每一筆寫入日誌的記錄都是按照先後順序，給定順序編號的LSN進行寫入的，日誌只會寫入到記錄檔的邏輯末端。而不像資料那樣，可能會寫到磁碟的各個地方.所以，寫入日誌的開銷會比寫入資料的開銷小很多。

SQL Server修改資料的步驟

SQL Server對於資料的修改,會分為以下幾個步驟順序執行:

1.在SQL Server的緩衝區的日誌中寫入”Begin Tran”記錄

2.在SQL Server的緩衝區的日誌頁寫入要修改的資訊

3.在SQL Server的緩衝區將要修改的資料寫入資料頁

4.在SQL Server的緩衝區的日誌中寫入”Commit”記錄

5.將緩衝區的日誌寫入記錄檔

6.發送確認資訊(ACK)到用戶端(SMSS,ODBC等）

可以看到,交易記錄並不是一步步寫入磁碟.而是首先寫入緩衝區後，一次性寫入日誌到磁碟.這樣既能在日誌寫入磁碟這塊減少IO，還能保證日誌LSN的順序.

上面的步驟可以看出，即使事務已經到了Commit階段，也僅僅只是把緩衝區的日誌頁寫入日誌，並沒有把資料寫入資料庫.那將要修改的資料頁寫入資料庫是在何時發生的呢?

Lazy Writer和CheckPoint

上面提到，SQL Server修改資料的步驟中並沒有包含將資料實際寫入到磁碟的過程.實際上，將緩衝區內的頁寫入到磁碟是通過兩個過程中的一個實現：

這兩個過程分別為:

1.CheckPoint

2.Lazy Writer

任何在緩衝區被修改的頁都會被標記為“髒”頁。將這個髒頁寫入到資料磁碟就是CheckPoint或者Lazy Writer的工作.

當事務遇到Commit時，僅僅是將緩衝區的所有日誌頁寫入磁碟中的記錄檔:

而直到Lazy Writer或CheckPoint時，才真正將緩衝區的資料頁寫入磁碟檔案:

前面說過，記錄檔中的LSN號是可以比較的，如果LSN2>LSN1,則說明LSN2的發生時間晚於LSN1的發生時間。CheckPoint或Lazy Writer通過將記錄檔末尾的LSN號和緩衝區中資料檔案的LSN進行對比，只有緩衝區內LSN號小於記錄檔末尾的LSN號的資料才會被寫入到磁碟中的資料庫。因此確保了WAL（在資料寫入到資料庫之前，先寫入日誌)。

Lazy Writer和CheckPoint的區別

Lazy Writer和CheckPoint往往容易混淆。因為Lazy Writer和CheckPoint都是將緩衝區內的“髒”頁寫入到磁碟檔案當中。但這也僅僅是他們唯一的相同點了。

Lazy Writer存在的目的是對緩衝區進行管理。當緩衝區達到某一臨界值時，Lazy Writer會將緩衝區內的髒頁存入磁碟檔案中，而將未修改的頁釋放並回收資源。

而CheckPoint存在的意義是減少伺服器的恢復(Recovery Time).CheckPoint就像他的名字指示的那樣，是一個存檔點.CheckPoint會定期發生.來將緩衝區內的“髒”頁寫入磁碟。但不像Lazy Writer,Checkpoint對SQL Server的記憶體管理毫沒有興趣。所以CheckPoint也就意味著在這個點之前的所有修改都已經儲存到了磁碟.這裡要注意的是：CheckPoint會將所有緩衝區的髒頁寫入磁碟，不管髒頁中的資料是否已經Commit。這意味著有可能已經寫入磁碟的“髒頁”會在之後復原（RollBack).不過不用擔心，如果資料復原，SQL
Server會將緩衝區內的頁再次修改，並寫入磁碟。

通過CheckPoint的運作機制可以看出，CheckPoint的間歇(Recovery Interval)長短有可能會對效能產生影響。這個CheckPoint的間歇是一個伺服器層級的參數。可以通過sp_config進行配置，也可以在SSMS中進行配置:

恢複間歇的預設參數是0，意味著由SQL Server來管理這個回複間隔。而自己設定復原間隔也是需要根據具體情況來進行界定。更短的恢複間歇意味這更短的恢復和更多的磁碟IO，而更長的恢複間歇則帶來更少的磁碟IO佔用和更長的恢復.

SQL Server原理講解來源於：http://www.uml.org.cn/sjjm/201203021.asp