SQL Server 即時監控 I/O 等待狀態：sys.dm_io_pending_io_requests 實戰查詢

當懷疑儲存子系統成為瓶頸時，結合 sys.dm_io_pending_io_requests（掛起 I/O 請求）與 sys.dm_io_virtual_file_stats（檔案層級 I/O 統計），可即時定位哪個資料庫、哪個檔案正經歷 I/O 壓力。

核心查詢

SELECT 
    DB_NAME(vfs.database_id) AS DBName,
    vfs.database_id,
    vfs.file_id,
    vfs.io_stall,
    pir.io_pending_ms_ticks,
    pir.scheduler_address
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN sys.dm_io_pending_io_requests pir ON pir.io_handle = vfs.file_handle;

欄位解讀

• io_stall：累積 I/O 等待毫秒數（讀+寫），高值代表歷史瓶頸。
• io_pending_ms_ticks：目前掛起請求已等待毫秒數，**即時壓力指標**。
• scheduler_address：關聯排程器，可進一步連結 sys.dm_os_schedulers 觀察是否有特定 CPU 核心 I/O 排隊。

進階：依資料庫/檔案聚合掛起 I/O 計數

SELECT 
    DB_NAME(vfs.database_id) AS DBName,
    mf.physical_name,
    COUNT(*) AS PendingRequestCount,
    MAX(pir.io_pending_ms_ticks) AS MaxWaitMs,
    AVG(pir.io_pending_ms_ticks) AS AvgWaitMs
FROM sys.dm_io_pending_io_requests pir
JOIN sys.dm_io_virtual_file_stats(NULL, NULL) vfs ON pir.io_handle = vfs.file_handle
JOIN sys.master_files mf ON mf.database_id = vfs.database_id AND mf.file_id = vfs.file_id
GROUP BY vfs.database_id, mf.physical_name
ORDER BY PendingRequestCount DESC;

配合觀察建議

• 若 PendingRequestCount 持續 > 0 且 AvgWaitMs 高，檢查對應磁碟延遲（sys.dm_io_virtual_file_stats 的 io_stall_read_ms/io_stall_write_ms）。
• 搭配效能監視器 PhysicalDisk\Avg. Disk sec/Read/Write 確認硬體層面延遲。
• 常見原因：Tempdb 爭用、缺乏索引導致大量掃描、記憶體不足頻繁頁面交換、儲存陣列效能不足。