淺談SQL Server 對(duì)于內(nèi)存的管理[圖文]

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

    對(duì)于計(jì)算機(jī)來(lái)說(shuō)，存儲(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來(lái)說(shuō)，正常的生產(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ù)庫(kù)所訪問的數(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多少，這些都無(wú)法通過配置進(jìn)行調(diào)控。這也是很多其它技術(shù)的開發(fā)人員對(duì)于使用微軟技術(shù)的開發(fā)人員充滿優(yōu)越感的原因，而在我看來(lái)，雖然SQL Server提供可控配置的地方很少，但是很多地方都可以在通曉原理的情況下進(jìn)行“間接”的配置。這也需要了解一些Windows的原理。

    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ù)頁(yè)和空閑頁(yè)）,非緩沖內(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í)候來(lái)看內(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)存)

    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è)賮?lái)看Memory Clerk,Memory Clerk用于分配內(nèi)存，用于將Allocate出去的內(nèi)存進(jìn)行分類，可以簡(jiǎn)單的進(jìn)行如下語(yǔ)句,如圖6所示.

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

    注意：由圖4可以看到，Memory Clerk只是分配內(nèi)存的一部分，另一部分是數(shù)據(jù)緩存（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)生缺頁(yè)中斷，將一部分頁(yè)面置換出內(nèi)存，然后將需要的部分從硬盤讀到內(nèi)存，關(guān)于這塊，可以讀我之前寫的一篇文章:淺談操作系統(tǒng)對(duì)內(nèi)存的管理。

    因此Buffer Pool的作用時(shí)緩沖數(shù)據(jù)頁(yè)，使得未來(lái)讀取數(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)存，可以通過如下語(yǔ)句查看，如圖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所分配的頁(yè)面和SQL Server OS頁(yè)面大小是一致的，也就是8192字節(jié)，當(dāng)SQL Server其它部分需要向”Buffer Pool”借內(nèi)存時(shí),也只能按照8k為單位借，并且這部分內(nèi)存在物理內(nèi)存中是不連續(xù)的，這聽上去像是Buffer Pool內(nèi)存管理自成體系，可以這么理解，因?yàn)锽uffer Pool 不使用任何SQL Server的page allocator,而直接使用virtual或AWE SQLOS's的接口。

    所以SQL Server所占用的內(nèi)存可以用這個(gè)公式粗略估算出來(lái): buffer pool占用的內(nèi)存+從buffer pool借的頁(yè)占得內(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)行分配的。

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

    解決辦法之一是通過減少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來(lái)說(shuō)，需要在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?；窘鉀Q了尋址范圍不夠的問題。

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)存，直接和更底層的頁(yè)表(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)存頁(yè)(Lock Page In Memory)

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

    但這又暴漏出了另一個(gè)問題，因?yàn)镾QL Server鎖定了內(nèi)存頁(yè)，當(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)存有多少用多少，因此很有可能在無(wú)法做出對(duì)Windows低內(nèi)存的響應(yīng)時(shí)造成Windows的不穩(wěn)定甚至崩潰。因此開啟了”Lock Page In Memory”之后，要限制SQL Server Buffer Pool的內(nèi)存使用，前面圖2中已經(jīng)說(shuō)了，這里就不再細(xì)說(shuō)了。

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

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

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