SQL Server 效能調優（一）——從等待狀態判斷系統資源瓶頸

標籤：

原文: SQL Server 效能調優（一）——從等待狀態判斷系統資源瓶頸

通過DMV查看當時SQL SERVER所有任務的狀態（sleeping、runnable或running）

2005、2008提供了以下三個視圖工詳細查詢：

DMV	用處
Sys.dm_exec_requests	返回有關在SQL Server中執行的每個請求的資訊，包括當前的等待狀態
Sys.dm_exec_sessions	對於每個通過身分識別驗證的會話都返回相應的一行。此時圖是伺服器範圍的視圖。此視圖首先可以查到伺服器負荷
Sys.dm_exec_connections	返回與SQL Server 執行個體建立的串連有關的資訊以及每個串連的詳細資料

 

Sys.sysprocesses是為了向後相容，所以建議使用以上3個DMV。

 

另外還有一個DMV：sys.dm_os_wait_stats可以返回從SQL Server啟動以來所有等待狀態的等待數和等待時間。是個累積值。

 

1、  LCK_XX類型：

如果SQL Server經常有阻塞發生，會經常看到以“LCK_”開頭的等待狀態：

等待狀態	說明
LCK_M_BU	正在等待擷取大容量更新鎖定（BU）
LCK_M_IS	等待擷取意圖共用鎖（IS）
LCK_M_IU	等待擷取意向更新鎖定（IU）
LCK_M_IX	等待意向排它鎖（IX）
LCK_M_RIn_NL	等待擷取當前索引值上的NULL鎖以及當前剪和上一個鍵之間的插入範圍鎖
LCK_M_RIn_S	等待擷取當前索引值上的共用鎖定以及當前鍵和上一個鍵之間的插入範圍鎖
LCK_M_RIn_U	等待擷取當前索引值上的更新鎖定以及當前鍵和上一個鍵之間的插入範圍鎖
LCK_M_RIn_X	等待擷取當前索引值上的獨佔鎖定以及當前鍵和上一個鍵之間的插入範圍鎖
LCK_M_RS_S	等待擷取當前索引值上的共用鎖定以及當前鍵和上一個鍵之間的共用範圍鎖
LCK_M_RS_U	等待擷取當前索引值上的更新鎖定以及當前鍵和上一個鍵之間的共用範圍鎖
LCK_M_RX_S	等待擷取當前索引值上的共用鎖定以及當前鍵和上一個鍵之間的排他範圍鎖
LCK_M_RX_S	等待擷取當前索引值上的共用鎖定以及當前鍵和上一個鍵之間的排他範圍鎖
LCK_M_RX_U	等待擷取當前索引值上的更新鎖定以及當前鍵和上一個鍵之間的排他範圍鎖
LCK_M_RX_X	等待擷取當前索引值上的獨佔鎖定以及當前鍵和上一個鍵之間的排他範圍鎖
LCK_M_S	等待擷取共用鎖定
LCK_M_SCH_M	等待架構修改鎖
LCK_M_SCH_S	等待擷取架構共用鎖定
LCK_M_SIU	等待共用意向更新鎖定
LCK_M_SIX	等待擷取共用意向獨佔鎖定
LCK_M_U	等待更新鎖定
LCK_M_UIX	等待更新意向獨佔鎖定
LCK_M_X	等待獨佔鎖定

2、  PAGEIOLATCH_X與WRITELOG：

在緩衝池中的資料頁面，為了同步多使用者並發，SQL Server會對記憶體的頁面加鎖。不同的是，加的是latch（輕量級的鎖），而不是lock。

如果發生PAGEIOLATCH類型的等待時，SQL Server一定是在等待某個I/O動作的完成。如果經常出現這類等待，說明磁碟速度不能滿足要求，已經成為SQL Server的瓶頸。

PAGEIOLATCH_X最常見的分兩大類：PAGEIOLATCH_SH和PAGEIOLATCH_EX，PAGEIOLATCH_SH：經常發生在使用者正想要訪問一個資料頁面，而同時SQL Server卻要把頁面從磁碟讀往記憶體。說明記憶體不夠大，觸發了SQL Server做了很多讀取頁面的工作，引發了磁碟讀的瓶頸。此時是記憶體有瓶頸。磁碟只是記憶體壓力的副產品。

PAGEIOLATCH_EX：經常發生在使用者對資料頁面做了修改。SQL Server要向磁碟回寫的時候。意味著寫的速度跟不上。這和記憶體沒直接關係。

WRITELOG：和磁碟有關的另一個等待狀態，正在等待寫日誌記錄，意味著寫入速度也明顯跟不上。

