淺談SQL Server 關於內存的治理[圖文]。本站提示廣大學習愛好者：（淺談SQL Server 關於內存的治理[圖文]）文章只能為提供參考，不一定能成為您想要的結果。以下是淺談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可以或許應用若干內存是由以下幾個身分決議的：
1.物理內存的年夜小
2.所裝置Windows版本關於內存的限制(好比windows server 2008尺度版限制最年夜內存只能應用32GB)
3.SQL Server是32位或64位
4.如圖2所示設置裝備擺設SQL Server關於內存的應用量
5.SQL Server的版本(好比express版只能用1G內存)
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停止分派的。
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成績都有能夠是內存所惹起的。
點擊這裡下載本文的PDF版本
