此文描述查詢內存授予(query memory grant)在SQL Server上是如何工作的，適用于SQL 2005 到2008。

查詢內存授予(下文縮寫為QMG)是用于存儲當數據進行排序和連接時的臨時中間數據行。查詢在實際執行前需要先請求保留內存，所以會存在一個授予的動作。

這樣的好處是提高查詢的可靠性和避免單個查詢占用所有的內存。

SQL Server在收到查詢時，會執行3個被定義好的步驟來返回用戶所請求的結果集。

1.生成編譯計劃。它包括各種邏輯指令，如怎么聯接數據行。

2.生成執行計劃，它包含將編譯計劃中的各種邏輯引用轉換成實際的對象的指令和查詢執行的跟蹤機制。

3. 從指令樹的頂端開始執行。

生成編譯計劃是件開銷較大的事情，因為它需要在數以百計的編譯計劃中挑出較優的一個。每一個編譯計劃都能被緩存，并被多個執行計劃共享使用。

內存授予預估的有些參數被存儲在編譯計劃當中，并且它本身有一個機制用于計算在實際執行時需要授予給查詢的內存大小。

一個成功執行的查詢包括三個主要的內存使用者：編譯，緩存和內存授予。

編譯：創建和在數以百計的計劃當找到最優的編譯計劃是件很耗內存的事。它的時間通常很短，因為優化器會在找到最優的編譯計劃后便馬上釋放內存。

編譯主要使用內存和CPU資源。缺少可用內存可能會導致編譯延遲和得到非最優的編譯計劃。

緩存： 為了編譯計劃重用，SQL Server會把編譯計劃保存到緩存中。它會長時間占用內存。如果缺少可用內存會導致不必要的生編譯。

內存授予：這部分主要用于存儲當數據進行排序和連接時的臨時中間數據行。缺少可用內存會導致查詢使用硬盤來暫存數據，降低查詢性能。

SQL Server使用內存代理(Memory Broker)來平衡三個使用者對內存的使用?；谑褂谜咝枰膬却婧涂捎梦锢韮却?，如果內存代理預估到內存不足時，便會設定內存使用限制并通知三者削減各自內存使用量。

授予參數(Grant Parameters)

當SQL Server創建編譯計劃時，會計算兩個參數：必須內存(Requeried memory)和額外內存(Additional memory)。

必須內存：執行排序和哈希聯接所需的最少內存。這部分內存是“必須”的，它用來創建處理排序和哈希所需要的內部數據結構。

額外內存：存儲所有臨時數據行所需的內存。它的大小由基數評估(Cardinality estimate,如行數和行大小)決定。“額外”，顧名思義在缺少這部分內存時，將會將臨時數據行存到硬盤上，并不會導致查詢失敗。

一個查詢的額外內存大小如果超過預設的限制，它實際得到的內存量并一定會跟請求量一樣。

例如，對行大小為10byte的100萬行數據進行排序，此查詢的必須內存為為512KB(此值是SQL Server處理一個排序操作創建內部數據結構所需的最小內存量)。為了存儲所有數據行，額外內存可能是10MB。

當編譯計劃中含有多個排序和聯接操作時，額外內存的計算就變得復雜了。因為SQL Server要考慮所有操作符如何高效地使用內存。可以查看ShowPlan xml中的<MemoryFractions>標記部分內容，獲取更多內存使用的信息。

當SQL Server使用多個CPU時，可以將查詢分解到到多個工作者線程并行執行來提高性能。這些工作者線程相互獨立運行，然后使用并行操作符(Parallelism Operator a.k.a Exchange)傳輸數據。

并行模式增加了內存的使用，因為并行的每個工作者都 需要一份排序和聯接的數據副本并且并行操作符需要緩存所有被傳輸的臨時數據。因此DOP N會使用N倍所需的內存。

然而，需要處理的數據行數和存儲它們的內存量并不會隨著DOP的改變而改變。也就是說，額外內存不會因DOP面改變。從SQL 2008開始并行操作符使用的緩存內存也被算成必須內存授予。

