簡(jiǎn)介

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

二級(jí)存儲(chǔ)(secondary storage)

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

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

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

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

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

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

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

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

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

SQL Server存儲(chǔ)引擎本身是一個(gè)Windows下的進(jìn)程，所以SQL Server使用內(nèi)存和其它Windows進(jìn)程一樣，都需要向Windows申請(qǐng)內(nèi)存。從Windows申請(qǐng)到內(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)存有可能會(huì)大于圖2中所設(shè)置的***內(nèi)存。

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

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

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

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

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

#p#

SQL Server OS的三層內(nèi)存分配

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

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

Memory Node

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

   
圖5.查看Memory Node信息

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

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

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

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

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

Memory Clerk

讓我們?cè)賮砜碝emory Clerk,Memory Clerk用于分配內(nèi)存，用于將Allocate出去的內(nèi)存進(jìn)行分類，可以簡(jiǎn)單的進(jìn)行如下語句,如圖6所示.

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

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

Buffer Pool

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

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

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

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

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

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

在SQL Server實(shí)例啟動(dòng)時(shí)，Buffer Pool所保留的VAS地址空間取決于多個(gè)因素:包括實(shí)際的物理內(nèi)存和SQL Server是32位或是64位（這個(gè)限制32位是4G，還要?jiǎng)澮话虢oWindows和減去MemToLeave空間）,而對(duì)于實(shí)際上SQL Server所使用的物理內(nèi)存，可以通過如下語句查看，如圖8所示。

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

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

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

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

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

Memory Object

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

#p#

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

由文章前面所述的一些基本原理可以看出，由于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。這個(gè)可以通過在Windows Server 2008中的命令行鍵入 BCDEdit /set設(shè)置increaseuserva選項(xiàng)，設(shè)置值為3072MB，對(duì)于Windows Server 2003來說，需要在boot.ini中加上/3gb啟動(dòng)參數(shù)。

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

VirtualAlloc和AllocateUserPhysicalPages

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

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

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

圖10.開啟AWE卻沒有開啟對(duì)應(yīng)權(quán)限報(bào)錯(cuò)

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

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

64位SQL Server的問題

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

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

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

總結(jié)

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

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