像 SQL Server 這樣的數(shù)據(jù)庫(kù)管理系統(tǒng)依賴于文件輸入/輸出操作的及時(shí)進(jìn)行。有故障或配置不當(dāng)?shù)挠布⒐碳O(shè)置、篩選器驅(qū)動(dòng)程序、壓縮、程序錯(cuò)誤以及 I/O 路徑內(nèi)的其他情況都可能導(dǎo)致阻塞或延遲 I/O 問(wèn)題，并且很快對(duì) SQL Server 性能產(chǎn)生消極影響。

上述問(wèn)題對(duì) SQL Server 的影響因問(wèn)題細(xì)節(jié)的不同而差異很大，但它們通常導(dǎo)致阻塞、鎖存器爭(zhēng)用和超時(shí)、過(guò)長(zhǎng)的響應(yīng)時(shí)間以及資源的過(guò)度利用。

阻塞 I/O 是指必須進(jìn)行外部干預(yù)才能完成的 I/O 請(qǐng)求（通常是 I/O 請(qǐng)求包 (IRP)）。這種狀況通常需要執(zhí)行完整的系統(tǒng)重新啟動(dòng)或類似操作才能解決，并且強(qiáng)烈表明硬件有故障或者在 I/O 路徑組件中存在程序錯(cuò)誤。

延遲 I/O 是指無(wú)需干預(yù)即可完成但所花時(shí)間超過(guò)預(yù)期時(shí)間的 I/O 請(qǐng)求（同樣，這通常是 IRP）。這種狀況的原因通常是硬件配置、固件設(shè)置或篩選器驅(qū)動(dòng)程序干預(yù)，需要硬件或軟件供應(yīng)商提供幫助以便跟蹤和解決。

SQL Server 2000 SP4 包含數(shù)據(jù)庫(kù)和日志文件 I/O（讀和寫）邏輯以便檢測(cè)延遲和阻塞狀況。當(dāng) I/O 操作經(jīng)過(guò) 15 秒鐘或更長(zhǎng)時(shí)間仍未完成時(shí)，SQL Server 會(huì)檢測(cè)到并報(bào)告這一狀況。以下消息將被記錄到 SQL Server 錯(cuò)誤日志中：

2007-11-1 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ù)載需求超出了 I/O 路徑或當(dāng)前系統(tǒng)配置和功能，或者 I/O 路徑含有不能正常工作的軟件（固件、驅(qū)動(dòng)程序）或硬件組件。

所記錄的錯(cuò)誤信息提供了以下信息：

• ### occurrences — 未能在 15 秒鐘以內(nèi)完成讀或?qū)懖僮鞯?I/O 請(qǐng)求的數(shù)量。

• File information — 完整的文件名、數(shù)據(jù)庫(kù)名和受影響文件的 DBID。

• File handle — 該文件的操作系統(tǒng)句柄。可以通過(guò)調(diào)試器和其他實(shí)用工具來(lái)使用這一信息跟蹤 IRP 請(qǐng)求。

• Offset — 上一個(gè)阻塞或延遲 I/O 的偏移量。可以通過(guò)調(diào)試器和其他實(shí)用工具來(lái)使用這一信息跟蹤 IRP 請(qǐng)求。（注：在記錄該消息的時(shí)候，該 I/O 可能不再阻塞或延遲。）

記錄與報(bào)告 I/O 的報(bào)告和記錄是按照文件執(zhí)行的。延遲和阻塞 I/O 請(qǐng)求的檢測(cè)和報(bào)告是兩個(gè)不同的操作。

檢測(cè)（記錄）是在 SQL Server 內(nèi)部的兩個(gè)位置處理的。第一個(gè)位置是在 I/O 實(shí)際完成的時(shí)候。如果請(qǐng)求花費(fèi)了 15 秒鐘以上，則發(fā)生記錄操作。第二個(gè)位置是在延遲寫入器進(jìn)程執(zhí)行的時(shí)候。當(dāng)延遲寫入器執(zhí)行時(shí)，它包含新的對(duì)所有掛起的數(shù)據(jù)和日志文件 I/O 請(qǐng)求進(jìn)行檢查的操作，并且，如果已經(jīng)超過(guò)了 15 秒鐘的閾值，則會(huì)發(fā)生記錄操作。

