SQL Server Nuts 堅果的堅持 Day3 (Disk & Storage)
2022-10-05
SQL Server 堅果的堅持學習筆記系列，不中斷的連續學習，深入 SQL Server 核心知識 🥜
說明
Buffer Pool
SQL Server 不會直接從 Data files 取得資料頁 (data pages)，而是會以記憶體建構的 buffer pool 將所需的資料頁快取於 buffer pool。
Buffer 的結構包含 Data Page 的位址 (Address)、記憶體中資料頁的指標 (Pointer)、Page latching queue 以及狀態資訊 (status information)。
Latches
Latches 是 SQL Server 內部用於處理記憶體中的物件，避免在多個 Threads (Work) 對物件的異動 (Modification) 而受到汙染 (Corruption)。
最常見的 Latch 包含 Exclusive，排除其他的讀取與異動；Shared，允許同時的讀取但禁止異動。latches 與程式語言的 critical sections 或 mutexes 概念相似。
Logical Read & Physical Read
當 SQL Server 需要存取 Data Page 時，從 Buffer pool 取回 Data Page 的動作稱為 logical read，而不再記憶體當中須從載入 Buffer pool 的動作則稱為 Physical read。
當資料需要異動時，SQL Server 首先會將 log 寫入交易紀錄，接著異動 Buffer pool 當中的 Data Page，使資料變得 Dirty (被異動)。而當發生 CheckPoint 或 Lazy Writer 的時候，才會從 Buffer pool 將 Data Page 寫入資料檔。
預設上所有的 Schedulers 都會參與 I/O 的工作，而一件 I/O 需求，可能由不同的 Worker 所參與完成。
sys.dm_io_pending_io_requests 可以觀察
Data Reads
當 SQL Server 需要 Data Page 時，首先檢查 Buffer pool 是否存在需要的 Data Page，否則 Worker 會分配 Buffer 給需要的資料頁，並且以 Exclusive Latch 保護。
接著會發動 I/O 需求，並將需求加入到 Scheduler 的 I/O 佇列 (Queue)，同時發起 OS API 已進行讀取。同時在 Buffer 上加入 Shared Latch，而因為該 Buffer 正受 Excludsive Latch，此時 Worker 會進入 Suspend 狀態，處於 PAGEIOLATCH 等待。
當另一個 Worker 進入 RUNNING 狀態時，它會檢查 Scheduler 的 I/O Queue 是否仍有項目，如果沒有會藉由 callback function 來結束操作，callback function 會檢查 padge 沒有被其他操作汙染 (corrupted)，接著從 Buffer 中移除 exclusive latch。最後 Worker 會送出 I/O 需求，可以恢復及存取 Data Page。
Data Writes
當資料庫發生資料異動，SQL Server 會先異動 Buffer Pool 中的資料頁，並產生 Log 儲存在交易紀錄當中，而直到交易紀錄被完整記錄，資料異動的交易才算是完成。
當發生 CheckPoint 以及 lazy writer 的時候，SQL Server 才會將 Buffer pool 的資料寫回 Data Files。
CheckPoint 是為了減少資料庫復原時間，將記憶體的資料寫入 mdf；Lazy Writer 是為減少記憶體壓力，將記憶體的資料寫入 mdf。預設上 CheckPoint 不會清除記憶體中的資料，必須要手動清除：
CHECKPOINT
GO
DBCC DROPCLEANBUFFERS
GOsys.dm_io_virtual_file_stats view
從 DMV sys.dm_io_virtual_file_stats view 觀察 stalls (資料頁讀取的時間)
目標是越小的 stalls 時間越佳，根據經驗法則交易紀錄的寫入應小於 2 ms、資料檔的讀取則應小於 5ms。
當查詢所需的資料傳輸量越多 (throughput)，資料讀取的時間也會成長 (stalls)，而如果在高 stalls 卻是低 throughput 的情況，效能的問題可能不是在 SQL Server 上。而很高的 staslls 也會反映在 PAGGEIOLATCH 等待類型以及資料頁的短生命週期。
增加資料庫伺服器的記憶體，因為可以快取更多的資料頁，也增加資料頁在記憶體中的生命週期，能夠緩解 stalss 增加的問題。
下列的資料庫調校都有助於降低 stalls：
- 移除不必要的索引
- 藉由調整 FILLFACTOR 減少 page splits
- 索引維護策略的調整
- Data compression 減少資料頁數量
- 調整資料庫的 Schema
Performance Counter
使用 Windows 的 perfmon 觀察下列磁碟相關指標進行效能調校與效能議題除錯。
Physical disk
Avg Disk Queue Length
Avg Disk sec/Read
Avg Disk sec/Write
SQL Server: Buffer Manager
Checkpoint pages/sec
Background writer pages/sec
Lazy writer/sec
Page reads/sec
Page writes/sec
Readahead pages/sec
SQL Server: Databases
Log Bytes Flushed/sec
Log Flush Write Time (ms)
Log Flushes/sec
SQL Server: SQL Statistics
Batch Requests/sec
SQL Server: Databases
Transactions/sec
I/O Wait Types
ASYNC_IO_COMPLETION
等待類型發生於 SQL Server 等待從 Data files 的非同步的 I/O 讀取或寫入。
Regular checkpoint
進行資料庫備份或 DBCC CHECKDB 的 Internal checkpoint。
Reading GAM pages from data files
基於資料庫備份所發生的讀取 Data Pages。
IO_COMPLETION
等待類型發生於同步讀取或寫入資料檔，或者從交易紀錄進行讀取，例如：
- Reading allocation map pages from the database
- Reading the transaction log during database recovery
- Writing data to tempdb during sort spills
WRITELOG
等待類型發生於資料庫寫入交易紀錄，過高的比率表示交易紀錄的寫入發生瓶頸。
WRITE_COMPLETION
等待類型發生於同步寫入資料庫與交易紀錄，常見於 Database Snapshots，而在 DBCC CHECKDB 的時候也會產生 internal database snapshots。
PAGEIOLATCH
等待類型發生於 SQL Server 從 Data Files 讀取 Data Pages，大量的 PAGEIOLATCH 顯示資料庫經常處於從 Data Files 讀取 Data Pages，通常源自於：
- SQL Server 硬體資源不足，記憶體無法快取足夠的 Data Pages
- 未最佳化的查詢，造成 Buffer pool 在記憶體中不斷置換 Data Pages (因為記憶體有限)
增加記憶體是最容易解決 PAGEIOLATCH 的方式，但優化查詢 (Optimizing) 裁示解決的根本之道。
PAGEIOLATCH_EX
發生於 Worker 想要更新 Data Pages 並且在等待該 Page 從 Data files 被載入 Buffer pool。
PAGEIOLATCH_SH
發生於 Worker 想要讀取 Data Page，並且在等待該 Page 從 Data files 被載入 Buffer pool。
PAGEIOLATCH_UP
發生於 Worke 想要更新 System Page (例如 allocation map)，並且在等待 Page 從 Data files 被載入 Buffer pool。
Best Practice Checklist
藉由 sys.dm_io_virtual_file_stats 檢查儲存的延遲 (Latency & Stalls)
藉由分析 Performance Counters 以檢查高延遲是否源自於瞬間爆發 (Bursts) 的 I/O 活動 (例如 Checkpoint)
如果是 Checkpoint 造成 I/O 問題，可以藉由調整為 indirect checkpoints 的方式控制。
藉由觀察 WRITELOG 的等待類型，確認交易紀錄是否有效能上的問題。
藉由觀察 IO_COMPLETION 的等待類型，確認 tempdb 是否存在效能問題。
藉由觀察 PAGEIOLATCH 判斷是否有未最佳化的查詢。
細說優化設定
CheckPoints Tuning
在許多情況下，I/O 的延遲是來自於瞬間爆發的 I/O 需求，而 Checkpoint Process 的發生，是最常見的肇因。
Checkpoint 是為了將 Data Pages 儲存至 Data files，目的是為減少資料庫的還原時間 (failover / carsh)，因為可以減少還原時所需重新進行交易紀錄 (replay) 的時間。
可以藉由調整 recovery target 來增加 Checkpont 的發生頻率，從而減少瞬間爆發的問題。
Checkpoint 的發生類型與時機
- Internal
- 備份或建立資料庫 snapshot 時
- Manual
- 手動執行
CHECKPOINT - Automatic
- 由 SQL Server 內部機制判斷定期進行，可能會導致積累的 Dirty Page 一次寫入造成 I/O 問題
- Indirect
- 藉由設定資料庫的
TARGET_RECOVERY_TIME增加 Checkpoint 頻率
參考資料
SQL Server Advanced Troubleshooting and Performance Tuning: Best Practices and Techniques