檢測和解決sqlserver2000sp4中的延...杭州電子.

1、檢測和解決 SQL Server 2000 SP 4 中的延遲和阻塞 I/O 問題簡介像 SQL Server 這樣的數(shù)據(jù)庫管理系統(tǒng)依賴于文件輸入/ 輸出操作的及時進行。 有故障或配置不當(dāng)?shù)挠布⒐碳O(shè)置、篩選器驅(qū)動程序、壓縮、程序錯誤以及 I/O 路徑內(nèi)的其他情況都 可能導(dǎo)致阻塞或延遲 I/O 問題，并且很快對 SQL Server 性能產(chǎn)生消極影響。上述問題對 SQL Server 的影響因問題細節(jié)的不同而差異很大，但它們通常導(dǎo)致阻塞、鎖存 器爭用和超時、過長的響應(yīng)時間以及資源的過度利用。阻塞 I/O 是指必須進行外部干預(yù)才能完成的 I/O 請求（通常是 I/O 請求包 （IRP） ）。這

2、種 狀況通常需要執(zhí)行完整的系統(tǒng)重新啟動或類似操作才能解決， 并且強烈表明硬件有故障或者 在 I/O 路徑組件中存在程序錯誤。延遲 I/O 是指無需干預(yù)即可完成但所花時間超過預(yù)期時間的 I/O 請求（同樣，這通常是 IRP）。這種狀況的原因通常是硬件配置、固件設(shè)置或篩選器驅(qū)動程序干預(yù)，需要硬件或軟 件供應(yīng)商提供幫助以便跟蹤和解決。SQL Server 2000 SP4包含數(shù)據(jù)庫和日志文件 I/O （讀和寫）邏輯以便檢測延遲和阻塞狀況。 當(dāng) I/O 操作經(jīng)過 15 秒鐘或更長時間仍未完成時， SQL Server 會檢測到并報告這一狀況。 以下消息將被記錄到 SQL Server 錯誤日志中：20

3、04-11-11 00:21:25.26 spid1 SQL Serverhas encountered 192 occurrence（s） of IO requests taking longer than 15 seconds to complete on file E:SEDATAstressdb5.ndf in database stressdb （7）. The OS file handle is 0x00000000000074D4. The offset of the latest long IO is: x00000000022000".該消息表明，當(dāng)前工作負(fù)載需求超

4、出了 I/O 路徑或當(dāng)前系統(tǒng)配置和功能，或者 I/O 路徑含 有不能正常工作的軟件（固件、驅(qū)動程序）或硬件組件。所記錄的錯誤信息提供了以下信息：? # occurrences 未能在 15 秒鐘以內(nèi)完成讀或?qū)懖僮鞯?I/O 請求的數(shù)量。? File information 完整的文件名、數(shù)據(jù)庫名和受影響文件的 DBID 。? File handle 該文件的操作系統(tǒng)句柄?？梢酝ㄟ^調(diào)試器和其他實用工具來使用這一信息 跟蹤 IRP 請求。? Offset 上一個阻塞或延遲 I/O 的偏移量。 可以通過調(diào)試器和其他實用工具來使用這一 信息跟蹤 IRP 請求。（注：在記錄該消息的時候，該 I/O 可能

5、不再阻塞或延遲。 ）記錄與報告I/O 的報告和記錄是按照文件執(zhí)行的。延遲和阻塞 I/O 請求的檢測和報告是兩個不同的操 作。檢測（記錄） 是在 SQL Server 內(nèi)部的兩個位置處理的。 第一個位置是在 I/O 實際完成的時 候。如果請求花費了 15 秒鐘以上， 則發(fā)生記錄操作。 第二個位置是在延遲寫入器進程執(zhí)行 的時候。當(dāng)延遲寫入器執(zhí)行時，它包含新的對所有掛起的數(shù)據(jù)和日志文件 I/O 請求進行檢 查的操作，并且，如果已經(jīng)超過了 15 秒鐘的閾值，則會發(fā)生記錄操作。報告是按照不低于 5 分鐘的時間間隔執(zhí)行的。 當(dāng)對文件進行下一次 I/O 請求時， 發(fā)生報告 操作。如果記錄操作已經(jīng)發(fā)生， 并且

