簡介
了解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版本