（譯）記憶體沉思:多個名稱相關的神秘的SQL Server記憶體取用。

標籤：das   被佔用   order by   eof   names   即時   可能性   雜湊   超過   

原文:（譯）記憶體沉思:多個名稱相關的神秘的SQL Server記憶體取用。

 

原文出處：https://blogs.msdn.microsoft.com/sqlmeditation/2013/01/01/memory-meditation-the-mysterious-sql-server-memory-consumer-with-many-names/

 

對於多個不同名稱的記憶體消費者

你曾經是否想知道記憶體授與是什麼（Memory grants ）？
什麼是查詢執行的保留（預定）記憶體（QE Reservations）？
以及查詢執行記憶體（Query Execution Memory）？
工作空間記憶體（Workspace memory），以及記憶體保留（譯者註：很多地方都將Reservations譯作“保留”，個人認為譯作“預定”一次更為容易理解，下文中預定同義與“保留”）

就好比生活中的許多事情一樣，簡而言之，一句話：所以的名字都指向SQL Server中的同一個記憶體消費者，
查詢期間的為了排序或者雜湊操作（包括bulk copy和索引建立等）的記憶體配置
請允許我引申出來更大的話題，在一個查詢執行的過程中，這個查詢可能會從不同的"buckets" 或者 clerks中申請記憶體，（從不同地方申請內的差異）依賴於申請的記憶體用來做什麼。
例如，一個查詢在最初的解析和編譯時間，他需要消費編譯或者是最佳化記憶體。一旦查詢語句編譯完成之後，他所申請的記憶體就會釋放，
同時其對應的查詢執行計畫會被緩衝，為此，這個執行計畫會消費過程緩衝記憶體，並且這塊記憶體一直被佔用直到伺服器重啟或者記憶體發生壓力，此時，查詢準備好執行。
如果查詢語句發生排序或者雜湊操作（串連或者組合），那麼它首選會使用實現預定的記憶體用來儲存結果或者儲存Hash桶（的結果）
這個部分記憶體發生在查詢執行期間，具體會涉及到很多記憶體的名稱。

術語和故障診斷工具

讓我們回顧一下您可能會遇到的關於這個記憶體取用的不同類別。
同樣，所有這些描述了與相同記憶體配置相關的概念:

查詢執行記憶體 (QE Memory):
　　這一項是用來強調在查詢執行過程中sort/hash記憶體使用量這個事實，同時也是查詢執行期間的最大的記憶體消費部分。

查詢記憶體（QE）預定或者記憶體預定：
　　當一個查詢需要sort/hash操作準備記憶體的時候，在執行期間，他將基於包含了sort或者hash運算的原始查詢計劃，執行一個記憶體保留請求（記憶體預定請求），
　　然後查詢開始執行，他請求記憶體，同時sqlserver將會授予這個查詢部分或者是全部的記憶體請求，主要是依賴於伺服器可用記憶體
　　有一個叫做MEMORYCLERK_SQLQERESERVATIONS的記憶體clerk，這個記憶體計數器來保留這部分記憶體的分配的情況。

　　------------------------------譯者注---------------------------------
　　這部分記憶體可以從sys.dm_os_memory_clerks中查詢出來，如下是一個存在負載的伺服器的伺服器上的MEMORYCLERK_SQLQERESERVATIONS分配情況
　　可見這部分記憶體還是不小的，這裡是有超過4GB的MEMORYCLERK_SQLQERESERVATIONS（伺服器記憶體32GB）
　　如果一個伺服器沒有負載的時候，這個記憶體的使用是很低的，甚至可以是0

　　

　　--------------------------------------------------------------------------

記憶體授與（Memory Grants）：
　　當SQL Server給一個請求授予請求記憶體的時候，稱之為記憶體授與發生了，
　　有一個效能計數器保留了已經有多少記憶體授與了請求記憶體：Memory Grants Outstanding
　　另外一個計數器表明有多少查詢已經請求了sort/hash記憶體，但是出於等待狀態，因為查詢執行記憶體已經耗盡：Memory Grants Pending
　　這兩個記憶體計數器顯示了記憶體授與和記憶體授與的不足，也就是說，一個單獨的查詢可能消耗4GB的記憶體去執行一個排序，但這兩種情況都不會被反映出來

　　------------------------------譯者注---------------------------------
　　這部分記憶體可以理解為即時請求記憶體，SQL Server即時請求越多，這部分記憶體就越大，
　　比如一個Session需要20MB的運行記憶體，同時運行10個Session就需要200MB，同時運行100個Session就需要2000MB，當然每個Session需要的運行記憶體都不一樣（memory grant），這裡僅僅是舉例
　　可以從sys.dm_os_memory_clerks中查詢出來，如下是一個存在負載的伺服器的伺服器上的MEMORYCLERK_SQLQERESERVATIONS分配情況
　　可見這部分記憶體還是不小的，這裡是有超過4GB的MEMORYCLERK_SQLQERESERVATIONS（伺服器記憶體32GB）
　　如果一個伺服器沒有負載的時候，這個記憶體的使用是很低的，甚至可以是0
　　Memory Grants Outstanding某一個Session無法得到其申請的記憶體，處於等待記憶體狀態，
　　SQL Server是一個自我調節（self tuning）引擎，這部分記憶體一般會預留的足夠，正常情況下不會發生因為sql的執行缺少記憶體而等待的情況
　　凡事得分清楚輕重緩急，記憶體也一樣分為急需的和非急需的，不能那邊為了快取資料而佔據記憶體，而這邊連即時的請求都無法響應吧。
　　舉個不恰當的例子，再窮，可以不買房不買車，總的留夠吃飯的錢吧。
　　其他地方的記憶體可以存在壓力（比如資料緩衝可能快速地被置換出記憶體），但是面對相應即時請求的記憶體，SQL Server留的還是比較充足的，個人很少見到因為Session等待記憶體而無法執行的情況。
　　但是話不能反過來說，不能說沒有出現Memory Grants Pending，就說明記憶體沒有壓力。

　　