報(bào)告是按照不低于 5 分鐘的時(shí)間間隔執(zhí)行的。當(dāng)對(duì)文件進(jìn)行下一次 I/O 請(qǐng)求時(shí)，發(fā)生報(bào)告操作。如果記錄操作已經(jīng)發(fā)生，并且自上一次報(bào)告發(fā)生以來(lái)已經(jīng)過(guò)去了 5 分鐘或更長(zhǎng)時(shí)間，則向錯(cuò)誤日志中寫入新的報(bào)告（上面顯示的錯(cuò)誤消息）。

15 秒鐘的閾值當(dāng)前是不可調(diào)整的。盡管不推薦這樣做，但您可以用跟蹤標(biāo)志 830 完全禁用延遲和阻塞 I/O 檢測(cè)。在 SQL Server 啟動(dòng)期間設(shè)置啟動(dòng)參數(shù) –T830 可以禁用延遲/阻塞 I/O 檢測(cè)。使用 dbcc traceon(830, -1) 可以禁用對(duì)當(dāng)前正在運(yùn)行的 SQL Server 實(shí)例的檢測(cè)。只有重新啟動(dòng) SQL Server，Dbcc traceon 才會(huì)生效。

注：延遲或阻塞的給定 I/O 請(qǐng)求只會(huì)報(bào)告一次。如果消息報(bào)告 10 個(gè) I/O 被延遲，則這 10 個(gè)報(bào)告不會(huì)再次發(fā)生。如果下一個(gè)消息報(bào)告 15 個(gè) I/O 被阻塞，則表明 15 個(gè)新的 I/O 請(qǐng)求已經(jīng)被延遲。

性能和計(jì)劃操作

總體系統(tǒng)性能可能在 I/O 處理中扮演關(guān)鍵的角色。在研究延遲或阻塞 I/O 的報(bào)告時(shí)，應(yīng)該考慮系統(tǒng)的綜合運(yùn)行狀況。過(guò)多的負(fù)載可能導(dǎo)致整個(gè)系統(tǒng)（包括 I/O 處理）變慢。系統(tǒng)在發(fā)生問(wèn)題時(shí)的行為可能是確定問(wèn)題根源的關(guān)鍵所在。例如，如果 CPU 利用率在發(fā)生問(wèn)題時(shí)變高或者保持較高水平，則可能表明系統(tǒng)中的某個(gè)進(jìn)程正在消耗如此之多的 CPU 時(shí)間，以至于它以各種方式對(duì)其他進(jìn)程產(chǎn)生了消極影響。

請(qǐng)查看性能計(jì)數(shù)器 Average Disk Sec/Transfer 以及 Average Disk Queue Length 或 Current Disk Queue Length，以獲得特定的 I/O 路徑信息。例如，SQL Server 計(jì)算機(jī)上的 Average Disk Sec/Transfer 通常低于 15ms。如果該值上升，則可能表明 I/O 子系統(tǒng)無(wú)法滿足 I/O 要求。

請(qǐng)記住，SQL Server 充分利用了 Windows 的異步 I/O 功能，并且猛烈地?cái)U(kuò)展磁盤隊(duì)列長(zhǎng)度，因此上述性能計(jì)數(shù)器具有較高的值本身并不表明存在問(wèn)題。

索引和并行性

特別常見(jiàn)的一種情況是，因?yàn)樗饕齺G失以及由此導(dǎo)致的掃描、哈希和排序?qū)?I/O 系統(tǒng)造成的壓力，所以突發(fā)大量的 I/O。運(yùn)行一遍“Index Turning Wizard”通常會(huì)有助于解決系統(tǒng)的 I/O 壓力。如果添加索引可以幫助查詢避免表掃描甚至排序或哈希，則系統(tǒng)可以獲得多個(gè)優(yōu)點(diǎn)：

• 減少完成操作所需的物理 I/O，這直接等效于提高查詢的性能。

• 數(shù)據(jù)緩存中只有較少的頁(yè)面必須周轉(zhuǎn)，因此緩存中的那些頁(yè)面可以一直與活動(dòng)查詢相關(guān)。

• 避免不必要的排序和哈希。

• 可以降低 tempdb 利用率和減少爭(zhēng)用情況。

