步驟1.定位問題

常見的等待類型

• • • 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_的相關等待：

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

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

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

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

註 : 當你的系統出現大量的 PAGEIOLATCH_ 類等待，說明你磁碟可能存在壓力（磁碟速度不能滿足當前業務需求）或你的記憶體不夠用，不能緩存業務常用數據而經常要與磁碟交互！

WRITELOG  和磁碟有關的另一個等待狀態，正在等待寫日誌記錄，意味著寫入速度也明顯跟不上。而速度跟不上一般有兩種情況：磁碟壓力大響應時間長或真的速度不能滿足讀寫需要。

PAGELATCH_ 

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

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

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

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

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

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

TempDB造成的 PAGELATCH_（其實也是一種Hot Page），這裡簡單的看一個例子：

　　　　系統中存在大量的 PAGELATCH_UP等待那麼是什麼成為了Hot Page 呢？為什麼說和TempDB有關呢？

 　　　　等待資源 “2:X:X： ”開頭是TempDB，系統中存在大量且高併發的語句使用臨時表和表變數，所以引起TEMPDB瓶頸。請參見：TempDB的診斷和優化。

LCK_ 

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

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

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

ASYNC_NETWORK_IO 

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

1. 出現這種情況一般不是資料庫的問題，調整資料庫配置不會有大的幫助。
2. 網路層的瓶頸當然是一個可能的原因：對此要考慮是否真有必要返回那麼多數據？
3. 應用程式端的性能問題，也會導致SQLServer里的ASYNC_NETWORK_IO等待。如果見到了這個類型的等待，就要檢查應用程式的健康狀況，也要檢查應用是否有必要想SQLServer申請這麼大的結果集。
4. 程式返回結果集的方式 。