透過等待看資料庫 - tw511教學網

等待分類與解決基本流程

步驟1.定位問題

系統等待往往能直觀的反映出系統問題。通過一些常見的等待型別，同樣可以找到系統瓶頸，結合效能計數器往往定位更準確。

如：系統中存在大量IO類等待，那麼可能表示你的磁碟或記憶體是語句執行緩慢的原因，也是系統的瓶頸所在。

常見的等待型別：

CXPACKET：當嘗試同步查詢處理器交換迭代器時出現。如果針對該等待型別的爭用成為問題時，可以考慮降低並行度。
IO_COMPLETION：在等待 I/O 操作完成時出現。通常，該等待型別表示非資料頁 I/O。
PAGEIOLATCH_：在任務等待 I/O 請求中緩衝區的閂鎖時發生。
PAGELATCH_：在任務等待不處於 I/O 請求中的緩衝區閂鎖時發生。
LCK_：等待閂鎖時出現。
ASYNC_NETWORK_IO：當任務被阻止在網路之後時出現在網路寫入中。驗證使用者端是否正在處理來自伺服器的資料。
OLEDB：當SQL Server 呼叫 Microsoft SQL Native Client OLE DB 存取介面時出現。該等待型別不用於同步。而是用於指示呼叫 OLE DB 存取介面的持續時間。
WRITELOG：等待紀錄檔重新整理完成時出現。導致紀錄檔重新整理的常見操作是檢查點和事務提交。

步驟2.分析

問題與解決

CXPACKET

CXPACKET 這個等待可以簡單理解成CPU相關的等待，主要發生在並行計劃中。由於並行計劃需要協同多個task同時工作，那麼「協同」分配等等操作的時候出現的就是這個等待。

如果 CXPACKET 在你係統中是最為嚴重的等待，這時候一般的表現是你的CPU很高。

解決方案：適當調整並行度。

一般建議系統如果超過32個CPU 那麼設定成8或者4，如果系統中都是特別短小且頻繁的語句建議設定成1（取消語句並行，要慎重真的符合你的場景才好）

並行開銷的閥值，主要控制SQL優化器何時選用並行計劃，建議預設值，此值設定的越小優化器越容易選擇並行計劃。

並行度的設定是針對範例級別的設定（2016中可以對單獨資料庫設定）。

IO類

IO_COMPLETION和PAGEIOLATCH_和WRITELOG 這三個等待是最為常見的和磁碟相關的等待。他們的不同點是IO_COMPLETION 主要針對非資料頁 I/O ，如備份操作所需的磁碟互動。PAGEIOLATCH_ 是資料頁相關的磁碟等待。WRITELOG是紀錄檔相關。如果系統中這三個等待是主要等待，說明系統磁碟存在壓力或已經成為瓶頸。

這裡用PAGEIOLATCH_ 為例進行說明

PAGEIOLATCH_的官方解釋：在任務等待 I/O 請求中緩衝區的閂鎖時發生。閂鎖請求處於「XX」模式。長時間的等待可能指示磁碟子系統出現問題。

PAGEIOLATCH_的相關等待：

PAGEIOLATCH_DT	在任務等待 I/O 請求中緩衝區的閂鎖時發生。閂鎖請求處於「破壞」模式。長時間的等待可能指示磁碟子系統出現問題。
PAGEIOLATCH_EX	在任務等待 I/O 請求中緩衝區的閂鎖時發生。閂鎖請求處於「獨佔」模式。長時間的等待可能指示磁碟子系統出現問題。
PAGEIOLATCH_KP	在任務等待 I/O 請求中緩衝區的閂鎖時發生。閂鎖請求處於「保持」模式。長時間的等待可能指示磁碟子系統出現問題。
PAGEIOLATCH_NL	僅供內部使用。
PAGEIOLATCH_SH	在任務等待 I/O 請求中緩衝區的閂鎖時發生。閂鎖請求處於「共用」模式。長時間的等待可能指示磁碟子系統出現問題。
PAGEIOLATCH_UP	在任務等待 I/O 請求中緩衝區的閂鎖時發生。閂鎖請求處於「更新」模式。長時間的等待可能指示磁碟子系統出現問題。

