淺談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能夠使用多少內存是由以下幾個因素決定的：
- 物理內存的大小
- 所安裝Windows版本對于內存的限制(比如windows server 2008標準版限制***內存只能使用32GB)
- SQL Server是32位或64位
- 如圖2所示配置SQL Server對于內存的使用量
- SQL Server的版本(比如express版只能用1G內存)
#p#
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進行分配的。
#p#
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問題都有可能是內存所引起的。
原文鏈接：http://www.cnblogs.com/CareySon/archive/2012/08/16/HowSQLServerManageMemory.html