　　----------------------------------------------------------------------------

為了觀察單獨的請求和他們已經申請的記憶體，你可以查詢sys.dm_exec_query_memory_grants這個DMV
這個DMV顯示的是當前啟動並執行查詢的記憶體授與情況，而非曆史情況
另外，你可以捕獲實際查詢執行計畫，查詢其一個叫做Query plan的XML節點，他包含了記憶體授與的大小，
如下樣本：
<QueryPlan DegreeOfParallelism="8" MemoryGrant="2009216"
另外一個DMV是 sys.dm_exec_requests，包含了一個叫做granted_query_memory的8kb為單位的列，
比如一個1000的值，意味著1000*8kb，或者說是8000kb的記憶體授與

工作空間記憶體（Workspace Memory）：

　　這仍舊是另外一個描述同樣記憶體的項目，
　　你經常會看到這個效能技術區授予Workspace Memory (KB)，反映的是當前所有用來sort/hash操作的記憶體使用量
　　最大工作空間記憶體（Maximum Workspace Memory (KB)）數量是自SQL Server啟動以來的最大的工作空間記憶體。
　　在我看來，工作空間記憶體項目是一個用來描述sqlserver7.0個sqlserver2000的記憶體配置的一個遺留問題，在SQLServer2005以後已經作廢

資源訊號量（Resource Semaphore）：
　　為了給這個概念增加更多的複雜性，SQL Server使用一個稱作訊號量（semaphore）的線程同步對象來跟蹤有多少已經被授予了的記憶體。
　　想法是這樣的：如果sqlserver用光了workspace memory/QE memory, 那麼使用out-of-memory錯誤來替代查詢執行失敗（不是直接反饋查詢失敗，而是等待記憶體）
　　它會促使查詢等待可用記憶體，然後（得到可用記憶體之後）可以重新執行

　　在這個過程中，Memory Grants Pending 效能計數器開始變得有意義。
　　因此會在sys.dm_exec_query_memory_grants中產生wait_time_ms，granted_memory_kb = NULL, timeout_sec
　　順便說一下，這個和編譯記憶體是在SQL Server中僅有的在記憶體不足的情況下，會執行等待記憶體的兩種記憶體。
　　在其他情況中，查詢將會直接失敗並報出701錯誤--記憶體不足。

在SQL Server中另外一種等待類型也昭示著一個查詢是在等待記憶體授與--RESOURCE_SEMAPHORE
正如文檔中所說的，這種情況發生，一個查詢記憶體無法被立即授予歸結於其他並發執行的查詢。
頻繁的等待和等待時間可能預示這個過多的並發查詢，或者是過多的記憶體授與數量。
你可以在sys.dm_exec_requests這個DMW中觀察Session層級的等待

 

為什麼需要關注Memory Grants 或者 Workspace Memory 或者 Query Execution Memory或者不管你怎麼稱呼它。

過去幾年對於效能問題的診斷過程中，我發現這是最常見的記憶體相關問題之一，應用程式經常執行一些看起來相當簡單的查詢，
卻因為上執行大量的Sort或者hash操作而遭受到大量的效能破壞
那些查詢不僅僅是在執行過程中消耗大量的記憶體，並且也會導致其他查詢等待記憶體，這就是效能瓶頸。

使用上述我提供的工具（DWVs，效能計數器和實際執行計畫），你可以查出來哪個查詢是耗費了大量的記憶體，然後再考慮最佳化/重寫的可能性

Sort/Hash操作產生的情景

提及查詢重寫，這裡有一些可能會導致大量記憶體授與的情況

Sort操作產生的原因（包括但不限於以下幾種情況）
　　ORDER BY (T-SQL)
　　GROUP BY (T-SQL)
　　DISTINCT (T-SQL)
　　Merge join操作是最佳化器選擇的，其中合并聯結的一個輸入必須被排序

Hash Match操作產生的原因（包括但不限於以下幾種情況）
　　JOIN (T-SQL) – 如果SQL選擇執行一個Hash操作，典型的就是缺少合理的索引導致的一個昂貴的join操作--hash join，觀察執行計畫
　　DISTINCT (T-SQL) –Hash彙總可能會用來執行distinct操作，觀察執行計畫
　　SUM/AVG/MAX/MIN (T-SQL)– 任何彙總操作可能會導致Hash 彙總，觀察執行計畫
　　UNION – Hash彙總可能會用來做移除重複項

瞭解這些常見的原因可以輔助應用程式開發人員儘可能地消除對SQL Server的大量記憶體授與請求。
一如既往，基本的查詢調優開始於檢查查詢是否有適當的索引，以協助它們減少讀取、儘可能減少或消除大型排序。

這裡有個記憶體授與的博文供參考 http://blogs.msdn.com/b/sqlqueryprocessing/archive/2010/02/16/understanding-sql-server-memory-grant.aspx

 

（譯）記憶體沉思:多個名稱相關的神秘的SQL Server記憶體取用。