6、自上一次報告發(fā)生以來已經(jīng)過去了 5 分鐘或更長時間， 則向錯誤日志中寫入新的報告（上面顯示的錯誤消息） 。15 秒鐘的閾值當(dāng)前是不可調(diào)整的。 盡管不推薦這樣做， 但您可以用跟蹤標(biāo)志 830 完全禁用 延遲和阻塞I/O檢測。在 SQL Server啟動期間設(shè)置啟動參數(shù)-T830可以禁用延遲/阻塞I/O 檢測。使用 dbcc traceon（830, -1） 可以禁用對當(dāng)前正在運行的 SQL Server 實例的檢測。 只有重新啟動 SQL Server，Dbcc traceon 才會生效。注 延遲或阻塞的給定 I/O 請求只會報告一次。如果消息報告 10 個 I/O 被延遲，則這 10 個報告不

7、會再次發(fā)生。如果下一個消息報告 15 個 I/O 被阻塞，則表明 15 個新的 I/O 請 求已經(jīng)被延遲。性能和計劃操作總體系統(tǒng)性能可能在 I/O 處理中扮演關(guān)鍵的角色。在研究延遲或阻塞 I/O 的報告時，應(yīng)該 考慮系統(tǒng)的綜合運行狀況。過多的負(fù)載可能導(dǎo)致整個系統(tǒng)（包括I/O 處理）變慢。系統(tǒng)在發(fā)生問題時的行為可能是確定問題根源的關(guān)鍵所在。 例如， 如果 CPU 利用率在發(fā)生問題時 變高或者保持較高水平， 則可能表明系統(tǒng)中的某個進程正在消耗如此之多的 CPU 時間，以 至于它以各種方式對其他進程產(chǎn)生了消極影響。請查看性能計數(shù)器 Average Disk Sec/Transfer 以及 Avera

8、ge Disk Queue Length 或 Current Disk Queue Length ，以獲得特定的 I/O 路徑信息。例如， SQL Server 計算機上的 Average Disk Sec/Transfer通常低于15ms。如果該值上升，則可能表明I/O子系統(tǒng)無法滿足 I/O 要 求。請記住， SQL Server 充分利用了 Windows 的異步 I/O 功能，并且猛烈地擴展磁盤隊列長 度，因此上述性能計數(shù)器具有較高的值本身并不表明存在問題。索引和并行性 特別常見的一種情況是，因為索引丟失以及由此導(dǎo)致的掃描、哈希和排序?qū)/O 系統(tǒng)造成的壓力，所以突發(fā)大量的 I/O 。運

9、行一遍“ Index Turning Wizard ”通常會有助于解決系統(tǒng)的 I/O 壓力。 如果添加索引可以幫助查詢避免表掃描甚至排序或哈希，則系統(tǒng)可以獲得多個優(yōu)點：八、? 減少完成操作所需的物理 I/O ，這直接等效于提高查詢的性能? 數(shù)據(jù)緩存中只有較少的頁面必須周轉(zhuǎn)，因此緩存中的那些頁面可以一直與活動查詢相關(guān)? 避免不必要的排序和哈希? 可以降低 tempdb 利用率和減少爭用情況? 減少資源利用率和 /或并行操作。 因為 SQL Server 不能保證服務(wù)器在確定是否將查詢并行 化時考慮并行查詢執(zhí)行和系統(tǒng)中的負(fù)載，所以您最好針對串行執(zhí)行優(yōu)化所有查詢。在 Q/A 環(huán)境中，應(yīng)該將 max

10、degree of parallelism 設(shè)置為 1，以便對根本沒有從服務(wù)器收到任何并 行計劃的最糟糕情況強行進行調(diào)整。如果在測試環(huán)境中證實查詢可以按串行方式高效執(zhí)行， 則生產(chǎn)環(huán)境中的并行計劃可以提供出乎意料的性能改進。 但是，很多情況下， SQL Server 選 擇并行執(zhí)行， 這是因為要遍歷數(shù)據(jù)的絕對數(shù)量過于龐大。 該數(shù)據(jù)量通常直接受到索引的影響。 例如， 如果丟失索引，則可能產(chǎn)生大量排序操作。 我們很容易就可以看出， 執(zhí)行排序操作的 多個輔助進程如何使響應(yīng)速度比以串行方式處理排序更快速， 不過我們需要了解， 該操作可 能大幅增加 I/O 系統(tǒng)的壓力。當(dāng)多個輔助進程并發(fā)運行時，來自多個輔

