淺談SQL Server 對於記憶體的管理[圖文]

理解SQL Server對於記憶體的管理是對於SQL Server問題處理和效能調優的基本，本篇文章講述SQL Server對於記憶體管理的記憶體原理。

二級儲存(secondary storage)

對於電腦來說，儲存體系是分層級的。離CPU越近的地方速度愉快，但容量越小(1所示)。比如：傳統的電腦儲存體繫結構離CPU由近到遠依次是:CPU內的寄存器，一級緩衝，二級緩衝，記憶體，硬碟。但同時離CPU越遠的儲存系統都會比之前的儲存系統大一個數量級。比如硬碟通常要比同時代的記憶體大一個數量級。

圖1.電腦儲存體系

因此對於SQL Server來說，正常的生產系統所配置的記憶體通常不能裝載所有資料，因此會涉及到二級儲存,也就是磁碟。磁碟作為現代電腦系統中最後的機械儲存組件，讀取資料需要移動磁頭（具體關於磁碟的原理，可以看我之前寫的一篇文章）,並且由於資料庫所訪問的資料往往是隨機分布在磁碟的各個位置,因此如果頻繁的讀取磁碟需要頻繁的移動磁頭,這個效能將會十分底下。

由電腦體儲存體繫結構可以知道，電腦對於所有硬碟內資料的操作都需要首先讀取到記憶體，因此利用好記憶體的緩衝區而減少對磁碟IO的訪問將會是提升SQL Server效能的關鍵，這也是本篇文章寫作的出發點之一。

SQL Server引擎，一個自我調整的引擎

由於SQL Server過去一直面向是中小型企業市場的原因，SQL Server儲存引擎被設計成一個不需要太多配置就能使用的產品，從而減少了部署成本，但這也是很多人一直詬病的微軟開放的配置過少。而對於SQL Server如何使用記憶體,幾乎沒有直接可以配置的空間，僅僅開放的配置只有是否使用AWE,以及執行個體佔用的最大或最小記憶體，2所示。

圖2.SQL Server可控控制記憶體的選項

而對於具體的SQL Server如何使用記憶體，例如分配給執行計畫緩衝多少，分配給資料buffer多少，這些都無法通過配置進行調控。這也是很多其它技術的開發人員對於使用微軟技術的開發人員充滿優越感的原因，而在我看來，雖然SQL Server提供可控配置的地方很少，但是很多地方都可以在通曉原理的情況下進行“間接”的配置。這也需要瞭解一些Windows的原理。

SQL Server是如何使用記憶體的

SQL Server儲存引擎本身是一個Windows下的進程，所以SQL Server使用記憶體和其它Windows進程一樣，都需要向Windows申請記憶體。從Windows申請到記憶體之後，SQL Server使用記憶體粗略可以分為兩部分：緩衝池記憶體（資料頁和空閑頁）,非緩衝記憶體(線程，DLL,連結的伺服器等)。而緩衝池記憶體佔據了SQL Server的大部分記憶體使用量。緩衝池所佔記憶體也就是圖2最大最小記憶體所設定的，因此sqlservr.exe所佔的記憶體有可能會大於圖2中所設定的最大記憶體。

還有一點是，SQL Server使用記憶體的特點是：有多少用多少，並且用了以後不釋放(除非收到Windows記憶體壓力的通知)。比如我所在公司的程式開發伺服器，在幾乎沒有負載的時候來看記憶體使用量，3所示。

圖3.SQL Server 進程的記憶體使用量

可以看到CPU在0負載的時候，記憶體卻佔據了13個G。這其實是在之前的使用SQL Server向Windows申請的記憶體一直沒有釋放所致。

具體SQL Server能夠使用多少記憶體是由以下幾個因素決定的：

1.實體記憶體的大小

2.所安裝Windows版本對於記憶體的限制(比如windows server 2008標準版限制最大記憶體只能使用32GB)

3.SQL Server是32位或64位

4.2所示配置SQL Server對於記憶體的使用量

5.SQL Server的版本(比如express版只能用1G記憶體)

SQL Server OS的三層記憶體配置

SQL Server OS對於記憶體的分配分為三個層級,依賴關係4所示。

圖4.SQL Server OS記憶體依賴關係

Memory Node

首先最底層的是Memory Node,Memory Node的作用是使得分配記憶體由Windows移交到SQL Server OS層面執行。每個SQL Server執行個體通常都只擁有一個Memory Node,Memory Node的多寡只取決於NUMA構架的硬體設定。我們通過 DBCC MEMORYSTATUS 可以看到Memory Node的一些資訊,5所示。

