SQL Server 效能 — File I/O
這篇文章主要針對 File I/O 下列議題進行討論
1. 如何知道 SQL Server 有潛在的 File I/O 瓶頸？
2. 哪一個資料庫存取造成最多的 File I/O read/writes?
3. 什麼原因可能造成 File I/O ?
對於 SQL Server 來說 memory 多多益善，
因為 Database 到 memory 存取資料跟到 Disk I/O 存取資料，
到 Disk I/O 存取資料不管是讀取效能、等待時間等，都是比較耗時的。
但是除非 memory 大到可以把整個資料庫檔案放到記憶體中，不然磁碟的存取是必須的
例如： page in/out 從記憶體到磁碟的資料移轉
例如：TempDB 存取暫時性的資料、排序等
例如： Committed or lazy write 的時候，資料必須要寫回磁碟
如何 知道SQL Server 有潛在的 Disk I/O 瓶頸呢?
可以使用 Performance Monitor counters
Performance Monitor counters 有些與 Disk I/O 重要的指標，說明如下
- Avg. Disk Queue Length: 表示有多少的 disk reads or writes request在等待，如果該值 Avg. Disk Queue Length > 2 表示有 Disk I/O 瓶頸
- Avg. Disk Sec/Read: 平均每個讀取花多少時間? 通常來說應該要低於 20 ms ，如果高於 50 ms 則表示有嚴重的 Disk I/O 問題
- Physical Disk: %Disk Time: read or write requests佔整體的時間，若高於 50%也表示有潛在的 Disk I/O 問題
如果是 RAID 的情況下，這些建議值會有些不同，
建議平常定期做資料的收集，以便建立 baseline 比較的基準，未來有異常的狀況、
過低或是過高的觀察值出現時，才能夠有相互對造的基礎可以比較。
2. 哪一個資料庫存取造成最多 Disk I/O?
要知道這樣的資訊，我們必須利用 SQL Server 的 DMV “dm_io_virtual_file_stats”
每一次磁碟的存取的資訊都會被統計存放在 dm_io_virtual_file_stats
該 DMV 存放自上次 SQL Server restart 到目前累積的資訊
因此，先確認該資訊累積的期間具有一定的代表性，涵蓋主要的 work load
dm_io_virtual_file_stats 與 sys.master_files
就可以把相關的 Disk I/O 與 database Name、File Name 等資訊 query
接著就可以針對該資料庫、該資料庫所處的檔案位置 C:\ or D:\
等等做進一步的分析與調整
3. 那些原因可能造成 Disk I/O?
- 記憶體 Buffer pool 的不足，造成許多資料在記憶體與磁碟間的 page in/out
- Index or Table Scan 由於需要對整個資料表 or index 進行每一筆掃描，造成許多的磁碟讀取
- 沒有效率的 Index
- Poorly Design Query
- Index Fragmentation
這些都有可能導致 Disk I/O 的效能瓶頸
因此，SQL Server performance tuning 比較難進行頭痛醫頭，腳痛醫腳
SQL Server performance tuning 更需要全面性的資訊綜合的考量，
定期調教與不斷觀察的過程
總結：
這篇文章我們主要討論
1. 如何知道 SQL Server 有潛在的 File I/O 瓶頸？
透過定期的 Performance counters資訊收集，
Avg. Disk Queue Length > 2 或是 Avg. Disk Sec/Read > 50 ms
都會告訴我們有潛在的效能瓶頸
2. 哪一個資料庫存取造成最多的 File I/O read/writes?
利用 dm_io_virtual_file_stats 與 sys.master_files 可以讓我們知道更進一步的資訊
哪一個 database ，哪一個 database file，哪一個磁碟，耗費了多少 disk reads/writes等
3. 什麼原因可能造成 File I/O ?
我們收集完這些健診的資訊之後，如何對症下藥需要綜合性的考量
- 有可能 Memory Buffer Pool 的瓶頸導致、
- 有可能是 Index 沒有效率、
- 有可能為 Table Scan等導致這些問題
- 可可能為 index Fragmentation
因此，再進一步分析將有助於找出 Disk I/O 的癥結點，
讓 SQL Server 效能得以提升