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_id為1，則用於小型查詢（所謂小型查詢是指請求的記憶體 ﹤5MB，且查詢成本 ﹤3）

，上圖中可以看到有100個查詢正在等待記憶體的授予。

通過SELECT * FROM sys.dm_exec_query_memory_grants可以查詢每個查詢的記憶體授予的詳細資料

grant_time及granted_memory_kb為NULL，表示正在等待授予記憶體。

我們也可以通過這些資訊，找到最耗用記憶體的查詢，並且透過上面指令所得到的sql_handle欄位值，去取得SQL語法。

RESOURCE_SEMAPHORE_QUERY_COMPILE

另外有個等待類型「RESOURCE_SEMAPHORE_QUERY_COMPILE」主要是在編譯執行計畫時，編譯這個動作也需要記憶體。

SQLSERVER將編譯可用的記憶體分為三級（3個gateway）「small、medium、big」，剛開始都是給予small gateway級別的記憶體，但如果一個複雜的語法，small記憶體無法滿足編譯所需，則會要求進入「更上一級」記憶體授予，直至big gateway。

每一級gateway可容許同時編譯的數量是有限制「Configured Unit」的，如果更上一級的gateway，因為限額已滿無法授予記憶體給此編譯動作，那麼將進入「等待」，此時的wait type即為「RESOURCE_SEMAPHORE_QUERY_COMPILE」。

SQLSERVER內建了3個編譯所需記憶體的關卡(gateway)「small、Medium、Big」，可以透過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 by或hash join）。

二、擴充記憶體大小。

三、調降MAXDOP。

四、清除或搬移舊資料至歷史區，降低ONLINE資料量。

RESOURCE_SEMAPHORE_QUERY_COMPILE

表示SQL語法過於複雜，或者複雜的SQL查詢太密集，系統Memory Gateway不足以編譯這些SQL。

解決方案：

一、調降SQL語法的複雜度。

二、增加CPU的core數。

三、避免過度使用WITH RECOMPILE。