SQL Server 2005 SP4 延遲與阻塞 I/O 偵測報告機制詳解

問題定義

• 阻塞 I/O：需外部干預才能完成的 I/O 請求 (IRP)，通常意味著硬體故障或驅動程式 Bug，需重開機修復
• 延遲 I/O：無需干預但超過預期完成時間的 I/O，常因硬體配置、韌體設定、濾波驅動程式導致

兩者皆嚴重影響 SQL Server 效能：阻塞、閂鎖競爭、逾時、回應時間過長、資源過度消耗。

SQL Server 2000 SP4+ 內建偵測邏輯

• 閾值：**15 秒** (不可調整)
• 偵測點：
  1. I/O 實際完成時：若耗時 >15 秒 → 記錄
  2. Lazy Writer 執行時：掃描所有掛起 I/O，超過 15 秒 → 記錄
• 報告頻率：至少 5 分鐘間隔，下次 I/O 請求觸發寫入錯誤日誌

錯誤日誌訊息範例

2004-11-11 00:21:25.26 spid1 SQL Server has encountered 192 occurrence(s) 
of IO requests taking longer than 15 seconds to complete 
on file [E:\SEDATA\stressdb5.ndf] in database [stressdb] (7). 
The OS file handle is 0x00000000000074D4. 
The offset of the latest long IO is: 0x00000000022000.

關鍵資訊欄位解讀

停用偵測 (不建議)

• 啟動參數 -T830 或執行 DBCC TRACEON(830, -1) (需重啟生效)
• 僅建議在確認為誤報且已與硬體廠商協調後暫時停用

排查建議流程

1. 收集錯誤日誌中重複出現的檔案/位移
2. 檢查 Windows 系統事件日誌 (System/Application) 磁碟/控制器錯誤
3. 執行硬體廠商診斷工具 (RAID 卡、HBA、磁碟)
4. 更新韌體、驅動程式、多路徑軟體 (MPIO/DSM)
5. 檢查防毒/備份/快照軟體是否攔截 I/O (濾波驅動)
6. 驗證磁碟佈局：資料/記錄/TempDB 分離、RAID 等級適配
7. 必要時啟用 fn_dblog/DBCC PAGE 分析具體頁面物件

SQL Server 2005 SP4 改進

報告工具顯著減少識別與定位根因所需時間，提供更豐富的診斷上下文 (具體報告介面細節請參考官方文件)。