前言： 本文是對博客https://www.mssqltips.com/sqlservertip/2827/troubleshooting-sql-server-resourcesemaphore-waittype-memory-issues/的翻譯,本文基本直譯，部分地方讀起來有點不自然。 如有翻... ...
前言： 本文是對博客https://www.mssqltips.com/sqlservertip/2827/troubleshooting-sql-server-resourcesemaphore-waittype-memory-issues/的翻譯,本文基本直譯，部分地方讀起來有點不自然。 如有翻譯不對或不好的地方，敬請指出，大家一起學習進步。尊重原創和翻譯勞動成果，轉載時請註明出處http://www.cnblogs.com/kerrycode/p/7068246.html。謝謝！
問題描述
今天，我們的一個SQL Server實例性能變得非常慢。當我登錄到資料庫伺服器進行一些初始檢查時，我最初始觀察、註意到的是它的記憶體壓力（memory pressure）。接下來，我們必須找出是什麼導致我們的實例出現記憶體緊張。當我檢查事務的等待類型時，RESOURCE_SEMAPHORE等待是大多數事務的問題。在這篇文章中，我將描述這個問題，以及如何查找哪個查詢語句或事務導致了記憶體壓力
解決方案
當我檢查所有事務的等待類型時，大多數事務的等待類型為RESOURCE_SEMAPHORE 等待以及某些頁面IO等待。頁面IO等待也是由於記憶體壓力導致，因為這些事務無法獲得足夠的記憶體來執行這個操作。
資源信號量等待
在繼續之前，我想對資源信號量（Resource_semaphore）等待進行一些說明，以便您可以更好地瞭解SQL Server是如何將記憶體授予SQL Server查詢語句的。
當SQL Server收到用戶發送的查詢請求（查詢語句）時，它首先創建一個編譯過的計劃，然後在這個基礎上創建一個執行步驟（個人覺得執行步驟比執行計劃要合適）。當SQL Server創建一個編譯過的計劃時，它會計算兩個記憶體授予參數，稱為“請求記憶體”（required memory）和“附加記憶體”(additional memory)。請求記憶體是運行排序和HASH連接所需的最小記憶體。它之所以是 "必需"的, 是因為如果沒有可用的“請求記憶體”, 查詢將無法啟動。附加記憶體（additional memory）是在記憶體中存儲臨時行（個人覺得翻譯為中間結果可能更合理）所需的記憶體量。這被稱為額外（附加）的，因為如果沒有足夠可用的“附加記憶體”可以將查詢的中間結果存儲在磁碟上。
首先，伺服器計算任何給定的查詢執行需要多少記憶體。這通常是“請求記憶體”（required memory）和“附加記憶體”(additional memory)的總和，但如果您的實例正在並行處理，那麼所需的記憶體將是（所需的記憶體* DOP）+附加記憶體。伺服器檢查所需的記憶體是否超過每個查詢限制，然後伺服器減少“附加記憶體”，直到總數達到限制。這個修改後的大小稱為請求記憶體。SQL Server有一個內部工具稱為資源信號量（RESOURCE SEMAPHORE)，用於將此請求的記憶體授予查詢。如果無法通過資源信號量向該請求的記憶體授予查詢，那麼如果查詢sys.sysprocesses系統表或sys.dm_exec_request DMV，則該查詢將處於等待狀態，並出現RESOURCE_SEMAPHORE等待類型。
當資源信號量（Resource_semaphore）接收到新的請求時，它首先檢查是否有任何查詢正在等待中。只要發現存在即是一個等待查詢，那麼會將新查詢（新請求）放入隊列中，因為等待隊列是以先到先得的方式設計的，並且有小權重以支持於小型查詢。當沒有等待查詢或查詢返回保留的記憶體時。資源信號量嘗試授予記憶體。如果找到足夠的記憶體，那麼請求記憶體被授予並且查詢可以開始運行，並且如果沒有找到足夠的可用記憶體來授予所請求的記憶體，那麼它將當前查詢放入等待隊列中，並且給當前會話RESOURCE_SEMAPHORE等待類型， 此時伺服器開始面臨記憶體壓力。
識別RESOURCE_SEMAPHORE等待
步驟1
首先，我們需要研究我們的實例，弄清楚為什麼在SQL Server中出現記憶體壓力。要查看所有事務的大概信息，我們可以查詢sys.sysprocesses，或者我們可以使用sys.dm_exec_requests DMV。
SELECT * FROM sys.sysprocesses
ORDER BY lastwaittype
這裡我們可以看到所有產生RESOURCE_SEMAPHORE等待類型的進程。
步驟2
從上面的SQL查詢，我們可以看到大量的事務正處於Resource Semaphore（資源信號量）等待狀態。現在我們可以運行下麵的SQL語句來查看已分配到記憶體的查詢的目前狀態，和未被分配記憶體的查詢的數量。
該DMV的輸出返回兩行，一個表示大型查詢（resource_semaphore_id為0），另一個表示小型查詢（resource_semaphore_id為1），小於5 MB。在這裡，您可以獲得實例的總授予記憶體和總可用記憶體。請參閱grantee_count和waiter_count上的數字，grantee_count是已經分配了記憶體的總查詢數，waiter_count是隊列中等待獲取記憶體的總查詢數量。所以在這裡我們可以看到大約100個查詢正在等待獲得他們要求的記憶體。
SELECT * FROM sys.dm_exec_query_resource_semaphores
步驟3
現在我們將獲取所有正在等待隊列中獲取所請求的記憶體的所有查詢的詳細信息，我們將使用DMV sys.dm_exec_query_memory_grants來獲取隊列中等待分配記憶體的查詢總數。對於等待獲取其請求的記憶體的查詢，grant_time和granted_memory_kb列將為NULL。您可以在下麵的截圖中查看所請求的記憶體量及其等待狀態，因為它們的grant_time和granted_memory_kb值為NULL。我們還可以使用該DMV獲取所有查詢的plan_handle和sql_handle。稍後我們將使用這些值來獲取確切的查詢。
註意：有太多列要顯示，可以只顯示部分所需的列。
SELECT * FROM sys.dm_exec_query_memory_grants
步驟4
現在，我們將找到記憶體密集型查詢。我們可以看到所有等待查詢的請求記憶體。在這裡我們可以看到所請求的記憶體對於大多數事務來說太大了。我們將獲得所有這些查詢的plan_handle，以獲得確切的SQL文本來查看查詢計劃。
從sys.dm_exec_query_memory_grants中選擇前10個*
select top 10 * from sys.dm_exec_query_memory_grants
步驟5
現在我們將使用上面查詢所獲得的plan_handle和sql句柄來獲取SQL代碼。
運行以下語句從上述查詢中獲取SQL代碼，使用sql_handle。
SELECT * FR
