SQL Server 效能 — File I/O

SQL Server 效能 — File I/O 

ID-1007527

這篇文章主要針對 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

Perf Counter Disk

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

IO_Stall_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 效能得以提升

 

 

Leave a Reply

Your email address will not be published.