3、  PAGELATCH_X：SQLServer為瞭解決在插入資料時，到了物理層的插入衝突，所以引入了另一類頁面上的latch:PAGELATCH，當一個任務要修改頁面時，它必須先申請一個EX的latch。只有得到這個，才能修改頁面的內容。由於資料頁的修改都是在記憶體中完成，所以時間應該非常短，可以忽略不計。而PAGELATCH只是在修改過程中才出現，所以生存周期應該很短，如果出現了，說明：1、SQLServer沒有明顯的記憶體和磁碟瓶頸。2、應用程式發來大量的並發語句在修改同一張表。而設計及使用者商務邏輯使得這些修改都集中在同一個頁面，或者數量不多的幾個頁面，成為Hot Page,通常在OLTP系統上出現比較多。3、這種瓶頸無法通過提高硬體設定解決，只能通過修改表設計或者商務邏輯，讓修改分散，提高並發性。

對於Hot page的緩解方法：

（1）、換一個資料列建叢集索引，而不要在Identity的欄位上，同一時間插入有機會分散到不同的頁面上。

（2）、如果一定要在Identity的欄位上建叢集索引，建議在其他某個列上建若干個分區。

4、  Tempdb上的PAGELATCH：

資料庫不僅在資料頁面修改的時候加latch，在資料檔案的系統頁面上，例如SGAM、PFS和GAM頁面發生修改的時候，也會加latch。有時候也會成為系統瓶頸。

在建立新表需要分配空間時，SQLServer同時要修改SGAM、PFS和GAM頁面，把已指派的頁面標誌成已使用，所以這些頁面都會有所修改。但在tempdb中，這種操作會並發、反覆。資料頁的hot能通過調整表設計來緩解。對此的解決方案：

1、  建立與cpu數量相同的tempdb檔案，並且大小要相同，這樣能平均分配壓力。

2、  嚴格防止tempdb空間用盡。防止自動成長時把其中一個檔案增長，破壞平均分配。

3、  可以使用sp_helpfile來查看檔案資訊。

5、  其他資源等待：

1、  LATCH_X：

（1）、某個先前的任務出現了訪問越界異常，SQLServer強制終止了任務，但是沒有完全將它申請的資源釋放乾淨。使其成為孤兒。後面的資源就被阻塞。只要開啟SQLServer記錄檔（errorlog），看看有沒有出現過Access Violation問題，但是一般無法從使用者層面一般無法解決，只有重啟伺服器才能解決。

（2）、同時發生其他資源瓶頸，如記憶體、線程調用、磁碟等，而latch等待只是一個衍生的等待。

（3）、當某個資料檔案空間用盡，做自動成長的時候，同一個時間點只能有一個使用者任務可以做檔案自動成長動作，其他任務必須等待。

（4）、在一些特殊情況下，有可能是SQLServer自己沒有處理好並發同步，沒有使用比較最佳化的演算法，使得使用者比較容易遇到等待，一些補丁就曾修複過這類問題。

一般等待都是由其他問題衍生出來，首先要檢查SQLServer是否健康運行。是否有出現過任何異常。是否有其他資源瓶頸。

2、  ASYNC_NETWORK_IO(NETWORK_IO：2000的叫法)：

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

（1）      出現這種情況一般不是資料庫的問題，調整資料庫配置不會有大的協助。

（2）      網路層的瓶頸當然是一個可能的原因：對此要考慮是否真有必要返回那麼多資料？

（3）      應用程式端的效能問題，也會導致SQLServer裡的ASYNC_NETWORK_IO等待。如果見到了這個類型的等待，就要檢查應用程式的健康情況，也要檢查應用是否有必要想SQLServer申請這麼大的結果集。

3、  和記憶體有關的等待狀態：

當使用者任務申請記憶體暫時申請不到的時候，會出現一些特殊的等待狀態：

COEMTHREAD/SOS_RESERVEDMEMBLOCKLIST/RESOURCE_SEMAPHORE_QUERY_COMPLIE

如果在DMV上看到這些狀態，就要確認SQLServer是否存在記憶體瓶頸。

4、  SQLTRACE_X：

對於繁忙的SQLServer，開啟SQL Trace會產生負面影響。如果出現這種等待，除非迫不得已，不然應該立刻停止搜集SQL Trace

6、  最後一道瓶頸：許多任務處於runnable狀態：

如果出現這種狀態，證明很多任務可以運行但沒在運行。

Sys.dm_exec_requests/sys.sysprocesses的status列，反映了當前所有任務的狀態，如果看到好多狀態是runnable，那就要嚴肅對待，正常的SQLServer哪怕非常忙，也不應該經常看到runnable，連running的狀態都不應該很多。

如果沒有報17883/17884之類的警告，出現非常多的runnable任務可能有兩種原因：

（1）、SQLServer CPU使用率接近100%，真的沒有足夠的cpu來及時處理使用者的並發任務。此時應該最佳化最耗CPU資源的語句或者應用，或者加CPU

（2）、SQLServer CPU使用率並不高，小於50%。這時檢查sys.dm_exec_requests的task_state列，會發現很多runnable狀態。因為SQLServer除了lock和latch之外，還有一種更輕量級的同步資源：spin lock（自旋鎖）。自旋：一些不會發生長時間等待的同步資源，SQLServer會選擇讓線程在cpu上稍微等待一下，而不會將cpu資源讓出來。

