第22/24周 等待和I/O延遲統計

大家好，歡迎回到性能調優教育訓練的第22周。上周我談了SQL Server裡的基線，今天我們繼續，談下SQL Server裡的等待和I/O延遲統計。當我進行SQL伺服器健康檢查時，我總會使用這2個次元全局掌握下SQL Server的健康狀況。

等待統計（Wait Statistics）

在SQL Server裡每次你執行一個查詢，查詢會等待。初次看這個看起來很慘淡，但其實有一個非常好的原因，在SQL Server裡總會等待。每次一個查詢等待，SQL Server通過所謂的等待統計（Wait Statistics）來跟蹤這些等待。在我們讨論等待統計本身前。我想介紹下為什麼在執行期間，查詢總會等待。等待的概念主要基于2個原則：

• 非同步資源等待（Asynchronous Resource Waiting）
• 協同排程（Cooperative Scheduling）

我們來詳細看下這2個。每次查詢等待一個目前不可用的資源——例如在緩存池理還沒緩存的頁，或者因為另一個不相容的鎖而不能獲得的鎖——查詢會進入SQL Server裡所謂的挂起（Suspended）狀态。查詢在挂起狀态一直等待直到資源變成可用。

當資源變成可用時，查詢進入所謂的可執行（Runnable）狀态，再次等待，知道CPU變成可用。當CPU是可用時，查詢最後進入運作（Running）狀态，執行到資源再次變成不可用。當這個發生時，查詢再次進入挂起（Suspended）狀态。下圖顯示了這個查詢生命周期。

另外查詢也會由于在SQLOS（SQL Server作業系統）裡SQL Server實作的協同排程（Cooperative Scheduling）而等待。SQL Server通過使用特定的WIN32 API功能排程它的線程。協同排程意味着當一個查詢本身超過近4ms的額（quantum ）時，它從CPU上撤離。因為這個實作方式，在SQL Server裡查詢總會等待：一旦一個資源尚不可用，或者查詢已超過了它的額——查詢就會進入挂起（Suspended）狀态并等待。

每次當一個等待情況發生時，等待時間被SQL Server通過等待統計（Wait Statistics）自動跟蹤。SQL Server通過DMV sys.dm_os_wait_stats 報告這些資訊。通過這個DMV傳回的每一行都代表SQL Server裡的一個特定等待——所謂的等待類型（Wait Type）。通過評估等待統計，SQL Server告訴你什麼是最突出的等待類型。然後你可以聚焦這個等待類型并找出内部問題根源，還有對于這個等待類型為什麼等待時間如此高。

I/O延遲統計（I/O Latency Statistics）

除了等待統計外另一個非常重要的是SQL Server也會報告的I/O延遲統計（I/O Latency Statistics）。有了這些延遲時間很容易找出你的SQL Server執行個體哪個檔案有延遲時間。SQL Server通過DMF sys.dm_io_virtual_file_stats來報告這些資訊。你可以傳入database_id和file_id。如果你對這2個值都提供NULL值的話，你會得到SQL Server執行個體（資料和日志）所有查詢相關檔案的延遲統計。

對于這個DMF最重要的是io_stall_read_ms和io_stall_write_ms列。自上次SQL Server重新開機後，對你的存儲進行讀寫操作所發生的累積延遲時間。如果你把這2個值除以num_of_read和num_of_writes列，你就得到從SQL Server角度來說，對于磁盤讀寫的平均延遲時間。這對于你的存儲子系統的故障排除非常友善。

如果這個DMF報告非常高的延遲時間，你不應該簡單的跑到存儲供應商那裡并買更快的存儲。第一步你總要想下為什麼你有這麼高的延遲時間。當我在不同的系統上使用這個DMF時，TempDb總會報告很高的延時。但這也不意味着你要把TempDb移到更快的存儲，例如SSD硬碟。第一步總要思考下，對于你特定的資料庫“為什麼”你有這麼高的延遲時間。如果是TempDb的話你可以嘗試最小化TempDb的使用——例如應用合理的索引政策來擺脫執行計劃裡的排序和哈希運算符，這2個運算符會蔓延到TempDb。

等待統計和I/O延遲統計直報告你症狀，你的任務是找出性能問題的内在根源，分析它，最後解決它。

小結

在今天的性能調優教育訓練裡我們詳細讨論了SQL Server裡的等待統計和I/O延遲統計。對于性能監控和故障排除來說，這2個DMVs/DMFs非常重要，因為你從中可以找出SQL Server目前在哪些領域有性能問題。下周我們會詳細談下TempDB，我把它叫做SQL Server的公共廁所。請繼續關注！

圍觀PPT:

1123_22_等待統計.rar

注：此文章為

WoodyTu

學習MS SQL技術，收集整理相關文檔撰寫，歡迎轉載，請在文章頁面明顯位置給出此文連結！

若您覺得這篇文章還不錯請點選下右下角的推薦，有了您的支援才能激發作者更大的寫作熱情，非常感謝！