Expert 诊断优化系列------------------透过等待看系统
上一篇我們簡單的介紹了,語句優化的三板斧,大部分語句三板斧過后,就算不成為法拉利也能是個寶馬了。為了方便閱讀給出系列文章的導讀鏈接：
SQL SERVER全面優化-------Expert for SQL Server 診斷系列
本篇主要講述幾個常見的系統等待，透過這些等待，看看系統存在什么問題，怎么樣解決這些問題。結合系統三巨頭（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類
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_ 類等待，說明你磁盤可能存在壓力（磁盤速度不能滿足當前業務需求）或你的內存不夠用，不能緩存業務常用數據而經常要與磁盤交互！
磁盤與內存壓力的判斷，請參見：Expert 診斷優化系列------------------冤枉磁盤了
?
WRITELOG ?和磁盤有關的另一個等待狀態，正在等待寫日志記錄，意味著寫入速度也明顯跟不上。而速度跟不上一般有兩種情況：磁盤壓力大響應時間長或真的速度不能滿足讀寫需要。
?
PAGELATCH_?
PAGELATCH_和 上面講述的PAGEIOLATCH_ ?看似很像，但中間少了?IO?這個關鍵。
- 磁盤中 ——> ?內存中 ——> ?最終使用 ?
磁盤中——>內存中?的等待為PAGEIOLATCH_ ??而?內存中——>?最終使用 的等待為?PAGELATCH_
當數據已經在內存中的時候SQL SERVER 想要使用這個數據頁就要給這個數據頁加鎖，sql server中PAGELATCH_x和PAGEIOLATCH_x解析
?
?
?
?
?
高能預警 ： 網上很多人介紹過?PAGELATCH_ 等待，但是很少人有提及TempDB造成的?PAGELATCH_（其實也是一種Hot Page），這里簡單的看一個例子：
?
系統中存在大量的?PAGELATCH_UP等待那么是什么成為了Hot Page?呢？為什么說和TempDB有關呢？
?
?
? 等待資源 “2:X:X： ”開頭是TempDB，系統中存在大量且高并發的語句使用臨時表和表變量，所以引起TEMPDB瓶頸。TempDB的診斷和優化請關注后續文章。
?
?
LCK_?
上面說的PAGELATCH_和PAGEIOLATCH_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的數據沒地方存放。
?
?
--------------博客地址---------------------------------------------------------------------------------------
Expert 診斷優化系列?http://www.cnblogs.com/double-K/
?
-----------------------------------------------------------------------------------------------------
總結：系統等待往往能直接反應出系統問題。本文主要介紹了 CXPACKET —— CPU，PAGEIOLATCH_ —— 磁盤、內存的聯系。
等待是系統中不能避免的，但通過語句優化，結構設計優化都能緩解這些阻塞。
語句的慢和等待有著密不可分的聯系。
出現CXPACKET 一般考慮降低并行度，PAGEIOLATCH_ 一般考慮內存和磁盤（一般情況語句優化可以解決），WRITELOG 一般意味著寫入速度跟不上（如果程序對磁盤的沖擊已經降到最小還是跟不上，那么才意味著你需要更好的硬件了）。
?
?
?
?-------------------------干貨到了--------------------------------------------------------------------------
運行語句監控（非常好用哦~）
WITH sess AS (SELECTes.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_planFROMsys.dm_exec_requests erINNER JOIN sys.dm_exec_sessions es ON er.session_id = es.session_idCROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qtWHEREes.session_id > 50AND es.session_Id NOT IN (@@SPID) ) SELECT* FROMsess UNION ALL SELECTes.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 FROMsys.dm_exec_sessions esINNER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_idCROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle)as qt WHEREec.most_recent_session_id IN(SELECT blocking_session_id FROM sess WHERE blocking_session_id NOT IN(SELECT DISTINCT session_id FROM sess)) ORDER BY1, 2?
?
?
?
?----------------------------------------------------------------------------------------------------
注：此文章為原創，歡迎轉載，請在文章頁面明顯位置給出此文鏈接！
若您覺得這篇文章還不錯請點擊下右下角的推薦，非常感謝！
引用高大俠的一句話 ：“拒絕SQL Server背鍋，從我做起!”
為了方便閱讀給出系列文章的導讀鏈接：
SQL SERVER全面優化-------Expert for SQL Server 診斷系列
總結
以上是生活随笔為你收集整理的Expert 诊断优化系列------------------透过等待看系统的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 物联网浪潮即将来袭，创业者们你需要知道这
- 下一篇： bzoj千题计划262：bzoj4868