資料庫資料檔與交易檔的讀取效能

這篇文章主要說說明當一筆資料被更新時，

資料庫如何對於 database data Files (ndf/mdf)與交易資料（ldf）檔案進行讀取與寫入的過程

進而了解該過程如何影響資料庫的效能與效能上的建議。

Step 1: 資料更新

當資料庫收到 Update 的指令的時候，會開始對於資料從記憶體與磁碟讀取並且更新

SQL 會判斷該讀取的資料是否可以在記憶體中 SQL buffer 取得，如果可以在記憶體中取得，則不需要到磁碟讀取。

如果記憶體中沒有所需的資料，則 SQL 就會到磁碟讀取，並且存放到記憶體中 SQL Buffer

資料庫會將更新的資料寫入記憶體，也就是 SQL Buffer，這時候由於資料僅在記憶體中 (SQL Buffer)被更新，尚未寫入磁碟，因此該資料狀態稱為 Dirty Page

資料庫會立刻紀錄一筆更新交易紀錄，每一個交易紀錄都會有一個循序的 LSN (Log Sequential Number)

當異常資料庫終止時，交易紀錄可以讓資料庫回復到原本的狀態。

當 “Commit”時，資料交易紀錄就會被寫入磁碟

由於交易紀錄的寫入較為頻繁，而寫讀取多半為 Sequential I/O

因此建議，Transaction log 可以存放在與 Database Data file 不同的實體磁碟

更新的資料會被暫時保存在 SQL Buffer 記憶體中，暫時還不會立刻寫入磁碟

一直等到 “CheckPoint”　的時間點，所有更新的資料會整批被寫入磁碟

這整個過程又稱為 Lazy Write 延遲讀寫，對於這樣的磁碟讀寫的動作通常為 Random I/O

為什麼 SQL Server 不立刻寫入資料到磁碟呢？

因為到磁碟讀取與寫入所耗費的資源與時間遠遠大於記憶體的存取

因此，為了較好的效能，SQL Server 會盡量在記憶體中 SQL Buffer 處理，

一直等到 checkPoint 時間點，才會以批次的方式整批將記憶體中 SQL Buffer 修改的資料，整批寫入磁碟檔案 (Ndf/MDF)

在了解整個 Database File 與交易黨存取與寫入的過程之後，資料庫的效能建議如下：

1. 建議將 Transaction Log (LDF)與 Database File (Mdf/NDF)實體的磁碟分開存放

2. LDF 可以放在磁碟效能較好的硬碟。因為交易紀錄檔的寫入較為頻繁，執行與每一筆交易有關。

3. 記憶體多多益善。當面臨昂貴的磁碟存儲設備時，增加記憶體是一個可以有效提升資料庫效能的方法。因為這可以讓資料庫許多的資料存取都在記憶體中完成，節省許多 Disk I/O 時間。

圖表來源：微軟資料庫效能調教課程