MSSQL記憶體架構及管理

1.  MSSQL記憶體架構

相比較Oracle，MSSQL的記憶體區域並沒那麼清晰，但和Oracle類似，MSSQL記憶體區域大體也可以分為三個部分：buffer pool，query/workspace memory，其他cache/memory。下面，我們分別對這三個記憶體區域做簡要介紹：

1)  Bufferpool：

Buffer pool 作為MSSQL記憶體主要元件之一，其包括和管理MSSQL的資料緩衝。其中，buffer pool 中的每個buffer為8k大小的記憶體頁（當然，MSSQL 64位系統上也支援large page，這裡不做討論，感興趣的同學可以參考其他文件），與資料或索引頁大小相同，這樣，整個buffer pool就由這種8k大小的buffer組成。可以透過以下SQL語句來查詢各資料庫的buffer pool資訊：

 

select database_id

,case database_id

when 32767 then 'resourcedb'

else db_name(database_id)

end as 'database'

,count(*)*8/1024 as 'cached size (MB)'

from sys.dm_os_buffer_descriptors

group by db_name(database_id),database_id

order by 'cached size (MB)' desc;

 

2)  Query/workspacememory：

類似Oracle的PGA，MSSQL中，query memoy（也稱為workspace memory）用於儲存查詢執行時雜湊和排序期間的臨時結果。雖然大家都知道Oracle的PGA，但MSSQL的query memory也許並沒有太多文件可讀，因此，也並沒有太多人清楚和知道，包括MSSQL的background process，memory components等內部的一些細節和內容，都是這種現狀，這也許和多方面因素相關，這裡不做太多的探討。但是，當你仔細的檢視MSSQL中SQL語句的查詢計劃時，你會清楚的看到hash和sort等操作相關的query memory內容。

Query memory的分配出自buffer pool，因為，MSSQL記憶體管理器非常清楚其記憶體的整個分配和使用情況，這點也許和Oracle的PGA有點不同，因為，Oracle為程式模型，在其buffer pool和PGA之間轉換和分配可能會比MSSQL更加複雜些，這也是MSSQL執行緒模型的特點和優勢，關於這點，大家自己思考吧，這裡不做過多的闡述。

Query memory空間可以佔到buffer pool大小的25%和75%之間，但是，當buffer pool不存在記憶體壓力時，query memory也可以進一步增長。對於所有代價低於3和query memory需求低於5M的小查詢，其query memory可以為之保留整個query memory5%的空間。單個查詢最多能佔到整個query memory的20%以保證其他查詢的正常執行，這點來說，與Oracle的PGA非常類似。可以透過以下SQL語句來獲取查詢語句的query memory及相關授權情況：

select session_id,sql_handle,grant_time,requested_memory_kb

,granted_memory_kb,used_memory_kb

  from sys.dm_exec_query_memory_grants

order bygranted_memory_kb desc;

 

select sum(isnull(requested_memory_kb,0))requested_memory_kb

,sum(isnull(granted_memory_kb,0))granted_memory_kb

       ,sum(isnull(used_memory_kb,0))used_memory_kb

   from sys.dm_exec_query_memory_grants

orderby granted_memory_kb desc;

3)  其他cache/memory：

MSSQL記憶體區域，除了上面講述的buffer pool和query/workspace memory外，剩下的那就是其他cache/memory部分，該部分記憶體用於MSSQL中所有不能放於前述兩個記憶體區域的記憶體元件。這部分雖然不像Oracle中定義的那麼清晰，但基本相當於Oracle中的SGA中除去buffer pool外的其他元件，這其中，主要是shared pool。該部分割槽域，雖然佔據的記憶體不多，但包含眾多的記憶體元件，也很重要。我們可以透過以下SQL語句來查詢其相關資訊：

select [name],[type],pages_kb,entries_count

from sys.dm_os_memory_cache_counters

order by pages_kb desc;