可以使用DBCC SQLPERF(SPINLOCKSTATS)查看。

在2005上的64位SQLServer，當記憶體比較充裕時，會緩衝很多執行計畫，同事緩衝很多執行計畫安全上下文。在memory clerk裡，用TokenAndPermUserStore表示，當這段記憶體比較大時，並發使用者會容易遇到一種叫MUTEX的自旋鎖。可以參考：http://suppot.microsoft.com/kb/927396。這種問題只在安全上下文緩衝得太多時才容易發生，所以定期執行一下以下語句有效防止，而且對系統整體效能也沒什麼壞的影響：

DBCC FREESYSTEMCACHE(TokenAndPermUserStore)

也可以以-T4618和-T4610啟動SQLServer，讓SQLServer使用另一種緩衝管理機制。

據說2008已經改進，不容易出現自旋鎖。

7、  小結：

使用者請求的什麼周期：

1、  用戶端向SQLServer發出請求指令，經過網路層，SQLServer接收到。

在這一步中，如果指令比較長，或者比較多，會影響SQLServer接受的速度。

2、  SQLServer對收到的指令進行文法、語義檢查，編譯，產生新的執行計畫，或者找到緩衝的計劃重用：這一步耗費資源的種類比較多：

l  CPU：做檢查、編譯、產生計劃都需要計算，這一步耗費CPU資源比較多，尤其是指令複雜的時候。

l  記憶體：對於非常長的IN子句或者由幾萬、幾十萬語句組成，要花費非常大的記憶體，主要使用stolen記憶體，對於32位系統來說是很緊張的。一般會出現這些等待情況：CMEMTHREAD/SOS_RESERVEDMEMBLOCKLIST/RESOURCE_SEMAPHORE_QUERY_COMPILE，或者701錯誤。

l  表上的架構鎖（schema lock）：在編譯時間，要防止對該架構進行修改。如果並發很高，那麼會產生阻塞。

l  在SQLServer確認是否有線程的執行計畫可用時，要在記憶體中進行搜尋。可能會產生自旋鎖。

3、  運行指令：

在等到執行計畫之後，就進入運行階段，用到的資源最多。在這一步要做很多事情：

（1） 、SQLServer首先為指令的運行申請記憶體。

如果同時需要執行很多指令，可能會在記憶體上遇到困難，通常會見到：RESOURCE_SEMAPHORE_開頭的等待狀態。

（2） 、如果發現要訪問的資料不在記憶體中。

要講資料從磁碟讀到記憶體，如果發現記憶體沒有足夠的空閑頁面存放所有資料，還要做記憶體整理和paging動作，騰出足夠的空間放資料。通常簡單的等待狀態是：PAGEIOLATCH_X。

（3） 、按執行計畫，掃描或者seek記憶體中的資料頁面，講執行需要處理的記錄找出來。這一步需要申請各種各樣的鎖，以實現事務隔離。通常會引起阻塞，以LCK_開頭的那些。

（4） 、指令可能還要做一些串連或者計算工作（sum、max、sort等）

            這一步主要使用CPU。

（5） 、根據指令內容、執行計畫和資料量，SQLServer可能還會在tempdb建立一些對象，存放暫存資料表、表變數，協助做join、sort等。

此時有可能出現tempdb瓶頸。

（6） 、如果指令需要修改資料記錄，SQLServer會修改記憶體緩衝區裡的頁面內容。

由於對象在記憶體中，不會觸發磁碟寫入，但由於修改同一頁面，容易導致PAGELATCH_X的等待狀態。

（7） 、如果指令發生資料修改，在提交事務之前，SQLServer必須將相應的日誌記錄按照順序寫入記錄檔。如果瞬間日誌量太大，會出現WRITELOG的等待狀態。

（8） 、將結果集返回給用戶端：得到結果後，SQLServer會把結果集放到輸出緩衝中，等用戶端把結果集全部取走。指令才結束。如果資料集太大，會導致網路互動太多。此時容易出現：ASYNC_NETWORK_IO等待狀態。

以上的動作都要在SQLOS中首先得到一個Worker/thread，然後還要排上scheduler，在CPU上運行。

l  SQLServer所有的Worker都在忙自己的事情，就會等待，可以看到等待狀態是0x46（UMSTHREAD）。而sys.dm_os_schedulers.work_queue_count的值會不等於0

l  成功拿到worker，但在scheduler又要等待其他Worker，這時看到狀態是runnable，而sys.dm_os_schedulers.runnable_tasks_count>1。

l  拿到scheduler，進入running狀態，如果非常耗CPU，會出現cpu使用率高的現象。

l  遇到效能問題，查看sys.dm_exec_requests這類DMV對找到問題很有協助。

 

SQL Server 效能調優（一）——從等待狀態判斷系統資源瓶頸