圖5.查看Memory Node資訊

我們可以看出 ，按照申請記憶體大小分類，可以分為兩部分

1.申請小於等於8KB為一個單位的記憶體，這些記憶體被用於緩衝。(圖5中的SinglePage Allocator)

2.申請大於8KB為一個單位的記憶體，這些記憶體稱為Multi-Page（或MemToLeave）(圖5中的MultiPage Allocator)

對於為什麼叫MemToLeave,被稱為MemToLeave的原因是由於SQL Server雖然大部分記憶體被用於緩衝區，但還需要一些連續的記憶體用於SQL CLR,linked server,backup buffer等操作，32位SQL Server在啟動執行個體時會保留一部分連續的虛擬位址（VAS）用於進行MultiPage Allocator。具體保留多少可以用如下公式計算：

保留地址=((CPU核心數量-4)+256)*0.5MB+256MB,通常在384MB左右。

Memory Clerk

讓我們再來看Memory Clerk,Memory Clerk用於分配記憶體，用於將Allocate出去的記憶體進行分類，可以簡單的進行如下語句,6所示.

圖6.按照Memory Clerk的類別進行分類

注意：由圖4可以看到，Memory Clerk只是分配記憶體的一部分，另一部分是資料緩衝（Buffer Pool)

Buffer Pool

在開始講述Buffer Pool之前，首先想講一下虛擬記憶體。

在Windows中每個進程都有一個虛擬記憶體(Virtual Address Space VAS)，32位系統是2的32次方，也就是4G，這4G被Windows劃為兩部分，一部分是Windows使用，另一部分才是應用程式使用。虛擬記憶體並不是實際的實體記憶體，而是對於實體記憶體的映射，當實體記憶體不存在虛擬記憶體指向的內容時，產生缺頁中斷，將一部分頁面置換出記憶體，然後將需要的部分從硬碟讀到記憶體，關於這塊，可以讀我之前寫的一篇文章:淺談作業系統對記憶體的管理。

因此Buffer Pool的作用時緩衝資料頁，使得未來讀取資料時減少對磁碟的訪問。

這個Buffer Pool這部分就是圖2中設定最大最小伺服器記憶體所佔用的空間。這個最小值並不意味著SQL Server啟動時就能佔用這麼多記憶體，而是SQL Server Buffer Pool的使用一旦超過這個值，就不會再進行釋放了。

在DBCC MEMORYSTATUS 其中有一部分我們可以看到Buffer Pool的資訊，7所示。

圖7.Buffer Pool的相關資訊

在SQL Server執行個體啟動時，Buffer Pool所保留的VAS地址空間取決於多個因素:包括實際的實體記憶體和SQL Server是32位或是64位（這個限制32位是4G，還要劃一半給Windows和減去MemToLeave空間）,而對於實際上SQL Server所使用的實體記憶體，可以通過如下語句查看，8所示。

圖8.查看Buffer Pool所使用實體記憶體

Buffer Pool會按照需要不斷的提出記憶體申請。Buffer Pool如果需要，Buffer Pool會不斷消耗記憶體，直到Windows通知SQL Server記憶體過低時，Buffer Pool才有可能釋放記憶體，否則Buffer Pool佔據了記憶體不會釋放。

另外值得注意的一點是，Buffer Pool所分配的頁面和SQL Server OS頁面大小是一致的，也就是8192位元組，當SQL Server其它部分需要向”Buffer Pool”借記憶體時,也只能按照8k為單位借，並且這部分記憶體在實體記憶體中是不連續的，這聽上去像是Buffer Pool記憶體管理自成體系，可以這麼理解，因為Buffer Pool 不使用任何SQL Server的page allocator,而直接使用virtual或AWE SQLOS's的介面。

所以SQL Server所佔用的記憶體可以用這個公式粗略估算出來: buffer pool佔用的記憶體+從buffer pool借的頁佔得記憶體+multiPageAllocator分配的非buffer pool記憶體，9所示。

圖9.可以近似的估算出sql server所佔的記憶體

Memory Object

menory object本質上是一個堆,由Page Allocator進行分配，可以通過sys.dm_os_memory_objects這個DMV進行查看，這個DMV可以看到有一列Page_Allocator_Address列，這列就是Memory Clerk的標識，表明這個Memory Object是由哪個Memory Clerk進行分配的。

32位SQL Server的記憶體瓶頸