11、助進程的大型讀請 求可能導(dǎo)致 I/O 突發(fā)以及 CPU 利用率提高。很多時候，如果添加了索引或者發(fā)生了其他 調(diào)整操作， 則可以調(diào)整查詢以使其更快地運行并使用更少的資源。 這不僅提高了相關(guān)查詢的 性能，而且還提高了系統(tǒng)的整體性能。來自 Microsoft SQL Server Support 的實際示例Microsoft SQL Server 和 Platforms Escalation Support 已經(jīng)處理了下列方案，這些方案旨在 提供一個參考框架，并且?guī)椭鷺淞⒂嘘P(guān)延遲和阻塞 I/O 情況以及系統(tǒng)可能如何受到影響的 預(yù)期。不存在給其他軟硬件帶來任何特殊或更高風(fēng)險的特殊硬件或驅(qū)動程序集； 在

12、這個方面， 所有系統(tǒng)都是相同的。示例 1 阻塞 45 秒鐘的日志寫操作一個嘗試性的 SQL Server 日志文件寫操作周期性地阻塞 45 秒鐘。 該日志寫操作無法及時 完成，從而產(chǎn)生阻塞情況，導(dǎo)致 30 秒鐘的客戶端查詢超時。請求被提交并阻塞（日志寫掛起） ，導(dǎo)致查詢繼續(xù)占用鎖并且阻塞來自其他客戶端的傳入請 求。其他客戶端開始超時并且使問題變得復(fù)雜， 這是因為應(yīng)用程序沒有被設(shè)計為在發(fā)生超時 的時候回滾尚未解決的事務(wù)。 這會導(dǎo)致數(shù)以百計尚未解決的事務(wù)占用鎖以及嚴(yán)重的阻塞。 （ 有 關(guān)事務(wù)處理和阻塞的詳細信息，請參閱 INF: Understanding and Resolving SQLSer

13、ver 7.0 or 2000 Blocking Problems）。應(yīng)用程序使用連接池來維護Web站點，因此，隨著更多的連接被阻塞， Web 站點創(chuàng)建了更多的連接，而這些連接又會被阻塞，該循環(huán)會一直持續(xù)下去。在大約 45 秒鐘之后， 該日志寫操作將完成， 但到此時為止， 數(shù)以百計的連接已經(jīng)積累起來， 從而導(dǎo)致阻塞問題，并使得 SQL Server 和應(yīng)用程序需要花費幾分鐘的時間進行恢復(fù)。當(dāng)與 應(yīng)用程序問題結(jié)合起來的時候，延遲 I/O 狀況會對系統(tǒng)產(chǎn)生非常消極的影響。解決辦法：這歸因于 HBA 驅(qū)動程序中的延遲 I/O 請求。計算機具有多個帶有故障轉(zhuǎn)移支 持的 HBA 卡。故障轉(zhuǎn)移超時值被配置

14、為 45 秒。當(dāng)一個 HBA 落后或者在 45 秒鐘或更 長時間內(nèi)未與 SAN 通信時，該 I/O 請求被路由到第二個 HBA 進行處理，并且會很快完 成。硬件產(chǎn)品的推薦故障轉(zhuǎn)移設(shè)置為 5 秒鐘，以便避免出現(xiàn)這樣的延遲狀況。如果在 SQL Server 2000 SP4 中已經(jīng)有了新的自動報告該狀況的功能， 那么我們在疑難解答 過程中就可以很快知道，基本問題是由于SQL Server 外部的問題而發(fā)生的阻塞或延遲I/O操作。事實上，我們花費了大量時間來解決一個在最初呈現(xiàn)為普通性能問題的問題。示例 2 篩選器驅(qū)動程序干預(yù)許多防病毒軟件和備份產(chǎn)品使用 I/O 篩選器驅(qū)動程序。這些篩選器驅(qū)動程序成為

15、 I/O 請求 棧的一部分， 并且可以訪問 IRP 請求。 Microsoft 技術(shù)支持部門已經(jīng)遇見過各種問題 從 導(dǎo)致阻塞 I/O 的錯誤到篩選器驅(qū)動程序?qū)崿F(xiàn)中的延遲狀況。其中， Microsoft SQL Server 技術(shù)支持部門遇到的一種情況是，涉及到用于備份處理（該過 程能夠備份在備份時處于打開狀態(tài)的文件） 的篩選器驅(qū)動程序。 系統(tǒng)管理員錯誤地在文件備 份選擇中包括了 SQL Server 數(shù)據(jù)文件目錄。當(dāng)備份發(fā)生時，它試圖收集備份開始時文件的 一致鏡像。在完成該操作時，它將延遲后續(xù)的 I/O 請求，使它們能夠在軟件處理它們時逐 個完成。當(dāng)備份開始時， SQL Server 的性能會

