摘要
本文探討了 SQL Server
Service Pack
中的報告工具如何顯著減少為識別和確定延遲和阻塞 I/O 操作的根源所花費的時間
簡介
像 SQL Server 這樣的數據庫管理系統依賴於文件輸入/輸出操作的及時進行
有故障或配置不當的硬件
固件設置
篩選器驅動程序
壓縮
程序錯誤以及 I/O 路徑內的其他情況都可能導致阻塞或延遲 I/O 問題
並且很快對 SQL Server 性能產生消極影響
上述問題對 SQL Server 的影響因問題細節的不同而差異很大
但它們通常導致阻塞
鎖存器爭用和超時
過長的響應時間以及資源的過度利用
阻塞 I/O 是指必須進行外部干預才能完成的 I/O 請求（通常是 I/O 請求包 (IRP)）
這種狀況通常需要執行完整的系統重新啟動或類似操作才能解決
並且強烈表明硬件有故障或者在 I/O 路徑組件中存在程序錯誤
延遲 I/O 是指無需干預即可完成但所花時間超過預期時間的 I/O 請求（同樣
這通常是 IRP）
這種狀況的原因通常是硬件配置
固件設置或篩選器驅動程序干預
需要硬件或軟件供應商提供幫助以便跟蹤和解決
SQL Server
SP
包含數據庫和日志文件 I/O（讀和寫）邏輯以便檢測延遲和阻塞狀況
當 I/O 操作經過
秒鐘或更長時間仍未完成時
SQL Server 會檢測到並報告這一狀況
以下消息將被記錄到 SQL Server 錯誤日志中
:
:
spid
SQL Serverhas encountered
occurrence(s) of IO requests taking longer than
seconds to complete
on file [E:\SEDATA\stressdb
ndf] in database [stressdb] (
)
The OS
file handle is
x
D
The offset of the latest long IO is:
x
該消息表明
當前工作負載需求超出了 I/O 路徑或當前系統配置和功能
或者 I/O 路徑含有不能正常工作的軟件（固件
驅動程序）或硬件組件
所記錄的錯誤信息提供了以下信息
### occurrences — 未能在
秒鐘以內完成讀或寫操作的 I/O 請求的數量
File information — 完整的文件名
數據庫名和受影響文件的 DBID
File handle — 該文件的操作系統句柄
可以通過調試器和其他實用工具來使用這一信息跟蹤 IRP 請求
Offset — 上一個阻塞或延遲 I/O 的偏移量
可以通過調試器和其他實用工具來使用這一信息跟蹤 IRP 請求
（注
在記錄該消息的時候
該 I/O 可能不再阻塞或延遲
）
記錄與報告 I/O 的報告和記錄是按照文件執行的
延遲和阻塞 I/O 請求的檢測和報告是兩個不同的操作
檢測（記錄）是在 SQL Server 內部的兩個位置處理的
第一個位置是在 I/O 實際完成的時候
如果請求花費了
秒鐘以上
則發生記錄操作
第二個位置是在延遲寫入器進程執行的時候
當延遲寫入器執行時
它包含新的對所有掛起的數據和日志文件 I/O 請求進行檢查的操作
並且
如果已經超過了
秒鐘的阈值
則會發生記錄操作
報告是按照不低於
分鐘的時間間隔執行的
當對文件進行下一次 I/O 請求時
發生報告操作
如果記錄操作已經發生
並且自上一次報告發生以來已經過去了
分鐘或更長時間
則向錯誤日志中寫入新的報告（上面顯示的錯誤消息）
秒鐘的阈值當前是不可調整的
盡管不推薦這樣做
但您可以用跟蹤標志
完全禁用延遲和阻塞 I/O 檢測
在 SQL Server 啟動期間設置啟動參數 –T
可以禁用延遲/阻塞 I/O 檢測
使用 dbcc traceon(
) 可以禁用對當前正在運行的 SQL Server 實例的檢測
只有重新啟動 SQL Server
Dbcc traceon 才會生效
注 延遲或阻塞的給定 I/O 請求只會報告一次
如果消息報告
個 I/O 被延遲
則這
個報告不會再次發生
如果下一個消息報告
個 I/O 被阻塞
則表明
個新的 I/O 請求已經被延遲
性能和計劃操作 總體系統性能可能在 I/O 處理中扮演關鍵的角色
在研究延遲或阻塞 I/O 的報告時
應該考慮系統的綜合運行狀況
過多的負載可能導致整個系統（包括 I/O 處理）變慢
系統在發生問題時的行為可能是確定問題根源的關鍵所在
例如
如果 CPU 利用率在發生問題時變高或者保持較高水平
則可能表明系統中的某個進程正在消耗如此之多的 CPU 時間
以至於它以各種方式對其他進程產生了消極影響
請查看性能計數器 Average Disk Sec/Transfer 以及 Average Disk Queue Length 或 Current Disk Queue Length
以獲得特定的 I/O 路徑信息
例如
SQL Server 計算機上的 Average Disk Sec/Transfer 通常低於
ms
如果該值上升
則可能表明 I/O 子系統無法滿足 I/O 要求
請記住
SQL Server 充分利用了 Windows 的異步 I/O 功能
並且猛烈地擴展磁盤隊列長度
因此上述性能計數器具有較高的值本身並不表明存在問題
索引和並行性 特別常見的一種情況是
因為索引丟失以及由此導致的掃描
哈希和排序對 I/O 系統造成的壓力
所以突發大量的 I/O
運行一遍
Index Turning Wizard
通常會有助於解決系統的 I/O 壓力
如果添加索引可以幫助查詢避免表掃描甚至排序或哈希
則系統可以獲得多個優點
減少完成操作所需的物理 I/O
這直接等效於提高查詢的性能
數據緩存中只有較少的頁面必須周轉
因此緩存中的那些頁面可以一直與活動查詢相關
避免不必要的排序和哈希 可以降低 tempdb 利用率和減少爭用情況
減少資源利用率和/或並行操作
因為 SQL Server 不能保證服務器在確定是否將查詢並行化時考慮並行查詢執行和系統中的負載
所以您最好針對串行執行優化所有查詢
在 Q/A 環境中
應該將 max degree of parallelism 設置為
以便對根本沒有從服務器收到任何並行計劃的最糟糕情況強行進行調整
如果在測試環境中證實查詢可以按串行方式高效執行
則生產環境中的並行計劃可以提供出乎意料的性能改進
但是
很多情況下
SQL Server 選擇並行執行
這是因為要遍歷數據的絕對數量過於龐大
該數據量通常直接受到索引的影響
例如
如果丟失索引
則可能產生大量排序操作
我們很容易就可以看出
執行排序操作的多個輔助進程如何使響應速度比以串行方式處理排序更快速
不過我們需要了解
該操作可能大幅增加 I/O 系統的壓力
當多個輔助進程並發運行時
來自多個輔助進程的大型讀請求可能導致 I/O 突發以及 CPU 利用率提高
很多時候
如果添加了索引或者發生了其他調整操作
則可以調整查詢以使其更快地運行並使用更少的資源
這不僅提高了相關查詢的性能
而且還提高了系統的整體性能
來自 Microsoft SQL Server Support 的實際示例
Microsoft SQL Server 和 Platforms Escalation Support 已經處理了下列方案
這些方案旨在提供一個參考框架
並且幫助樹立有關延遲和阻塞 I/O 情況以及系統可能如何受到影響的預期
不存在給其他軟硬件帶來任何特殊或更高風險的特殊硬件或驅動程序集
在這個方面
所有系統都是相同的
示例 — 阻塞 秒鐘的日志寫操作 一個嘗試性的 SQL Server 日志文件寫操作周期性地阻塞
秒鐘
該日志寫操作無法及時完成
從而產生阻塞情況
導致
秒鐘的客戶端查詢超時
請求被提交並阻塞（日志寫掛起）
導致查詢繼續占用鎖並且阻塞來自其他客戶端的傳入請求
其他客戶端開始超時並且使問題變得復雜
這是因為應用程序沒有被設計為在發生超時的時候回滾尚未解決的事務
這會導致數以百計尚未解決的事務占用鎖以及嚴重的阻塞
應用程序使用連接池來維護 Web 站點
因此
隨著更多的連接被阻塞
Web 站點創建了更多的連接
而這些連接又會被阻塞
該循環會一直持續下去
在大約
秒鐘之後
該日志寫操作將完成
但到此時為止
數以百計的連接已經積累起來
從而導致阻塞問題
並使得 SQL Server 和應用程序需要花費幾分鐘的時間進行恢復
當與應用程序問題結合起來的時候
延遲 I/O 狀況會對系統產生非常消極的影響
解決辦法
這歸因於 HBA 驅動程序中的延遲 I/O 請求
計算機具有多個帶有故障轉移支持的 HBA 卡
故障轉移超時值被配置為
秒
當一個 HBA 落後或者在
秒鐘或更長時間內未與 SAN 通信時
該 I/O 請求被路由到第二個 HBA 進行處理
並且會很快完成
硬件產品的推薦故障轉移設置為
秒鐘
以便避免出現這樣的延遲狀況
如果在 SQL Server
SP
中已經有了新的自動報告該狀況的功能
那麼我們在疑難解答過程中就可以很快知道
基本問題是由於 SQL Server 外部的問題而發生的阻塞或延遲 I/O 操作
事實上
我們花費了大量時間來解決一個在最初呈現為普通性能問題的問題
示例 — 篩選器驅動程序干預 許多防病毒軟件和備份產品使用 I/O 篩選器驅動程序
這些篩選器驅動程序成為 I/O 請求棧的一部分
並且可以訪問 IRP 請求
Microsoft 技術支持部門已經遇見過各種問題 — 從導致阻塞 I/O 的錯誤到篩選器驅動程序實現中的延遲狀況
其中
Microsoft SQL Server 技術支持部門遇到的一種情況是
涉及到用於備份處理（該過程能夠備份在備份時處於打開狀態的文件）的篩選器驅動程序
系統管理員錯誤地在文件備份選擇中包括了 SQL Server 數據文件目錄
當備份發生時
它試圖收集備份開始時文件的一致鏡像
在完成該操作時
它將延遲後續的 I/O 請求
使它們能
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22220.html
