簡介

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

二級存儲(secondary storage)

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

圖1.計算機存儲體系

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

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

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多少，這些都無法通過配置進行調(diào)控。這也是很多其它技術(shù)的開發(fā)人員對于使用微軟技術(shù)的開發(fā)人員充滿優(yōu)越感的原因，而在我看來，雖然SQL Server提供可控配置的地方很少，但是很多地方都可以在通曉原理的情況下進行“間接”的配置。這也需要了解一些Windows的原理。

SQL Server是如何使用內(nèi)存的

SQL Server存儲引擎本身是一個Windows下的進程，所以SQL Server使用內(nèi)存和其它Windows進程一樣，都需要向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)存。

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

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

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

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

1. 物理內(nèi)存的大小
2. 所安裝Windows版本對于內(nèi)存的限制(比如windows server 2008標準版限制***內(nèi)存只能使用32GB)
3. SQL Server是32位或64位
4. 如圖2所示配置SQL Server對于內(nèi)存的使用量
5. SQL Server的版本(比如express版只能用1G內(nèi)存)

#p#

SQL Server OS的三層內(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）用于進行MultiPage Allocator。具體保留多少可以用如下公式計算：

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

Memory Clerk

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

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

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

Buffer Pool

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

在Windows中每個進程都有一個虛擬內(nèi)存(Virtual Address Space  VAS)，32位系統(tǒng)是2的32次方，也就是4G，這4G被Windows劃為兩部分，一部分是Windows使用，另一部分才是應(yīng)用程序使用。虛擬內(nèi)存并不是實際的物理內(nèi)存，而是對于物理內(nèi)存的映射，當物理內(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的使用一旦超過這個值，就不會再進行釋放了。

在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)存不會釋放。

另外值得注意的一點是，Buffer Pool所分配的頁面和SQL Server OS頁面大小是一致的，也就是8192字節(jié)，當SQL Server其它部分需要向”Buffer Pool”借內(nèi)存時,也只能按照8k為單位借，并且這部分內(nèi)存在物理內(nèi)存中是不連續(xù)的，這聽上去像是Buffer Pool內(nèi)存管理自成體系[[91120]]，可以這么理解，因為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進行分配，可以通過sys.dm_os_memory_objects這個DMV進行查看，這個DMV可以看到有一列 Page_Allocator_Address列，這列就是Memory Clerk的標識，表明這個Memory Object是由哪個Memory Clerk進行分配的。

#p#

32位SQL Server的內(nèi)存瓶頸

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

解決辦法之一是通過減少Windows默認占用的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通過計算機物理地址擴展(Physical Address Extension PAE)，增加4位，使得32位的CPU尋址范圍增加到2的36次方，也就是64GB?；窘鉀Q了尋址范圍不夠的問題。

VirtualAlloc和AllocateUserPhysicalPages

VirtualAlloc 和AllocateUserPhysicalPages是SQL Server向Windows申請內(nèi)存所使用的方法。在默認情況下，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)進行匹配，因此使用這個方法申請的內(nèi)存不會被置換出內(nèi)存。在32位SQL Server的情況下，通過開啟AWE分配內(nèi)存，buffer pool中的data cache部分將會使用這個函數(shù)，而MemToLeave部分和Buffer Pool中的另一部分內(nèi)存（主要是執(zhí)行計劃緩存）依然通過VirtualAlloc進行內(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位SQL Server的問題

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

但這又暴漏出了另一個問題，因為SQL Server鎖定了內(nèi)存頁，當Windows內(nèi)存告急時，SQL Server就不能對Windows的內(nèi)存告急做出響應(yī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)說了，這里就不再細說了。

還有一個問題是當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)存。

總結(jié)

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

原文鏈接：http://www.cnblogs.com/CareySon/archive/2012/08/16/HowSQLServerManageMemory.html