SQL Server需要考慮服務器內存量和并發查詢數量來避免最終提交的內存量超過物理內存限制.這分兩步完成,首先它計算需要分配多少內存給某個查詢,然后使用內部因子-資源信號量(Resource Semaphore)去保留實際內存,或者當過多查詢請求內存時,它會調節(Throttle)這些請求.按照下面的步驟來確定內存請求量:

1. SQL Server根據服務器狀態和計劃來確定最大并行度.

2. SQL Server檢查是否需要執行內存授予,如果不需要則馬上執行查詢.例如查詢中沒有group by 或order by,則不需要進行內存授予.

3. 為單個查詢計算內存限制,默認值=0.25(23bit SQL 200為0.2)*0.9*Server內存.這個比值在SQL 2008是可配置的.單個查詢的內存限制,避免了一個查詢占用所用服務器內存.

4. 計算理想情況下必須的查詢內存量=理想內存需求量*DOP+額外內存(+Exchange on SQL 2008).

5. 然后檢查理想內存理是否超過單個查詢內存限制.如果超過,則會減少額外內存量,直到符合單個查詢內存限制.這個修正后的內存稱為被請求的內存(Requested memory),SQL Server調用資源信號去授予這個被請求的內存.

資源信號量負責調整和滿足內存授予請求,以保證它們的使用量在服務器的限制內.

1. 只當有足夠空閑內存時,資源信號才允許查詢保留內存.否則將會被排進一個等待隊列.

2. 當資源信號量收到一個查詢請求時,會先檢查等待隊列中是否有正在等待的請求,如果有,則此請求也會被放進此FIFO的隊列中.

3. 當隊列中無等待的查詢或者之前的查詢返回被保留的內存時，資源信號會嘗試授予內存。

4. 如果找到等待的查詢時，請求的查詢將會被入進隊列中。

5. 如果沒有等待的查詢，它會檢查可用空閑內存。

6. 如果找到足夠的空閑內存，則授予查詢所請求的內存。查詢就可以開始運行了。

7. 如果沒有找到足夠的空閑內存，則當前查詢會被放進等待隊列。

8. 當有足夠的可用空閑內存時，資源信號量會喚醒隊列中等待的查詢。

SQL Server 提供了一些DMV用于分析內存授予相關的問題。

sys.dm_exec_query_resource_semaphores

此視圖返回當前資源信號量的狀態。它返回兩行，一行叫常規資源信號量(max_target_memory_kb列為非NULL)，一行叫小型資源信號量(小于5MB的查詢)。

sys.dm_exec_query_memory_grants

返回已經獲得內存授予和正在等待內存授予的查詢相關信息。正在等待的查詢的grant_time列會是NULL。資源信號量使用內部查詢成本來評估授予內存的先后次序。

is_next_candidate列標識出下一下被喚醒的正在等待的查詢。

sys.dm_os_wait_stats

此DMV返回顯示所有服務器對象的等待統計信息。內存授予的等待類型叫做“RESOURCE_SEMAPHORE”.如果此等待類型過多，則可能是大型查詢引起的。

使用示例

查詢內存隊列中正在等待的查詢:

SELECT * FROM sys.dm_exec_query_memory_grants where grant_time is null

查詢誰使用最多查詢內存授予:

SELECT mg.granted_memory_kb, mg.session_id, t.text, qp.query_plan FROM sys.dm_exec_query_memory_grants AS mg CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp ORDER BY 1 DESC OPTION (MAXDOP 1)

查詢緩存中使用查詢內存授予的查詢:

SELECT t.text, cp.objtype,qp.query_plan FROM sys.dm_exec_cached_plans AS cp JOIN sys.dm_exec_query_stats AS qs ON cp.plan_handle = qs.plan_handle CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t WHERE qp.query_plan.exist('declare namespace n="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; //n:MemoryFractions') = 1

總結：

1. 原文地址：Understanding SQL server memory grant

2. 我是在看過ShanksGao的這篇博文后,找到這個資料腦補的.

3. 大型查詢，特別是大型統計查詢，肯定會有排序和哈希操作，而且是最消耗資源的操作。理解查詢內存授予有助于判斷必要性和優化這種查詢。