16、急劇下降，因為針對 SQL Server 的 I/O 被強迫逐個 完成。使該問題變得更為復(fù)雜的是，單 I/O 邏輯的特點使得 I/O 通常無法異步執(zhí)行，因此 當(dāng) SQL Server 期望發(fā)送 I/O 請求并繼續(xù)工作時， UMS 輔助進程卻在 I/O 完成之前一直阻 塞在讀或?qū)懻{(diào)用中。 SQL Server 預(yù)讀功能實際上被篩選器驅(qū)動程序的操作禁用了。而且， 即使當(dāng)備份完成時，篩選器驅(qū)動程序中的另一個程序錯誤仍然使單 I/O 行為保持不變。恢 復(fù) SQL Server 性能的唯一方法是關(guān)閉數(shù)據(jù)庫并重新打開它或者重新啟動 SQL Server ，以便 在當(dāng)前篩選器驅(qū)動程序交互未就緒的情況下釋放并

17、重新獲取文件句柄。解決辦法：將 SQL Server 的數(shù)據(jù)文件從文件備份過程中排除，并且解決篩選器驅(qū)動程序中 的導(dǎo)致文件被置于單 I/O 模式的程序錯誤。這時，如果我們已經(jīng)具有了 SQL Server 2000 SP4 對延遲 I/O 操作進行報告的功能，那么 我們在疑難解答過程中就可以很快知道基本問題是什么。示例 3 隱藏的錯誤很多高端系統(tǒng)具有用于處理負(fù)載平衡的多通道 I/O 路徑以及類似的工具。 Microsoft SQL Server 技術(shù)支持部門已經(jīng)見過使用此類軟件的情況，其中，盡管 I/O 請求失敗，但軟件確 實正確地處理了錯誤狀況，并且執(zhí)行了無數(shù)次重試。 I/O 被阻塞，并且 S

18、QL Server 無法完 成指定的操作。 與上面描述的日志寫狀況非常類似， 在這樣的狀況對系統(tǒng)產(chǎn)生了消極影響之 后，發(fā)生了很多糟糕的系統(tǒng)行為。解決辦法：在類似情況下，重新啟動 SQL Server 可以在一定程度上緩解問題，但是，有時需要重新啟動 Windows 來使處理恢復(fù)到正常狀態(tài)。當(dāng)然， I/O 子系統(tǒng)中的程序錯誤最終需 要由 I/O 供應(yīng)商解決。SQL Server 2000 SP4 的新的對此類狀況進行自動報告的功能使得類似問題的檢測變得更加 容易。我們不僅可以看到整個服務(wù)器的總體性能下降，而且還可以通過 SP4 所記錄的新消 息洞察問題的本質(zhì)，并且知道該問題很可能出在 SQL S

19、erver 外部。示例 4 遠程存儲 /鏡像 /RAID 驅(qū)動器很多系統(tǒng)使用鏡像或類似的技術(shù)來幫助防止丟失數(shù)據(jù)。 其中一些系統(tǒng)是基于軟件的， 而其他 系統(tǒng)是基于 硬件的。 Microsoft SQL Server 技術(shù)支持部門經(jīng)常遇到的與這些系統(tǒng)有關(guān)的情況 是延遲增加。當(dāng)針對鏡像的 I/O 必須在 I/O 操作被視為完成之前成功完成時， 這顯然會增加總體 I/O 時 間。對于遠程鏡像安裝，網(wǎng)絡(luò)延遲和重試可能成為一個不利因素。當(dāng)發(fā)生驅(qū)動器故障并且 RAID 子系統(tǒng)重新生成時， I/O 吞吐量可能會受到影響。解決辦法：在類似情況下，我們通常建議使用嚴(yán)格的配置設(shè)置(這隨供應(yīng)商和設(shè)備而異) ， 以減少鏡像延遲和 RAID 重新生成操作。RAID 系統(tǒng)開銷和延遲可能導(dǎo)致 I/O 變慢，而 SQL Server 對此無能為力。 就像任何其他應(yīng) 用程序一樣，它是 RAID 硬件和驅(qū)動程序的客戶端。當(dāng)該類型的問題使服務(wù)器的速度過度 降低時， SP4 中新的延遲和阻塞 I/O 報告功能有助于查明問題所在。示例 5 壓縮Microsoft 不在壓縮驅(qū)動器上支持 SQL Server 7.0 或 2000 數(shù)據(jù)和日志文件。 NTFS 壓縮是 不安全的， 這不僅是因為它破壞了預(yù)寫日志 (WAL) 協(xié)議，而且還因為它