熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> SQL Server >> 正文

檢測解決SQLServer延遲阻塞I/O問題

2013-11-15 14:42:47  來源: SQL Server 

  摘要本文探討了 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\stressdbndf] in database [stressdb] () The OS
  file handle is xD 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 ServerDbcc 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
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.