SQL Server I/O 瓶頸識別與磁碟子系統最佳實務：效能計數器、DMV、碎片整理與 RAID 規劃

I/O 子系統是 SQL Server 效能的命門。本文提供系統化的 I/O 瓶頸識別方法論：效能計數器 (PerfMon) 關鍵指標 → DMV 等待統計/虛擬檔案統計/掛起 I/O → 磁碟碎片影響 → RAID/儲存配置最佳實務，助您精準定位並解決 I/O 爭用。

一、效能計數器 (PerfMon) 關鍵指標

多磁碟修正： RAID 陣列下，Queue Length 需除以物理磁碟數。例：4 顆磁碟 RAID 10，Queue=10 → 平均 2.5，仍需關注。

RAID 級別 IOPS 估算公式

-- 讀寫比例假設：Reads=R, Writes=W
RAID 0:  IOPS/磁碟 = (R + W) / N
RAID 1:  IOPS/磁碟 = (R + 2W) / 2
RAID 5:  IOPS/磁碟 = (R + 4W) / N   -- 寫入懲罰 4x
RAID 10: IOPS/磁碟 = (R + 2W) / N   -- 寫入懲罰 2x

實測範例：RAID 1, R=90, W=75 → (90 + 2*75)/2 = 120 IOPS/磁碟。若單顆 SAS 15K 磁碟 ~180 IOPS，尚有餘裕；若 SATA 7.2K ~75 IOPS，則嚴重超載。

二、DMV 深度診斷

1. PAGEIOLATCH 等待 — 緩衝區未命中，需同步 I/O

SELECT wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'PAGEIOLATCH%'
ORDER BY wait_time_ms DESC;

• PAGEIOLATCH_SH：讀取頁面等待 (SELECT、掃描)
• PAGEIOLATCH_EX：寫入/修改頁面等待 (INSERT/UPDATE、Checkpoint、Lazy Writer)
• signal_wait_time_ms 高 → CPU 排程壓力；wait_time_ms - signal_wait_time_ms 高 → 純 I/O 延遲

2. 掛起 I/O 請求 — 精確定位慢速檔案

SELECT 
    DB_NAME(vfs.database_id) AS DatabaseName,
    vfs.file_id,
    mf.physical_name,
    vfs.io_stall_read_ms, vfs.io_stall_write_ms,
    vfs.num_of_reads, vfs.num_of_writes,
    vfs.io_stall / NULLIF(vfs.num_of_reads + vfs.num_of_writes, 0) AS Avg_Latency_ms,
    iopior.io_pending_ms_ticks,
    iopior.scheduler_address
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN sys.master_files mf ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
LEFT JOIN sys.dm_io_pending_io_requests iopior ON vfs.file_handle = iopior.io_handle
ORDER BY vfs.io_stall DESC;

重點關注 Avg_Latency_ms > 20 或有 io_pending_ms_ticks 累積的檔案。

3. 檢查點/惰性寫入器壓力

SELECT 
    (cntr_value / 1024.0) AS CheckpointPagesPerSec
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Checkpoint pages/sec';
-- 持續高值表示記憶體壓力或 I/O 寫入跟不上

三、磁碟碎片整理

• NTFS 檔案系統碎片： 導致磁頭頻繁移動，嚴重降低順序讀寫效能。定期排程整理 (每週/每月)，但 SAN/虛擬磁碟需諮詢廠商 (部分 SAN 整理後效能反降)。
• 索引碎片 (邏輯碎片)： sys.dm_db_index_physical_stats 監控 avg_fragmentation_in_percent。>30% → REBUILD (ONLINE=ON)；5-30% → REORGANIZE。
• TempDB 碎片： 重啟 SQL 服務自動重建，無需手動整理。

四、磁碟配置最佳實務

五、寫入快取與斷電保護

• 控制器寫入快取 (Write-Back Cache)： 必須啟用 並配備 電池備援單元 (BBU) / 快閃備援 (FBWC)，確保斷電時快取資料可寫入磁碟。
• 作業系統寫入快取： 裝置管理員 → 磁碟機 → 原則 → 「啟用裝置上的寫入快取」+「關閉 Windows 寫入快取緩衝區」 (SQL Server 自行管理)。
• SQL Server 「強制寫入」： 日誌寫入總是 FILE_FLAG_WRITE_THROUGH + FILE_FLAG_NO_BUFFERING，繞過 OS 快取，直接命中控制器快取。

六、OLTP/OLAP 隔離原則

• 嚴禁在同一實例混跑 OLTP (高併發小交易) 與 OLAP (大掃描報表)
• 方案：AlwaysOn 可讀次要複本承接報表、或 ETL 至專用資料倉儲 (Columnstore、分區表)
• 程式碼優化 (索引、SARGable、避免掃描) 是減少不必要 I/O 的根本

I/O 調優是「測量 → 定位 → 優化儲存/配置/查詢 → 再測量」的閉環。建立基線指標庫，異動前後對比，才能量化成果。