理解SQL Server對于內(nèi)存的管理是對于SQL Server問題處理和性能調(diào)優(yōu)的基本，本篇文章講述SQL Server對于內(nèi)存管理的內(nèi)存原理。

    對于計算機(jī)來說，存儲體系是分層級的。離CPU越近的地方速度愉快，但容量越小(如圖1所示)。比如：傳統(tǒng)的計算機(jī)存儲體系結(jié)構(gòu)離CPU由近到遠(yuǎn)依次是:CPU內(nèi)的寄存器，一級緩存，二級緩存，內(nèi)存，硬盤。但同時離CPU越遠(yuǎn)的存儲系統(tǒng)都會比之前的存儲系統(tǒng)大一個數(shù)量級。比如硬盤通常要比同時代的內(nèi)存大一個數(shù)量級。

    圖1.計算機(jī)存儲體系

    因此對于SQL Server來說，正常的生產(chǎn)系統(tǒng)所配置的內(nèi)存通常不能裝載所有數(shù)據(jù)，因此會涉及到二級存儲,也就是磁盤。磁盤作為現(xiàn)代計算機(jī)系統(tǒng)中最后的機(jī)械存儲部件，讀取數(shù)據(jù)需要移動磁頭（具體關(guān)于磁盤的原理，可以看我之前寫的一篇文章）,并且由于數(shù)據(jù)庫所訪問的數(shù)據(jù)往往是隨機(jī)分布在磁盤的各個位置,因此如果頻繁的讀取磁盤需要頻繁的移動磁頭,這個性能將會十分底下。

    由計算機(jī)體存儲體系結(jié)構(gòu)可以知道，計算機(jī)對于所有硬盤內(nèi)數(shù)據(jù)的操作都需要首先讀取到內(nèi)存，因此利用好內(nèi)存的緩沖區(qū)而減少對磁盤IO的訪問將會是提升SQL Server性能的關(guān)鍵，這也是本篇文章寫作的出發(fā)點(diǎn)之一。

SQL Server引擎，一個自我調(diào)整的引擎

    由于SQL Server過去一直面向是中小型企業(yè)市場的原因，SQL Server存儲引擎被設(shè)計成一個不需要太多配置就能使用的產(chǎn)品，從而減少了部署成本，但這也是很多人一直詬病的微軟開放的配置過少。而對于SQL Server如何使用內(nèi)存,幾乎沒有直接可以配置的空間，僅僅開放的配置只有是否使用AWE,以及實例占用的最大或最小內(nèi)存，如圖2所示。

    圖2.SQL Server可控控制內(nèi)存的選項

    而對于具體的SQL Server如何使用內(nèi)存，例如分配給執(zhí)行計劃緩存多少，分配給數(shù)據(jù)buffer多少，這些都無法通過配置進(jìn)行調(diào)控。這也是很多其它技術(shù)的開發(fā)人員對于使用微軟技術(shù)的開發(fā)人員充滿優(yōu)越感的原因，而在我看來，雖然SQL Server提供可控配置的地方很少，但是很多地方都可以在通曉原理的情況下進(jìn)行“間接”的配置。這也需要了解一些Windows的原理。

    SQL Server存儲引擎本身是一個Windows下的進(jìn)程，所以SQL Server使用內(nèi)存和其它Windows進(jìn)程一樣，都需要向Windows申請內(nèi)存。從Windows申請到內(nèi)存之后，SQL Server使用內(nèi)存粗略可以分為兩部分：緩沖池內(nèi)存（數(shù)據(jù)頁和空閑頁）,非緩沖內(nèi)存(線程，DLL,鏈接服務(wù)器等)。而緩沖池內(nèi)存占據(jù)了SQL Server的大部分內(nèi)存使用。緩沖池所占內(nèi)存也就是圖2最大最小內(nèi)存所設(shè)置的，因此sqlservr.exe所占的內(nèi)存有可能會大于圖2中所設(shè)置的最大內(nèi)存。

    還有一點(diǎn)是，SQL Server使用內(nèi)存的特點(diǎn)是：有多少用多少，并且用了以后不釋放(除非收到Windows內(nèi)存壓力的通知)。比如我所在公司的開發(fā)服務(wù)器，在幾乎沒有負(fù)載的時候來看內(nèi)存使用，如圖3所示。

    圖3.SQL Server 進(jìn)程的內(nèi)存使用

    可以看到CPU在0負(fù)載的時候，內(nèi)存卻占據(jù)了13個G。這其實是在之前的使用SQL Server向Windows申請的內(nèi)存一直沒有釋放所致。

    具體SQL Server能夠使用多少內(nèi)存是由以下幾個因素決定的：

    1.物理內(nèi)存的大小

    2.所安裝Windows版本對于內(nèi)存的限制(比如windows server 2008標(biāo)準(zhǔn)版限制最大內(nèi)存只能使用32GB)

    3.SQL Server是32位或64位

    4.如圖2所示配置SQL Server對于內(nèi)存的使用量

    5.SQL Server的版本(比如express版只能用1G內(nèi)存)

    SQL Server OS對于內(nèi)存的分配分為三個層級,依賴關(guān)系如圖4所示。

    圖4.SQL Server OS內(nèi)存依賴關(guān)系