• 減少資源利用率和/或并行操作。因?yàn)?SQL Server 不能保證服務(wù)器在確定是否將查詢并行化時(shí)考慮并行查詢執(zhí)行和系統(tǒng)中的負(fù)載，所以您最好針對(duì)串行執(zhí)行優(yōu)化所有查詢。在 Q/A 環(huán)境中，應(yīng)該將 max degree of parallelism 設(shè)置為 1，以便對(duì)根本沒(méi)有從服務(wù)器收到任何并行計(jì)劃的最糟糕情況強(qiáng)行進(jìn)行調(diào)整。如果在測(cè)試環(huán)境中證實(shí)查詢可以按串行方式高效執(zhí)行，則生產(chǎn)環(huán)境中的并行計(jì)劃可以提供出乎意料的性能改進(jìn)。但是，很多情況下，SQL Server 選擇并行執(zhí)行，這是因?yàn)橐闅v數(shù)據(jù)的絕對(duì)數(shù)量過(guò)于龐大。該數(shù)據(jù)量通常直接受到索引的影響。例如，如果丟失索引，則可能產(chǎn)生大量排序操作。我們很容易就可以看出，執(zhí)行排序操作的多個(gè)輔助進(jìn)程如何使響應(yīng)速度比以串行方式處理排序更快速，不過(guò)我們需要了解，該操作可能大幅增加 I/O 系統(tǒng)的壓力。當(dāng)多個(gè)輔助進(jìn)程并發(fā)運(yùn)行時(shí)，來(lái)自多個(gè)輔助進(jìn)程的大型讀請(qǐng)求可能導(dǎo)致 I/O 突發(fā)以及 CPU 利用率提高。很多時(shí)候，如果添加了索引或者發(fā)生了其他調(diào)整操作，則可以調(diào)整查詢以使其更快地運(yùn)行并使用更少的資源。這不僅提高了相關(guān)查詢的性能，而且還提高了系統(tǒng)的整體性能。來(lái)自 Microsoft SQL Server Support 的實(shí)際示例 Microsoft SQL Server 和 Platforms Escalation Support 已經(jīng)處理了下列方案，這些方案旨在提供一個(gè)參考框架，并且?guī)椭鷺?shù)立有關(guān)延遲和阻塞 I/O 情況以及系統(tǒng)可能如何受到影響的預(yù)期。不存在給其他軟硬件帶來(lái)任何特殊或更高風(fēng)險(xiǎn)的特殊硬件或驅(qū)動(dòng)程序集；在這個(gè)方面，所有系統(tǒng)都是相同的。

示例 1 — 阻塞 45 秒鐘的日志寫操作：

一個(gè)嘗試性的 SQL Server 日志文件寫操作周期性地阻塞 45 秒鐘。該日志寫操作無(wú)法及時(shí)完成，從而產(chǎn)生阻塞情況，導(dǎo)致 30 秒鐘的客戶端查詢超時(shí)。

請(qǐng)求被提交并阻塞（日志寫掛起），導(dǎo)致查詢繼續(xù)占用鎖并且阻塞來(lái)自其他客戶端的傳入請(qǐng)求。其他客戶端開(kāi)始超時(shí)并且使問(wèn)題變得復(fù)雜，這是因?yàn)閼?yīng)用程序沒(méi)有被設(shè)計(jì)為在發(fā)生超時(shí)的時(shí)候回滾尚未解決的事務(wù)。這會(huì)導(dǎo)致數(shù)以百計(jì)尚未解決的事務(wù)占用鎖以及嚴(yán)重的阻塞。（有關(guān)事務(wù)處理和阻塞的詳細(xì)信息，請(qǐng)參閱 INF: Understanding and Resolving SQLServer 7.0 or 2000 Blocking Problems）。應(yīng)用程序使用連接池來(lái)維護(hù) Web 站點(diǎn)，因此，隨著更多的連接被阻塞，Web 站點(diǎn)創(chuàng)建了更多的連接，而這些連接又會(huì)被阻塞，該循環(huán)會(huì)一直持續(xù)下去。

