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_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)檢視。

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對找到問題很有幫助。