Memory Node

    首先最底層的是Memory Node,Memory Node的作用是使得分配內(nèi)存由Windows移交到SQL Server OS層面執(zhí)行。每個SQL Server實例通常都只擁有一個Memory Node,Memory Node的多寡只取決于NUMA構(gòu)架的硬件配置。我們通過 DBCC MEMORYSTATUS  可以看到Memory Node的一些信息,如圖5所示。

    圖5.查看Memory Node信息

    我們可以看出 ，按照申請內(nèi)存大小分類，可以分為兩部分

    1.申請小于等于8KB為一個單位的內(nèi)存，這些內(nèi)存被用于緩存。(圖5中的SinglePage Allocator)

    2.申請大于8KB為一個單位的內(nèi)存，這些內(nèi)存稱為Multi-Page（或MemToLeave）(圖5中的MultiPage Allocator)

    對于為什么叫MemToLeave,被稱為MemToLeave的原因是由于SQL Server雖然大部分內(nèi)存被用于緩沖區(qū)，但還需要一些連續(xù)的內(nèi)存用于SQL CLR,linked server,backup buffer等操作，32位SQL Server在啟動實例時會保留一部分連續(xù)的虛擬地址（VAS）用于進(jìn)行MultiPage Allocator。具體保留多少可以用如下公式計算：

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

Memory Clerk

    讓我們再來看Memory Clerk,Memory Clerk用于分配內(nèi)存，用于將Allocate出去的內(nèi)存進(jìn)行分類，可以簡單的進(jìn)行如下語句,如圖6所示.

    圖6.按照Memory Clerk的類別進(jìn)行分類

    注意：由圖4可以看到，Memory Clerk只是分配內(nèi)存的一部分，另一部分是數(shù)據(jù)緩存（Buffer Pool)

    在開始講述Buffer Pool之前，首先想講一下虛擬內(nèi)存。

    在Windows中每個進(jìn)程都有一個虛擬內(nèi)存(Virtual Address Space  VAS)，32位系統(tǒng)是2的32次方，也就是4G，這4G被Windows劃為兩部分，一部分是Windows使用，另一部分才是應(yīng)用程序使用。虛擬內(nèi)存并不是實際的物理內(nèi)存，而是對于物理內(nèi)存的映射，當(dāng)物理內(nèi)存不存在虛擬內(nèi)存指向的內(nèi)容時，產(chǎn)生缺頁中斷，將一部分頁面置換出內(nèi)存，然后將需要的部分從硬盤讀到內(nèi)存，關(guān)于這塊，可以讀我之前寫的一篇文章:淺談操作系統(tǒng)對內(nèi)存的管理。

    因此Buffer Pool的作用時緩沖數(shù)據(jù)頁，使得未來讀取數(shù)據(jù)時減少對磁盤的訪問。

    這個Buffer Pool這部分就是圖2中設(shè)置最大最小服務(wù)器內(nèi)存所占用的空間。這個最小值并不意味著SQL Server啟動時就能占用這么多內(nèi)存，而是SQL Server Buffer Pool的使用一旦超過這個值，就不會再進(jìn)行釋放了。

    在DBCC MEMORYSTATUS 其中有一部分我們可以看到Buffer Pool的信息，如圖7所示。

    圖7.Buffer Pool的相關(guān)信息

    在SQL Server實例啟動時，Buffer Pool所保留的VAS地址空間取決于多個因素:包括實際的物理內(nèi)存和SQL Server是32位或是64位（這個限制32位是4G，還要劃一半給Windows和減去MemToLeave空間）,而對于實際上SQL Server所使用的物理內(nèi)存，可以通過如下語句查看，如圖8所示。

    圖8.查看Buffer Pool所使用物理內(nèi)存

    Buffer Pool會按照需要不斷的提出內(nèi)存申請。Buffer Pool如果需要，Buffer Pool會不斷消耗內(nèi)存，直到Windows通知SQL Server內(nèi)存過低時，Buffer Pool才有可能釋放內(nèi)存，否則Buffer Pool占據(jù)了內(nèi)存不會釋放。

    另外值得注意的一點(diǎn)是，Buffer Pool所分配的頁面和SQL Server OS頁面大小是一致的，也就是8192字節(jié)，當(dāng)SQL Server其它部分需要向”Buffer Pool”借內(nèi)存時,也只能按照8k為單位借，并且這部分內(nèi)存在物理內(nèi)存中是不連續(xù)的，這聽上去像是Buffer Pool內(nèi)存管理自成體系，可以這么理解，因為Buffer Pool 不使用任何SQL Server的page allocator,而直接使用virtual或AWE SQLOS's的接口。

    所以SQL Server所占用的內(nèi)存可以用這個公式粗略估算出來: buffer pool占用的內(nèi)存+從buffer pool借的頁占得內(nèi)存+multiPageAllocator分配的非buffer pool內(nèi)存，如圖9所示。

    圖9.可以近似的估算出sql server所占的內(nèi)存