在大約 45 秒鐘之后，該日志寫操作將完成，但到此時(shí)為止，數(shù)以百計(jì)的連接已經(jīng)積累起來(lái)，從而導(dǎo)致阻塞問(wèn)題，并使得 SQL Server 和應(yīng)用程序需要花費(fèi)幾分鐘的時(shí)間進(jìn)行恢復(fù)。當(dāng)與應(yīng)用程序問(wèn)題結(jié)合起來(lái)的時(shí)候，延遲 I/O 狀況會(huì)對(duì)系統(tǒng)產(chǎn)生非常消極的影響。

解決辦法：這歸因于 HBA 驅(qū)動(dòng)程序中的延遲 I/O 請(qǐng)求。計(jì)算機(jī)具有多個(gè)帶有故障轉(zhuǎn)移支持的 HBA 卡。故障轉(zhuǎn)移超時(shí)值被配置為 45 秒。當(dāng)一個(gè) HBA 落后或者在 45 秒鐘或更長(zhǎng)時(shí)間內(nèi)未與 SAN 通信時(shí)，該 I/O 請(qǐng)求被路由到第二個(gè) HBA 進(jìn)行處理，并且會(huì)很快完成。硬件產(chǎn)品的推薦故障轉(zhuǎn)移設(shè)置為 5 秒鐘，以便避免出現(xiàn)這樣的延遲狀況。 如果在 SQL Server 2000 SP4 中已經(jīng)有了新的自動(dòng)報(bào)告該狀況的功能，那么我們?cè)谝呻y解答過(guò)程中就可以很快知道，基本問(wèn)題是由于 SQL Server 外部的問(wèn)題而發(fā)生的阻塞或延遲 I/O 操作。事實(shí)上，我們花費(fèi)了大量時(shí)間來(lái)解決一個(gè)在最初呈現(xiàn)為普通性能問(wèn)題的問(wèn)題。

示例 2 — 篩選器驅(qū)動(dòng)程序干預(yù)：

許多防病毒軟件和備份產(chǎn)品使用 I/O 篩選器驅(qū)動(dòng)程序。這些篩選器驅(qū)動(dòng)程序成為 I/O 請(qǐng)求棧的一部分，并且可以訪問(wèn) IRP 請(qǐng)求。Microsoft 技術(shù)支持部門已經(jīng)遇見(jiàn)過(guò)各種問(wèn)題 — 從導(dǎo)致阻塞 I/O 的錯(cuò)誤到篩選器驅(qū)動(dòng)程序?qū)崿F(xiàn)中的延遲狀況。

其中，Microsoft SQL Server 技術(shù)支持部門遇到的一種情況是，涉及到用于備份處理（該過(guò)程能夠備份在備份時(shí)處于打開(kāi)狀態(tài)的文件）的篩選器驅(qū)動(dòng)程序。系統(tǒng)管理員錯(cuò)誤地在文件備份選擇中包括了 SQL Server 數(shù)據(jù)文件目錄。當(dāng)備份發(fā)生時(shí)，它試圖收集備份開(kāi)始時(shí)文件的一致鏡像。在完成該操作時(shí)，它將延遲后續(xù)的 I/O 請(qǐng)求，使它們能夠在軟件處理它們時(shí)逐個(gè)完成。

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

解決辦法：將 SQL Server 的數(shù)據(jù)文件從文件備份過(guò)程中排除，并且解決篩選器驅(qū)動(dòng)程序中的導(dǎo)致文件被置于單 I/O 模式的程序錯(cuò)誤。

示例 3 — 隱藏的錯(cuò)誤：

很多高端系統(tǒng)具有用于處理負(fù)載平衡的多通道 I/O 路徑以及類似的工具。Microsoft SQL Server 技術(shù)支持部門已經(jīng)見(jiàn)過(guò)使用此類軟件的情況，其中，盡管 I/O 請(qǐng)求失敗，但軟件確實(shí)正確地處理了錯(cuò)誤狀況，并且執(zhí)行了無(wú)數(shù)次重試。I/O 被阻塞，并且 SQL Server 無(wú)法完成指定的操作。與上面描述的日志寫狀況非常類似，在這樣的狀況對(duì)系統(tǒng)產(chǎn)生了消極影響之后，發(fā)生了很多糟糕的系統(tǒng)行為。