由文章前面所述的一些基本原理可以看出，由於32位的SQL Server使用的是VAS進行地址分配，因此定址空間被限制在4GB，這4GB還要有一半分給Windows,使得Buffer Pool最多隻能用到2G的記憶體，這使得32位SQL Server即使有多餘的實體記憶體，也無法使用。

解決辦法之一是通過減少Windows預設佔用的2G到1G，使得SQL Server可以使用的記憶體變為3G。這個可以通過在Windows Server 2008中的命令列鍵入 BCDEdit /set設定increaseuserva選項，設定值為3072MB，對於Windows Server 2003來說，需要在boot.ini中加上/3gb啟動參數。

另一種辦法是使用AWE（Address Window Extension）分配記憶體。AWE通過電腦實體位址延伸(Physical Address Extension PAE)，增加4位，使得32位的CPU定址範圍增加到2的36次方，也就是64GB。基本解決了定址範圍不夠的問題。

VirtualAlloc和AllocateUserPhysicalPages

VirtualAlloc和AllocateUserPhysicalPages是SQL Server向Windows申請記憶體所使用的方法。在預設情況下，SQL Server所需要的所有記憶體都會使用VirtualAlloc去Windows申請記憶體，這種申請是作業系統層面的，也就是直接對應的虛擬記憶體。這導致一個問題，所有通過VirtualAlloc分配的記憶體都可以在Windows面臨記憶體壓力時被置換到虛擬記憶體中。這會造成IO佔用問題。

而使用AllocateUserPhysicalPages所申請的記憶體，直接和更底層的頁表(Page Table)進行匹配，因此使用這個方法申請的記憶體不會被置換出記憶體。在32位SQL Server的情況下，通過開啟AWE分配記憶體，buffer pool中的data cache部分將會使用這個函數，而MemToLeave部分和Buffer Pool中的另一部分記憶體（主要是執行計畫緩衝）依然通過VirtualAlloc進行記憶體配置。

因此在開啟通過AWE分配記憶體之前，SQL Server首先需要對應的許可權，否則就會在日誌中報錯，10所示。

圖10.開啟AWE卻沒有開啟對應許可權報錯

我們可以在組策略裡設定啟動SQL Server的賬戶擁有這個許可權，11所示。

圖11.鎖定記憶體頁(Lock Page In Memory)

64位SQL Server的問題

64位Windows基本已經不存在上述的記憶體問題，但是依然要注意，在預設情況下，64位的SQL Server使用的依然是VirtualAlloc進行記憶體配置，這意味著所有分配的記憶體都會在Windows面臨壓力時將頁置換出去，這很可能造成抖動（Buffer Pool Churn）,這種情況也就是SQL Server Buffer Pool中的頁不斷的被交換進硬碟，造成大量的IO佔用(可以通過sys.dm_exec_query_memory_grants這個DMV查看等待記憶體的查詢),因此64位SQL Server將Buffer Pool中的Date Page通過AllocateUserPhysicalPages來進行記憶體配置就能避免這個問題。與32位SQL Server不同的是，64位SQL Server並不需要開啟AWE,只需開啟11所示的“Lock Page In Memory”就行了。

但這又暴漏出了另一個問題，因為SQL Server鎖定了記憶體頁，當Windows記憶體告急時，SQL Server就不能對Windows的記憶體告急做出響應（當然了Buffer Pool中的非data cache和MemToLeave部分依然可以，但往往不夠，因為這部分記憶體相比Data Cache消耗很小），因為SQL Server的特性是記憶體有多少用多少，因此很有可能在無法做出對Windows低記憶體的響應時造成Windows的不穩定甚至崩潰。因此開啟了”Lock Page In Memory”之後，要限制SQL Server Buffer Pool的記憶體使用量，前面圖2中已經說了，這裡就不再細說了。

還有一個問題是當Buffer Pool通過AllocateUserPhysicalPages分配記憶體時，我們在工作管理員中看到的sqlservr.exe佔用的記憶體就僅僅包含Buffer Pool中非Data Cache部分和MemToLeave部分，而不包含Data Cache部分，因此看起來有可能造成sqlservr.exe只佔用了幾百兆記憶體而記憶體的使用是幾十G。這時我們就需要在Perfmon.exe中查看SQL Server:Memory Manager\Total Server Memory計數器去找到SQL Server真實佔用的記憶體。

總結

本文講述了SQL Server對記憶體管理的基本原理和SQL Server對記憶體使用量所分的部分，對於SQL Server效能調優來說，理解記憶體的使用是非常關鍵的一部分，很多IO問題都有可能是記憶體所引起的。

點擊這裡下載本文的PDF版本