Memory Object

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

    由文章前面所述的一些基本原理可以看出，由于32位的SQL Server使用的是VAS進(jìn)行地址分配，因此尋址空間被限制在4GB，這4GB還要有一半分給Windows,使得Buffer Pool最多只能用到2G的內(nèi)存，這使得32位SQL Server即使有多余的物理內(nèi)存，也無法使用。

    解決辦法之一是通過減少Windows默認(rèn)占用的2G到1G，使得SQL Server可以使用的內(nèi)存變?yōu)?G。這個可以通過在Windows Server 2008中的命令行鍵入 BCDEdit /set設(shè)置increaseuserva選項，設(shè)置值為3072MB，對于Windows Server 2003來說，需要在boot.ini中加上/3gb啟動參數(shù)。

   另一種辦法是使用AWE（Address Window Extension）分配內(nèi)存。AWE通過計算機(jī)物理地址擴(kuò)展(Physical Address Extension PAE)，增加4位，使得32位的CPU尋址范圍增加到2的36次方，也就是64GB?；窘鉀Q了尋址范圍不夠的問題。

VirtualAlloc和AllocateUserPhysicalPages

    VirtualAlloc和AllocateUserPhysicalPages是SQL Server向Windows申請內(nèi)存所使用的方法。在默認(rèn)情況下，SQL Server所需要的所有內(nèi)存都會使用VirtualAlloc去Windows申請內(nèi)存，這種申請是操作系統(tǒng)層面的，也就是直接對應(yīng)的虛擬內(nèi)存。這導(dǎo)致一個問題，所有通過VirtualAlloc分配的內(nèi)存都可以在Windows面臨內(nèi)存壓力時被置換到虛擬內(nèi)存中。這會造成IO占用問題。

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

    因此在開啟通過AWE分配內(nèi)存之前，SQL Server首先需要對應(yīng)的權(quán)限，否則就會在日志中報錯，如圖10所示。

    圖10.開啟AWE卻沒有開啟對應(yīng)權(quán)限報錯

    我們可以在組策略里設(shè)置啟動SQL Server的賬戶擁有這個權(quán)限，如圖11所示。

    圖11.鎖定內(nèi)存頁(Lock Page In Memory)

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

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

    還有一個問題是當(dāng)Buffer Pool通過AllocateUserPhysicalPages分配內(nèi)存時，我們在任務(wù)管理器中看到的sqlservr.exe占用的內(nèi)存就僅僅包含Buffer Pool中非Data Cache部分和MemToLeave部分，而不包含Data Cache部分，因此看起來有可能造成sqlservr.exe只占用了幾百兆內(nèi)存而內(nèi)存的使用是幾十G。這時我們就需要在Perfmon.exe中查看SQL Server:Memory Manager\Total Server Memory計數(shù)器去找到SQL Server真實占用的內(nèi)存。

    本文講述了SQL Server對內(nèi)存管理的基本原理和SQL Server對內(nèi)存使用所分的部分，對于SQL Server性能調(diào)優(yōu)來說，理解內(nèi)存的使用是非常關(guān)鍵的一部分，很多IO問題都有可能是內(nèi)存所引起的。

點(diǎn)擊這里下載本文的PDF版本