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

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

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

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

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

 

Step 1: 資料更新

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

 

Step 2: 從磁碟讀取資料

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

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

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

 

 

Step 3: Transaction Log (ldf)

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

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

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

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

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

 

Step 4: 將更新資料寫入磁碟

更新的資料會被暫時保存在 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 時間。

 

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

 

SQL DataFile Transactionaa

 

Leave a Reply

Your email address will not be published.