理解SQL Server的查詢記憶體授與(譯)

標籤：des   blog   http   io   ar   os   使用   sp   strong   

此文描述查詢記憶體授與(query memory grant)在SQL Server上是如何工作的，適用於SQL 2005 到2008。

        查詢記憶體授與(下文縮寫為QMG)是用於儲存當資料進行排序和串連時的臨時中間資料行。查詢在實際執行前需要先請求保留記憶體，所以會存在一個授予的動作。

這樣的好處是提高查詢的可靠性和避免單個查詢佔用所有的記憶體。

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

    1.產生編譯計劃。它包括各種邏輯指令，如怎麼聯結資料行。

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

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

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

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

 

記憶體使用量者(Memory Consumers)

      一個成功執行的查詢包括三個主要的記憶體使用量者：編譯，緩衝和記憶體授與。

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

               編譯主要使用記憶體和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>標記部分內容，擷取更多記憶體使用量的資訊。

 

並行度依賴(DOP Dependence)

       當SQL Server使用多個CPU時，可以將查詢分解到到多個工作者線程並存執行來提高效能。這些工作者線程相互獨立運行，然後使用並行操作符(Parallelism operator a.k.a Exchange)傳輸資料。

並行模式增加了記憶體的使用，因為並行的每個工作者都 需要一份排序和聯結的資料副本並且並行操作符需要緩衝所有被傳輸的臨時資料。因此DOP N會使用N倍所需的記憶體。

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

 

記憶體授與過程(Memory grant process)

       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調用資源訊號去授予這個被請求的記憶體.

 

資源訊號量(Resource Semaphore)

       資源訊號量負責調整和滿足記憶體授與請求,以保證它們的使用量在伺服器的限制內.

       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. 大型查詢，特別是大型統計查詢，會有很排序和雜湊操作，並且是最消耗資源的操作。理解查詢記憶體授與有助於判斷必要性和最佳化這種查詢。

理解SQL Server的查詢記憶體授與(譯)