此外，log cache/buffer也是大家比較關心的一個記憶體元件，相較於Oracle來說，MSSQL對這個記憶體區域的持比較保守的態度，不管是官網還是其他資料，對這塊區域都未曾提及。Oracle中，我們既可以很容易的檢視該區域的大小，也可以很容易的對該區域大小進行配置和更改；然而，MSSQL中該區域則是截然相反，我們對該區域既不能檢視，也不能修改，真相估計也只有微軟內部清楚。縱觀所有可以查閱的資料，我們只是知道，MSSQL例項中的每個資料都有一個log cache/buffer，其為一段連續的記憶體區域，其大小並不固定，最大大小不過60k，使用者不能干預該區域的管理和設定，該區域由MSSQL例項自動動態管理，僅此而已，也希望有清楚和知道這部分機制和管理配置方法的同學隨時聯絡和討論，先謝謝了。

2.  動態記憶體管理

MSSQL預設的記憶體管理行為動態記憶體管理，即在不造成系統層面記憶體短缺的前提下，儘可能多的獲取其所需的記憶體，MSSQL透過windows的記憶體通知APIs來實現這點。這點來講，和Oracle11g的AMM非常相似，只不過，MSSQL幾乎一直是這種記憶體管理方式，而Oracle則是在11g之後的版本才實現，這其中涉及程式模型及記憶體共享實現方面的內容和細節，這裡不再深入探討。

當MSSQL啟動時，將會基於各種因素（像：系統上的實體記憶體，伺服器執行緒數，以及各種啟動引數等）計算MSSQL程式地址空間的大小，MSSQL將會保留這個計算出大小的記憶體空間，但它僅僅獲取當前負載需要的實體記憶體空間。

接下來，當更多使用者連線和執行查詢時，為了支撐更多的負載，MSSQL會按需獲取另外的實體記憶體。MSSQL例項會繼續獲取實體記憶體，直到達到它的max server memory目標，或者windows通知不再有額外的自有記憶體可用,此時，如果例項獲取的記憶體大於min server memory且windows通知自由記憶體短缺時，則MSSQL會釋放記憶體。

Min server memory和max server memory配置選項建立了MSSQL例項所用記憶體的上下限。MSSQL並不會立即獲取min server memory確定大小的記憶體，開始僅僅獲取例項初始化所需大小的記憶體，此後，隨著MSSQL例項上負載的增加，其不斷獲取記憶體以支撐這些工作負載,期間，MSSQL例項記憶體達到min server memory前不會釋放任何已獲取的記憶體，一旦達到min server memory值後，MSSQL例項會利用其內部記憶體相關演算法按需獲取和釋放記憶體，和之前不同的是，MSSQL例項不再會釋放記憶體至min server memory確定值之下，也不會獲取記憶體至max server memory確定值之上。

實際上，MSSQL2012之前的版本中，min/max server memory並非指MSSQL例項所佔用和管理的所有記憶體，而僅僅是指MSSQL例項中的buffer pool大小，而MSSQL例項的其他記憶體元件則在此之外，而MSSQL2012之後的版本中，這一切發生了變化，min/max server memory幾乎包括了MSSQL例項所佔用和管理的所有記憶體,MSSQL例項完全根據其上負載按需動態的獲取和釋放記憶體。

當執行MSSQL例項的同一計算機上其他應用啟動時，它們會消耗記憶體且自由實體記憶體降到MSSQL記憶體目標值以下時，MSSQL例項開始調整自己的記憶體消耗。當其他應用停止且更多記憶體變得可用時，MSSQL例項開始增加它的記憶體分配。MSSQL例項每秒鐘能釋放和獲取數MB的記憶體，以便迅速的調整記憶體分配。至於MSSQL例項記憶體使用方面的資訊，可以透過以下SQL來查詢：

select name,value,value_in_use,[description]

  from sys.configurations

 where namelike'%server memory%'

 order by name;

 

select physical_memory_in_use_kb,locked_page_allocations_kb,

       page_fault_count,memory_utilization_percentage,

       available_commit_limit_kb,process_physical_memory_low,

       process_virtual_memory_low

  from sys.dm_os_process_memory;