怎麼來理解這個官方解釋呢？首先明確一點，作業系統CPU操作的任何資料都是從記憶體中讀取的，也就是說讀取資料要經過這樣的一條路：

磁碟中 ——> 記憶體中 ——> 最終使用

這裡的PAGEIOLATCH_ 就是發生在，磁碟中 ——> 記憶體中

以讀取為例：要讀取的資料頁不在記憶體中，所以就要去磁碟上讀取這部分資料頁，去磁碟讀取資料的時候就會產生PAGEIOLATCH_的相關等待，如果磁碟壓力大，長時間不能反回資料，那麼PAGEIOLATCH_的時間也會越長，語句執行的時間也會越長。

注 : 當你的系統出現大量的 PAGEIOLATCH_ 類等待，說明你磁碟可能存在壓力（磁碟速度不能滿足當前業務需求）或你的記憶體不夠用，不能快取業務常用資料而經常要與磁碟互動！
WRITELOG和磁碟有關的另一個等待狀態，正在等待寫紀錄檔記錄，意味著寫入速度也明顯跟不上。而速度跟不上一般有兩種情況：磁碟壓力大響應時間長或真的速度不能滿足讀寫需要。

PAGELATCH_

PAGELATCH_和上面講述的PAGEIOLATCH_ 看似很像，但中間少了 IO 這個關鍵。

磁碟中 ——> 記憶體中 ——> 最終使用

磁碟中——>記憶體中的等待為PAGEIOLATCH_ 而記憶體中——> 最終使用的等待為 PAGELATCH_

當資料已經在記憶體中的時候SQL SERVER 想要使用這個資料頁就要給這個資料頁加鎖。

當等待中出現很多PAGELATCH_ 等待，那麼可以說明：

SQL Server沒有明顯的記憶體和磁碟瓶頸。
應用程式發來大量的並行語句在修改同一張表格裡的記錄，而表格架構設計以及使用者業務邏輯使得這些修改都集中在同一個頁面，或者數量不多的幾個頁面上。這些頁面有的時候也被稱為Hot Page。這樣的瓶頸通常只會發生在並行使用者比較多的、典型的OLTP系統上。
這種瓶頸是無法通過提高硬體設定解決的，只有通過修改表格設計或者業務邏輯，讓修改分散到儘可能多的頁面上，才能提高並行效能。

LCK_

LCK_型別中的所有很多，如果這種等待在系統中大量存在，可以說明，系統語句間的相互阻塞嚴重。如大家都知道的當你update一張表的時候，你的select會被阻塞直到update完成。這裡就不過多介紹場景了，主要看一下解決此類等待的主要方法：

語句優化，讓語句執行的更快，減少等待時間。
採用批次操作代替迴圈方式。
儘量減少事務的長度。
嘗試降低事務隔離級別。
上述都不能緩解...請選用讀寫分離。

LCK_型別中包含：（這裡不做詳細解讀了）

