概述： 當一個SQLServer實例運行得很慢的時候，應該做一些檢查，如檢查 等待 狀態 。最好的方法是一開始就建立一個性能基線，以便做性能對比。當發現與性能基線對比后，存在 內存 壓力的話，就要找出是什么原因導致的。可以檢查事務的 等待 狀態 ，其中Resou

概述：

當一個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等待的數據：

[sql] view plaincopyprint?

1. SELECT *
2. FROM sys.SYSPROCESSES
3. WHERE lastwaittype = 'RESOURCE_SEMAPHORE'
4. ORDER BY lastwaittype

由于這種情況不好模擬出來，所以沒有截圖。

步驟2：

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

[sql] view plaincopyprint?

1. SELECT *
2. 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：

[sql] view plaincopyprint?

1. SELECT *
2. FROM sys.dm_exec_query_memory_grants

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

步驟4：

現在將要找到集中消耗內存的查詢，可以查看所有等待查詢中的需求內存。當看到這部分內存太大的時候，然后找到這些查詢的plan_handle，并查看它們的執行計劃：

[sql] view plaincopyprint?

1. SELECT TOP 10
2. *
3. FROM sys.dm_exec_query_memory_grants

步驟5：

把步驟4中查詢的plan_handle的數據復制，然后執行：

[sql] view plaincopyprint?

1. SELECT * FROM sys.dm_exec_sql_text(sql_handle)

注意替換括號中的sql_handle。然后查看其執行計劃。

總結：

通過上面的步驟找到耗費內存的查詢后，應該調整語句，使其占用更少的資源。以便解決內存壓力。

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com