I/O 子系統是 SQL Server 效能的命門。當 `PAGEIOLATCH_*` 等待飆高、查詢變慢、超時頻發時,如何科學定位 I/O 瓶頸?本文提供 **Performance Monitor 計數器閾值、DMV 深度診斷查詢、磁碟碎片整理策略、RAID 級別 I/O 試算公式、磁碟配置最佳實踐** 完整工具箱。
| 物件/計數器 | 含義 | 健康閾值 | 警示閾值 | 嚴重閾值 |
|---|---|---|---|---|
| PhysicalDisk\Avg. Disk Queue Length | 平均讀寫請求佇列長度 | < 2 (每磁碟) | > 2 (持續) | > 5 (持續) |
| PhysicalDisk\Avg. Disk sec/Read | 平均讀取延遲 (秒) | < 0.010 (10ms) | 0.010 - 0.020 | > 0.020 (20ms) |
| PhysicalDisk\Avg. Disk sec/Write | 平均寫入延遲 (秒) | < 0.010 (10ms) | 0.010 - 0.020 | > 0.020 (20ms) |
| PhysicalDisk\% Disk Time | 磁碟忙碌處理讀寫的時間百分比 | < 50% | 50% - 80% | > 80% |
| PhysicalDisk\Disk Reads/sec | 每秒讀取操作數 | < 磁碟能力 85% | 接近極限 | > 85% 吞吐量呈指數級惡化 |
| PhysicalDisk\Disk Writes/sec | 每秒寫入操作數 | < 磁碟能力 85% | 接近極限 | > 85% |
觀測值為**所有磁碟總佇列**。
平均每磁碟佇列 = 觀測值 / 物理磁碟數 (或 RAID 有效磁碟數)。
例:4 顆磁碟 RAID 10,觀測 Queue=10 → 平均 2.5 (需關注),而非 10 (嚴重)。
已知 `Reads/sec`、`Writes/sec`,試算**每顆物理磁碟承擔的 I/OPS**:
| RAID 類型 | 公式 (每磁碟 I/OPS) | 寫入懲罰 |
|---|---|---|
| RAID 0 | (Reads + Writes) / N | 1x (無保護) |
| RAID 1 / 10 | [Reads + (Writes × 2)] / N | 2x (鏡像雙寫) |
| RAID 5 | [Reads + (Writes × 4)] / N | 4x (讀舊數據/舊奇偶、寫新數據/新奇偶) |
| RAID 6 | [Reads + (Writes × 6)] / N | 6x (雙奇偶) |
實例:RAID 1,Reads=90, Writes=75 → 每磁碟 = [90 + 75×2] / 2 = **120 IOPS**。對比磁碟規格 (如 15K SAS ~180 IOPS, SSD ~5000-100000 IOPS) 判斷是否超載。
-- 聚焦 I/O Latch 等待 (頁面不在緩衝區,需等待物理讀取)
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;
SELECT
DB_NAME(database_id) AS DBName,
file_id,
type_desc, -- ROWS (資料) / LOG (日誌)
num_of_reads, num_of_writes,
io_stall_read_ms, io_stall_write_ms, io_stall_ms, -- 總等待毫秒
size_on_disk_bytes / 1024 / 1024 AS SizeMB
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
ORDER BY io_stall_ms DESC;
SELECT
vfs.database_id, vfs.file_id, vfs.io_stall, vfs.io_pending_ms_ticks,
p.scheduler_address
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN sys.dm_io_pending_io_requests p ON vfs.file_handle = p.io_handle;
I/O 瓶頸排查遵循 **「由宏觀到微觀、由症狀到根因」** 路徑:PerfMon 看磁碟整體健康 → DMV 定位資料庫/檔案/等待類型/具體查詢 → 檢查碎片/RAID 配置/檔案佈局 → 程式碼/索引優化減少 I/O 需求。**量化指標 (IOPS、延遲、佇列、等待時間) 是唯一說話依據**,拒絕主觀臆測。