解決辦法：在類似情況下，重新啟動(dòng) SQL Server 可以在一定程度上緩解問(wèn)題，但是，有時(shí)需要重新啟動(dòng) Windows 來(lái)使處理恢復(fù)到正常狀態(tài)。當(dāng)然，I/O 子系統(tǒng)中的程序錯(cuò)誤最終需要由 I/O 供應(yīng)商解決。

SQL Server 2000 SP4 的新的對(duì)此類狀況進(jìn)行自動(dòng)報(bào)告的功能使得類似問(wèn)題的檢測(cè)變得更加容易。我們不僅可以看到整個(gè)服務(wù)器的總體性能下降，而且還可以通過(guò) SP4 所記錄的新消息洞察問(wèn)題的本質(zhì)，并且知道該問(wèn)題很可能出在 SQL Server 外部。

示例 4 — 遠(yuǎn)程存儲(chǔ)/鏡像/RAID 驅(qū)動(dòng)器：

很多系統(tǒng)使用鏡像或類似的技術(shù)來(lái)幫助防止丟失數(shù)據(jù)。其中一些系統(tǒng)是基于軟件的，而其他系統(tǒng)是基于硬件的。Microsoft SQL Server 技術(shù)支持部門經(jīng)常遇到的與這些系統(tǒng)有關(guān)的情況是延遲增加。

當(dāng)針對(duì)鏡像的 I/O 必須在 I/O 操作被視為完成之前成功完成時(shí)，這顯然會(huì)增加總體 I/O 時(shí)間。對(duì)于遠(yuǎn)程鏡像安裝，網(wǎng)絡(luò)延遲和重試可能成為一個(gè)不利因素。當(dāng)發(fā)生驅(qū)動(dòng)器故障并且 RAID 子系統(tǒng)重新生成時(shí)，I/O 吞吐量可能會(huì)受到影響。

解決辦法：在類似情況下，我們通常建議使用嚴(yán)格的配置設(shè)置（這隨供應(yīng)商和設(shè)備而異），以減少鏡像延遲和 RAID 重新生成操作。

RAID 系統(tǒng)開(kāi)銷和延遲可能導(dǎo)致 I/O 變慢，而 SQL Server 對(duì)此無(wú)能為力。就像任何其他應(yīng)用程序一樣，它是 RAID 硬件和驅(qū)動(dòng)程序的客戶端。當(dāng)該類型的問(wèn)題使服務(wù)器的速度過(guò)度降低時(shí)，SP4 中新的延遲和阻塞 I/O 報(bào)告功能有助于查明問(wèn)題所在。

示例 5 — 壓縮：

Microsoft 不在壓縮驅(qū)動(dòng)器上支持 SQL Server 7.0 或 2000 數(shù)據(jù)和日志文件。NTFS 壓縮是不安全的，這不僅是因?yàn)樗茐牧祟A(yù)寫日志 (WAL) 協(xié)議，而且還因?yàn)樗髮?duì)每個(gè) I/O 請(qǐng)求執(zhí)行更多的處理。壓縮禁止了異步 I/O，從而導(dǎo)致所有帶有受影響數(shù)據(jù)或日志文件的 SQL Server I/O 都被同步執(zhí)行。

解決辦法：在這種情況下，我們總是建議客戶解壓縮他們的數(shù)據(jù)和日志文件。

NTFS 壓縮可能導(dǎo)致 I/O 變慢，而 SQL Server 對(duì)此無(wú)能為力。就像任何其他用戶模式應(yīng)用程序一樣，它是文件系統(tǒng)的客戶端。當(dāng)壓縮對(duì) SQL Server I/O 操作產(chǎn)生不利影響時(shí)，SP4 中新的延遲和阻塞 I/O 報(bào)告功能有助于查明問(wèn)題所在。

附加數(shù)據(jù)點(diǎn)

系統(tǒng)進(jìn)程中提供的等待類型信息可能有助于診斷 I/O 瓶頸。緩沖區(qū) I/O 鎖存器等待類型和寫日志等待是調(diào)查 I/O 路徑性能的關(guān)鍵指標(biāo)。Microsoft 知識(shí)庫(kù)文章 822101: The waittype and lastwaittype fields in the sysprocesses table 概述了等待類型，并且詳細(xì)介紹了與診斷延遲或阻塞 I/O 狀況有關(guān)的 I/O 等待類型。