2017年5月17日 星期三

SQLSERVER wait type之SOS_SCHEDULER_YIELD

如同在「SQL Server的等待分析」中所說,SQLSERVER會給一個處於RUNNINGthread一個限量的時間(4ms),如果這個thread在這個限量的時間內沒有執行完畢。若這個thread不需等待任何資源,則會將這個thread退回到Queue中(RUNNABLE,即使目前沒有其它的thread在等侯CPU也是一樣。

SOS_SCHEDULER_YIELD嚴格的來說,它並不是一種等待,它只是線程在4ms時間內,無法完成工作而將CPU讓出來,以避免其它線程「餓死」的一種調度做法,所以它不會顯示在sys.dm_os_waiting_tasks中。它與一般的resource wait不同,它被稱為signal wait。如果系統中沒有其它的線程在runnable中,通常它的signal wait time的時間會很短,也就是很快又會回到running的狀態。

當一個thread超過限量時間,退回到runnable時,將被標示「SOS_SCHEDULER_YIELD」,直到它回到running狀態。這段時間將被紀錄為「訊號等待時間signal wait time」。

如果一個命令執行,耗用的CPU時間不超過4ms,那麼就不會有SOS_SCHEDULER_YIELD的出現,如果你看到許多次的SOS_SCHEDULER_YIELD,表示你的系統有許多命令耗用超過4msCPU

SOS_SCHEDULER_YIELD的等待次數很高,但總的等待時間卻不高,整體而言CPU並不忙碌。這通常是無害的,僅代表有較多的命令不能在4ms的限量時間完成。例如DBA關閉了平行處理(MAXDOP=1,也有可能造成此現象。

SOS_SCHEDULER_YIELD的等待次數很高,而且總的等待時間也很高。這非常可能代表了CPU有壓力。表示你的系統有許多CPU密集型的查詢,例如許多複雜的SQL statement,如table scan、複雜或不符合SARG的查詢..等,你可能需要針對特定的語法或物件進行優化。如果伴隨著高CXPACKET的出現,則需要去調整平行處理的配置。(詳見:SQLSERVER wait type之CXPACKET

SOS_SCHEDULER_YIELD的等待次數不高,但總的等待時間卻很高。這種情況表示你SQLSERVER所在的主機,作業系統的其它程式在搶佔CPU的資源。如果你在同一台主機上同時安裝了SQLSERVERAP伺服器(或其它非DB相關的軟體),就有可能發生這種現象。或者在作業系統開啟了省電功能,也可能導致此種現象。

2017年5月15日 星期一

SQLSERVER wait type之RESOURCE_SEMAPHORE

RESOURCE_SEMAPHORE
SQL Server收到用戶查詢時,它首先建立一個已編譯(complied plan)的計劃,然後根據已編譯的計劃建立一個執行計劃(execution plan)

SQL Server建立一個已編譯的計劃時,它會計算兩個記憶體授予參數(memory grant parameters),稱為「所需記憶體required memory」和「額外記憶體additional memory」。所需記憶體是運行排序和雜湊連接(sort and hash join)所需的最小記憶體。這是必需的,因為如果沒有這個記憶體可用,查詢將不會啟動
額外的記憶體是將臨時的資料行(temporary rows)存儲在記憶體中所需的記憶體量。這被稱為額外的,如果沒有足夠的可用記憶體,則可以將查詢存儲在磁碟上。

首先,SQLSERVER計算這個查詢執行需要多少記憶體。這通常是所需記憶體和額外記憶體的總和,但如果您的查詢正在使用平行處理,則所需的記憶體將為(所需記憶體* DOP。再加上「額外記憶體」。這被稱為請求的記憶體(requested memory)

SQL Server中有一個稱為RESOURCE SEMAPHORE的內部設備,它被用來授予請求的記憶體給每個查詢。如果記憶體不足以授予時,那麼該查詢將處於等待記憶體的狀態,其等待類型即為「RESOURCE_SEMAPHORE」。

我們可以通過SELECT * FROM sys.dm_exec_query_resource_semaphores

來查詢每個「資源集區resource pool」已授予或未授予的記憶體大小資訊。

resource_semaphore_id 0,主要用於「一般查詢」。resource_semaphore_id1,則用於小型查詢(所謂小型查詢是指請求的記憶體 ﹤5MB,且查詢成本 ﹤3
,上圖中可以看到有100個查詢正在等待記憶體的授予。
通過SELECT * FROM sys.dm_exec_query_memory_grants可以查詢每個查詢的記憶體授予的詳細資料
grant_timegranted_memory_kbNULL,表示正在等待授予記憶體。
我們也可以通過這些資訊,找到最耗用記憶體的查詢,並且透過上面指令所得到的sql_handle欄位值,去取得SQL語法。

RESOURCE_SEMAPHORE_QUERY_COMPILE
另外有個等待類RESOURCE_SEMAPHORE_QUERY_COMPILE」主要是在編譯執行計畫時,編譯這個動作也需要記憶體。
SQLSERVER將編譯可用的記憶體分為三級(3gateway)「smallmediumbig」,剛開始都是給予small gateway級別的記憶體,但如果一個複雜的語法,small記憶體無法滿足編譯所需,則會要求進入「更上一級」記憶體授予,直至big gateway
每一級gateway可容許同時編譯的數量是有限制「Configured Unit」的,如果更上一級的gateway,因為限額已滿無法授予記憶體給此編譯動作,那麼將進入「等待」,此時的wait type即為「RESOURCE_SEMAPHORE_QUERY_COMPILE」。

SQLSERVER內建了3個編譯所需記憶體的關卡(gateway)smallMediumBig」,可以透過dbcc memorystatus得知。
1.small gateway
configure unit 通常是cpu core*4
2.Medium gateway
configure unit 通常是cpu core*1。
3.Big gateway
configure unit 通常是1。

Summary
RESOURCE_SEMAPHORE
表示目前記憶體可用空間不足以執行SQL語法。
解決方案:
一、調整SQL語法,特別是(order byhash join)。
二、擴充記憶體大小。
三、調降MAXDOP
四、清除或搬移舊資料至歷史區,降低ONLINE資料量。

RESOURCE_SEMAPHORE_QUERY_COMPILE
表示SQL語法過於複雜,或者複雜的SQL查詢太密集,系統Memory Gateway不足以編譯這些SQL
解決方案:
一、調降SQL語法的複雜度。
二、增加CPUcore數。
三、避免過度使用WITH RECOMPILE。


SQLSERVER wait type之CXPACKET

CXPACKET這種等待類型(wait type),是SQLSERVER最常見的等待類型之一。
CXPACKET這個詞來自於「Class Exchange Packet」,可以把它解釋為平行線程(thread)間的資料交換。
SQLSERVER決定使用平行處理來執行一句SQL statement時,會啟動一個協調控制線程(coordinator/control thread),線程編號為0,即thread 0,接著視所能使用的CPU核心數,分別啟動一個線程。
協調線程負責各線程間的協調,不參與SQL的執行。其餘的thread則按分配的工作同時執行。各個線程執行的時間有快有慢,先執行完的線程,需等待其它線程執行完成,這時的「等待」類型,即為CXPACKET。如下圖所示:
上圖各threads暗紅色部份的時間加總,即為執行此SQL statementCXPACKET等待時間。

thread 0CXPACKET的等待時間,其實就代表了整個SQL statement執行的時間。因此在平行處理的計畫中,CXPACKET是必然會發生的即使各線程都同時完成
通常的建議是「只要CXPACKET總時間不超過總等待時間的50%」,則不應把它視為一個問題而是一個指標。

我們可以使用下列語法,查詢各等待事件所佔的時間、佔比、累進佔比
WITH Waits AS
 (
 SELECT 
   wait_type, 
   wait_time_ms / 1000. AS wait_time_s,
   100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
   ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
 FROM sys.dm_os_wait_stats
 WHERE wait_type 
   NOT IN
     ('CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE',
   'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR',
   'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT')
   ) -- filter out additional irrelevant waits
   
SELECT W1.wait_type,
 CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
 CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
 CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
 INNER JOIN Waits AS W2 ON W2.rn <= W1.rn
GROUP BY W1.rn, 
 W1.wait_type, 
 W1.wait_time_s, 
 W1.pct

HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold;
CXPACKET雖然是因平行處理而產生,但產生高的CXPACKET並不一定是「平行處理的問題」,常常它代表了其它問題的症狀。

例如CXPACKET如果伴隨著高的PAGEIOLATCH_*(可能還會見到IO_COMPLETION, ASYNC_IO_COMPLETION),這很大的背後原因是表示I/O子系統無法滿足平行處理的需求。這種情況下,你如果去調整最大並行度,或許會看到CXPACKET降低了,但其實你的系統效能(AP執行的反應時間)並不會得到提昇,問題沒有得到根本的解決。
這種情況,應首先考慮I/O的優化。
當然,直接去昇級I/O的硬體(例如換成SSD)也是一種方法,只是這有點強人所難了;我們可以針對相關的SQL語法/Table/index等進行優化(例如索引未匹配、碎片整理..等),以儘量減少I/O,然後再做觀察。

另外,是否會採用平行處理,以及平行處理的各個工作線程(如上圖的thread 1~4)的工作量分配,均是由系統依據統計值計算而來。如果統計值是正確的,那麼平行處理各工作線程的工作分配通常會較為平均,其CXPACKET的等待總時間應該不會太高。如果統計值過期,那就有可能不當的啟用平行處理,或者造成各線程工作分配不均,形成較大的CXPACKET等待。面對CXPACKET,也可以先嘗試由統計值更新著手。

當高CXPACKET伴隨著高SOS_SCHEDULER_YIELDLACTH_*的時候,這種情況表示「平行處理」確實是問題了,這時候你可能需要去調整系統的平行處理組態。

SQL SERVER的平行處理程度,主要受下列兩個選項的控制
cost threshold for parallelism (平行處理原則的成本臨界值)
SQL SERVER執行SQL前,先估計SQL所需的時間成本(單位為秒),如果大於上述臨界值(預設值為5),那麼就會採用平行處理來執行SQL語法。這個值該不該調整,該調成多少?讀者們可以參考下列兩篇文章(感謝PASS的好友Rock提供):

max degree of parallelism (平行處理原則的最大程度)
如果SQL SERVER決定採用平行處理,將依照max degree of parallelism選項所設定的值(threads)同時執行。0:代表無限制(最大限度)。

max degree of parallelism要設定為多少才是適當的,這個有許多種說法。主要還是要看實際的系統狀況而定。底下的表格僅為個人經驗的建議值。
上面的表格只是我個人的初始性建議,之後最好視系統實際的情況,觀察相關的效能參數,做出符合實際環境的調整。