Expert 診斷優化系列------------------透過等待看系統

　　　　上一篇我們簡單的介紹了,語句優化的三板斧,大部分語句三板斧過後,就算不成為法拉利也能是個寶馬了。

　　　　本篇主要講述幾個常見的系統等待，透過這些等待，看看系統存在什麼問題，怎麼樣解決這些問題。結合系統三巨頭（CPU，記憶體，磁碟）綜合展現系統問題和這些元素的聯繫。

　　　　首先我們舉個例子：前文提到了，一個好的SQL語句就好比一輛時速180的好車，好的系統硬體（CPU，記憶體，磁碟）就好比平坦寬闊的馬路。看似好車配好路，一定可以開的很快了！其實還忽略了一點！當你駕駛一輛法拉利跑在北京寬闊的三環上，就算你是老炮中的“三環十二少“，早高峰你能開到多少? 北京的早高峰！北京的早高峰！

　　　　這個例子就引出了系統阻塞和等待的概念，紅燈（硬體等待，如IO等待），這就是正常的等待。另外一輛車在你前面不走了或開的很慢，那麼你也只能等待（也可以說成你被他阻塞了）！

--------------博客地址---------------------------------------------------------------------------------------

Expert 診斷優化系列 http://www.cnblogs.com/double-K/

廢話不多說，直接開整-----------------------------------------------------------------------------------------

• 如何判斷任務或語句是否在等待？

　　　　SQL SERVER所有任務的狀態大致有三類（sleeping、runnable或running）通過英文我想不用過多解釋了。SQL DMV視圖提供了以下三個視圖詳細查詢語句的運行狀態：

• • • Sys.dm_exec_requests ：返回有關在SQL Server中執行的每個請求的信息，包括當前的等待狀態
    • Sys.dm_exec_sessions ：對於每個通過身份驗證的會話都返回相應的一行。此時圖是伺服器範圍的視圖。此視圖首先可以查到伺服器負荷
    • Sys.dm_exec_connections ：  返回與SQL Server 實例建立的連接有關的信息以及每個連接的詳細信息
    • sys.dm_os_wait_stats ：SQL Server啟動以來所有等待狀態的等待數和等待時間。這是個累積值。

註：常用查看系統等待的語句文章最後奉上

• 常見的等待類型
• 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 :等待日誌刷新完成時出現。導致日誌刷新的常見操作是檢查點和事務提交。　　　

　　註：等待有很多種，這裡主要以這八個等待為例，用普通話講解這八個等待的意義。並結合例子看看不同的等待中能反應出你SQL SERVER 系統中的哪些問題！

• CXPACKET 

　　　　CXPACKET 這個等待可以簡單理解成CPU相關的等待，主要發生在並行計劃中。由於並行計劃需要協同多個task同時工作，那麼“協同”分配等等操作的時候出現的就是這個等待，另外當並行使用的多個task其中一個被阻塞，在sys.dm_exec_requests查看wait_type等待類型也會是CXPACKET。詳見：sys.dm_os_waiting_tasks 引發的疑問(上)

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

　　　　解決方案：適當調整並行度。具體設置請參見： 　　Expert 診斷優化系列------------------你的CPU高麽？

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

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

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

• IO_COMPLETION 和 PAGEIOLATCH_ 和 WRITELOG 
  

　　　　這三個等待是最為常見的和磁碟相關的等待。他們的不同點是 IO_COMPLETION  主要針對非數據頁 I/O ，如備份操作所需的磁碟交互。PAGEIOLATCH_ 是數據頁相關的磁碟等待。WRITELOG 是日誌相關。

　　　　如果系統中這三個等待是主要等待，說明系統磁碟存在壓力或已經成為瓶頸。

　　　　這裡用PAGEIOLATCH_ 為例進行說明

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

　　　　PAGEIOLATCH_的相關等待：

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

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

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

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

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

　　磁碟與記憶體壓力的判斷，請參見：Expert 診斷優化系列------------------冤枉磁碟了

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

• PAGELATCH_ 

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

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

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

　　當數據已經在記憶體中的時候SQL SERVER 想要使用這個數據頁就要給這個數據頁加鎖，sql server中PAGELATCH_x和PAGEIOLATCH_x解析

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

　　　　高能預警 ： 網上很多人介紹過 PAGELATCH_ 等待，但是很少人有提及TempDB造成的 PAGELATCH_（其實也是一種Hot Page），這裡簡單的看一個例子：

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

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

• LCK_ 

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

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

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

• ASYNC_NETWORK_IO 

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

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

--------------博客地址---------------------------------------------------------------------------------------

Expert 診斷優化系列 http://www.cnblogs.com/double-K/

-----------------------------------------------------------------------------------------------------

　　總結：系統等待往往能直接反應出系統問題。本文主要介紹了 CXPACKET —— CPU，PAGEIOLATCH_ —— 磁碟、記憶體的聯繫。

　　　　　等待是系統中不能避免的，但通過語句優化，結構設計優化都能緩解這些阻塞。

　　　　　語句的慢和等待有著密不可分的聯繫。

　　　　　出現CXPACKET 一般考慮降低並行度，PAGEIOLATCH_ 一般考慮記憶體和磁碟（一般情況語句優化可以解決），WRITELOG 一般意味著寫入速度跟不上（如果程式對磁碟的衝擊已經降到最小還是跟不上，那麼才意味著你需要更好的硬體了）。

 -------------------------乾貨到了--------------------------------------------------------------------------

運行語句監控（非常好用哦~）

WITH sess AS
(
    SELECT
        es.session_id,
        database_name = DB_NAME(er.database_id),
        er.cpu_time,
        er.reads,
        er.writes,
        er.logical_reads,
        login_name,
        er.status,
        blocking_session_id,
        wait_type,
        wait_resource,
        wait_time,
        individual_query = SUBSTRING (qt.text, (er.statement_start_offset/2)+1, ((CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2)+1),
        parent_query = qt.text,
        program_name,
        host_name,
        nt_domain,
        start_time,
        DATEDIFF(MS,er.start_time,GETDATE()) as duration,
        (SELECT query_plan FROM sys.dm_exec_query_plan(er.plan_handle)) AS query_plan
    FROM
        sys.dm_exec_requests er
        INNER JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id
        CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
    WHERE
        es.session_id > 50
        AND es.session_Id NOT IN (@@SPID)
)
SELECT
    *
FROM
    sess
UNION ALL SELECT
    es.session_id,
    database_name = '',
    0,
    0,
    0,
    0,
    login_name,
    es.status,
    0,
    '',
    '',
    '',
    qt.text,
    parent_query = qt.text,
    program_name,
    host_name,
    nt_domain,
    es.last_request_start_time,
    DATEDIFF(MS,es.last_request_start_time,GETDATE()) as duration,
    NULL AS query_plan
FROM
    sys.dm_exec_sessions es
    INNER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id
    CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle)as qt
WHERE
    ec.most_recent_session_id IN
    (
        SELECT blocking_session_id FROM sess WHERE blocking_session_id NOT IN(SELECT DISTINCT session_id FROM sess)
    )
ORDER BY
    1, 2

 ----------------------------------------------------------------------------------------------------

註：此文章為原創，歡迎轉載，請在文章頁面明顯位置給出此文鏈接！
若您覺得這篇文章還不錯請點擊下右下角的推薦，非常感謝！

　　引用高大俠的一句話 ：“拒絕SQL Server背鍋，從我做起!”