SQLServer RESOURCE_SEMAPHORE 等待狀態

標籤：

原文: SQLServer RESOURCE_SEMAPHORE 等待狀態

概述：

         當一個SQLServer執行個體運行得很慢的時候，應該做一些檢查，如檢查等待狀態。最好的方法是一開始就建立一個效能基準，以便做效能對比。當發現與效能基準對比後，存在記憶體壓力的話，就要找出是什麼原因導致的。可以檢查事務的等待狀態，其中Resource_semaphore等待可能出現最多。下面是如何去處理這個問題： 

         當檢查事務的所有等待類型後，可能會發現Resource_semaphore這個等待類型出現非常多，這會增加一些頁面的IO等待。因為這些事務沒有足夠的記憶體來處理它們的操作，所以到這了頁面的IO等待。

 

Resource_semaphore等待：

         首先我們來弄清楚一下什麼是Resource_semaphore等待。當SQLServer收到一個使用者請求（或者查詢時）。首先會建立一個編譯後的計劃，然後在這個基礎上建立一個執行計畫。當SQLServer建立一個編譯後的計劃時，它會計算兩個記憶體授與參數，成為：請求記憶體（required memory）和額外記憶體（additional memory）

        請求記憶體是運行排序和hash串連的所需最少記憶體，之所謂成為“請求”，是因為查詢不需要在一開始就申請這部分的記憶體。而額外記憶體是存放臨時資料到記憶體中所需的那部分記憶體。如果沒有足夠的記憶體，查詢所需的資料將會存到硬碟當中。

         首先，伺服器會計算運行特定查詢所需要的記憶體。這部分通常等於請求記憶體和額外記憶體的總和。但當執行個體使用並存執行時，所需的記憶體為（請求記憶體*並行度）加上額外記憶體的總和。伺服器會檢查是否有足夠的記憶體來運行每個查詢，然後會降低額外記憶體的量，知道所有總記憶體需求量剛好達到記憶體的限制量。這部分修改後的記憶體成為需求記憶體（requested memory）。在SQLServer內不能，有一個叫Resource Semaphore的設定，用於授予需求記憶體用於查詢。當查詢沒有得到足夠的記憶體，就會把等待狀態改為：Resource_Semaphore。可以從sysprocesses系統資料表或者sys.dm_exec_request DMV中查詢。

        當Resource_semaphore接受一個新的請求時，首先檢查是否有查詢還在等待中，如果發現有，那麼會把這個新請求放到先進先出的隊列中，Resource Semaphore會嘗試對未等待的查詢授予記憶體，這部分記憶體可能是之前的查詢執行完畢後返回的記憶體。如果發現有足夠的記憶體，那麼就會把記憶體賦予給處於Resource Semaphore等待狀態的查詢，讓其開始運行。如果不夠，那麼會把查詢放入等待隊列並標記為Resource_Semaphore等待。因此，看這個等待狀態可以發現記憶體存在壓力。

識別Resource_Semaphore等待：步驟1：

        執行以下語句，並篩選Resource_Semaphore等待的資料：

SELECT  *FROM    sys.SYSPROCESSESWHERE   lastwaittype = ‘RESOURCE_SEMAPHORE‘ORDER BY lastwaittype

由於這種情況不好類比出來，所以沒有。

步驟2：

        從步驟1中得到的結果,可能會看到很多的事務處於ResourceSemaphore 等待狀態，現在可以運行下面語句來查看已指派到記憶體的查詢的目前狀態，和未被分配記憶體的查詢的數量。這個DMV會返回兩行，一行是resource_semaphore_id為0的大查詢，另外一些是為1的小查詢，這裡的小是記憶體小於5M。在這裡可以獲得總授予記憶體和執行個體上總可用記憶體。可以查看grantee_count和waiter_count，grantee_count是已經分配了記憶體的總查詢數量，而waiter_count是在隊列中等待授予記憶體的總查詢數量：

SELECT  *FROM    sys.dm_exec_query_resource_semaphores

步驟3:

         然後使用DMV：sys.dm_exec_query_memory_grants來獲得在等待隊列中的查詢所需要記憶體的詳細資料。這些查詢的grant_time和granted_memory_kb可能為null。也可以從這個DMV中得到plan_handle和sql_handle：

SELECT  *FROM    sys.dm_exec_query_memory_grants

我們要關注的是下面3列：

步驟4：

       現在將要找到集中消耗記憶體的查詢，可以查看所有等待查詢中的需求記憶體。當看到這部分記憶體太大的時候，然後找到這些查詢的plan_handle，並查看它們的執行計畫：

SELECT TOP 10        *FROM    sys.dm_exec_query_memory_grants

步驟5：       把步驟4中查詢的plan_handle的資料複製，然後執行：

SELECT * FROM sys.dm_exec_sql_text(sql_handle)

注意替換括弧中的sql_handle。然後查看其執行計畫。

 

總結：         通過上面的步驟找到耗費記憶體的查詢後，應該調整語句，使其佔用更少的資源。以便解決記憶體壓力。

SQLServer RESOURCE_SEMAPHORE 等待狀態