LCK_M_RIn_NL	當某任務正在等待獲取當前鍵值上的 NULL 鎖以及當前鍵和上一個鍵之間的插入範圍鎖時出現。鍵上的 NULL 鎖是指立即釋放的鎖。有關鎖相容性矩陣，請參閱：sys.dm_tran_locks
LCK_M_RIn_S	當某任務正在等待獲取當前鍵值上的共用鎖以及當前鍵和上一個鍵之間的插入範圍鎖時出現。有關鎖相容性矩陣，請參閱 sys.dm_tran_locks。
LCK_M_RIn_U	任務正在等待獲取當前鍵值上的更新鎖以及當前鍵和上一個鍵之間的插入範圍鎖。有關鎖相容性矩陣，請參閱sys.dm_tran_locks。
LCK_M_RIn_X	當某任務正在等待獲取當前鍵值上的排他鎖以及當前鍵和上一個鍵之間的插入範圍鎖時出現。有關鎖相容性矩陣，請參閱 sys.dm_tran_locks。
LCK_M_RS_S	當某任務正在等待獲取當前鍵值上的共用鎖以及當前鍵和上一個鍵之間的共用範圍鎖時出現。有關鎖相容性矩陣，請參閱 sys.dm_tran_locks。
LCK_M_RS_U	當某任務正在等待獲取當前鍵值上的更新鎖以及當前鍵和上一個鍵之間的更新範圍鎖時出現。有關鎖相容性矩陣，請參閱 sys.dm_tran_locks。
LCK_M_RX_S	當某任務正在等待獲取當前鍵值上的共用鎖以及當前鍵和上一個鍵之間的排他範圍鎖時出現。有關鎖相容性矩陣，請參閱 sys.dm_tran_locks。
LCK_M_RX_U	當某任務正在等待獲取當前鍵值上的更新鎖以及當前鍵和上一個鍵之間的排他範圍鎖時出現。有關鎖相容性矩陣，請參閱 sys.dm_tran_locks。
LCK_M_RX_X	當某任務正在等待獲取當前鍵值上的排他鎖以及當前鍵和上一個鍵之間的排他範圍鎖時出現。有關鎖相容性矩陣，請參閱 sys.dm_tran_locks。
LCK_M_S	當某任務正在等待獲取共用鎖時出現。有關鎖相容性矩陣，請參閱 sys.dm_tran_locks。
LCK_M_SCH_M	當某任務正在等待獲取架構修改鎖時出現。有關鎖相容性矩陣，請參閱 sys.dm_tran_locks。
LCK_M_SCH_S	當某任務正在等待獲取架構共用鎖時出現。有關鎖相容性矩陣，請參閱 sys.dm_tran_locks。
LCK_M_SIU	當某任務正在等待獲取共用意向更新鎖時出現。有關鎖相容性矩陣，請參閱 sys.dm_tran_locks。
LCK_M_SIX	當某任務正在等待獲取共用意向排他鎖時出現。有關鎖相容性矩陣，請參閱 sys.dm_tran_locks。
LCK_M_U	當某任務正在等待獲取更新鎖時出現。有關鎖相容性矩陣，請參閱 sys.dm_tran_locks。
LCK_M_UIX	當某任務正在等待獲取更新意向排他鎖時出現。有關鎖相容性矩陣，請參閱 sys.dm_tran_locks。
LCK_M_X	當某任務正在等待獲取排他鎖時出現。有關鎖相容性矩陣，請參閱 sys.dm_tran_locks。

ASYNC_NETWORK_IO

此等待狀態出現在SQLServer已經把資料準備好，但是網路沒有足夠的傳送速度跟上，所以SQLServer的資料沒地方存放。

出現這種情況一般不是資料庫的問題，調整資料庫設定不會有大的幫助。
網路層的瓶頸當然是一個可能的原因：對此要考慮是否真有必要返回那麼多資料？
應用程式端的效能問題，也會導致SQLServer裡的ASYNC_NETWORK_IO等待。如果見到了這個型別的等待，就要檢查應用程式的健康狀況，也要檢查應用是否有必要想SQLServer申請這麼大的結果集。
程式返回結果集的方式。
北京格瑞趨勢科技有限公司是聚焦於資料服務的高新技術企業，成立於2008年，創始團隊及核心技術人員來自微軟和雅虎。微軟資料平臺金牌合作伙伴，衛寧健康資料平臺戰略合作伙伴。通過產品+服務雙輪驅動的業務模式，14年間累計服務4000+客戶，覆蓋網際網路、市政、交通、電信、醫療、教育、電力、製造業等各個領域。