SQL Server記憶體機制詳解

1.前言

對於資料庫引擎來說，記憶體是一個效能提升的重要解決手段。把資料快取起來，可以避免在查詢或更新資料時花費多餘的時間，而這時間通常是從磁碟獲取資料時用來等待磁碟定址的。把執行計劃快取起來，可以避免重複分析執行計劃時帶來額外的CPU及各種資源的開銷。通過在記憶體中開闢查詢記憶體空間，可以迅速地完成排序、雜湊等計算，達到快速返回運算結果的目的。若沒有足夠的記憶體空間，資料庫引擎將無法快速地響應使用者的請求。

2.SQL Server如何從作業系統層面分配記憶體

SQL Server儲存引擎本身是一個Windows下的程序，所以SQL Server使用記憶體和其它Windows程序一樣，都需要向Windows申請記憶體（通過VirtualAlloc之類的API向Windows申請記憶體）。

3.實體記憶體、虛擬記憶體、虛擬記憶體管理器

3.1實體記憶體（RAM）

記憶體晶片提供的物理儲存空間，能被CPU直接存取，存取速度快，易丟失。記憶體效能指標GB/s,ns（納秒），前者是吞吐量，後者是響應時間。磁碟效能指標MB/s,us(微秒)，從兩者對比就能看出記憶體存取速度是遠優於磁碟的。

3.2虛擬記憶體

實體記憶體容量是有限的，如果所有程序都直接使用有限的實體記憶體，那新的程序將無法為他們找到任何實體記憶體，那麼實體記憶體將容易成為瓶頸。所以Windows會授予每個程序一個虛擬地址空間（Virtual Address Space，VAS），通過VAS建立應用程式與實體記憶體的橋樑。

3.2.1虛擬地址空間（Virtual Address Space，VAS）

是指一個應用程式能夠申請存取的最大地址空間。VAS作為中間的抽象層的，不是所有的請求都直接對映到實體記憶體，它首先對映到VAS，然後對映到實體記憶體。
而兩個程序可以共用一個VAS，而VAS的大小取決於CPU架構，具體請看下面表格：

VAS有兩種記憶體模式，Kernel Model和User Model。Kernel Model下的VAS是供Windows系統程序使用，而User Model下的VAS是供使用者程序使用。
由表格可知，32位元Windows系統應用程式可以存取最大2GB的VAS，64Windows位系統可以存取最大8TB的VAS。這意味著在32位元Windows系統中一個word檔案程序跟一個SQL Server程序能得到最大2GB的VAS是一樣的。因此，從理論上講，這意味著任何應用程式程序在32位元Windows系統上都將共用最大限度的2 G的VAS。

3.3虛擬記憶體管理器（Virtual Memory Manager，VMM）

VMM是負責把實體記憶體在系統中所有需要記憶體的程序之間作共用，必要時會從VAS回收實體記憶體，把資料儲存到頁面檔案上面去，保證資料永不丟失。當程序需要記憶體時，VMM會從頁面檔案中查詢資料，並將這資料寫入一部分空閒記憶體當中，然後將新頁面對映到需要操作的VAS當中。

4.SQL Server記憶體架構

SQL Server 2012對記憶體管理這塊跟SQL Server 2008還是有比較大的區別的，參考一些資料，下面我們來看看兩者具體架構。

SQL Server 2008 R2：

SQL Server 2012：

名詞術語

4.1緩衝池（Buffer Pool）

為了更加清楚瞭解Buffer Pool，我們先來了解下 SQL Server的所需要的記憶體有哪些，其中包括SQL Server服務(sqlserver.exe)和其它一些元件所佔用的記憶體，例如SQL Server代理程式（sqlagent.exe）, SQL Server複製代理程式、SQL Server報表服務(ReportingServicesService.exe)、SQL Server Analysis Services(msmdsrv.exe)、SQL Server Integration Services(MsDtsSrvr.exe)，和SQL Server 全文搜尋(msftesql.exe)。
在一臺執行SQL Server的伺服器上，執行著sqlserver服務(sqlserver.exe)和其它一些元件。在sqlserver服務(sqlserver.exe)獲取到的記憶體中，又分為2大塊：一部分為Buffer Pool，另一部分為非Buffer Pool，舊稱MemToReserve（預設sqlserver.exe給它預留了256MB）。下表為這兩部分記憶體各自的用途：

4.2 Single-Page

這塊記憶體是<=8kb的儲存，適用於sql server 2008及以前，屬於Buffer Pool緩衝池來分配。有儲存資料頁面，Consumer功能元件。

4.3 Multi-Page

這塊記憶體是>8kb的儲存，適用於sql server 2008及以前，不屬於Buffer Pool緩衝池來分配, 有儲存Consumer功能元件, 第三方程式碼, Threads執行緒。

4.4 Any Size Page

這個適用於sql server 2012及以上，整合了single-page,multi-page統稱any size page。

4.5 Memory Manager

它來統一響應SQL Server 內部各種元件記憶體申請的請求。因為這個原因，在SQL Server 2012裡面，max server memory 不再像以前的版本那樣，只控制buffer pool的大小，也包括那些大於8kb 的記憶體請求。也就是，max server memory能夠更準確地控制SQL Server 的記憶體使用了。

5.SQL Server 2008記憶體

從記憶體架構我們可以看到有page reservation需預先申請的記憶體，有momory objects從windows api申請的記憶體，有clr第三方申請的記憶體。

記憶體使用分類

5.1按用途分類

（1）Database Cache（資料頁面緩衝區）

存放資料頁面的緩衝區。SQL Server資料庫裡的資料都是以8KB為一個頁面儲存。當有使用者需要使用到這個頁面上儲存的資料時，SQL Server會把整個頁面都調入記憶體，供使用者使用。所以8KB是資料存取的最小單元。當用戶修改了某個頁面上的資料時，SQL Server會在記憶體中將這個頁面修改，但是不會立刻將這個頁面寫回磁碟，而是等到後面的Checkpoint或Lazy Write的時候集中處理。

（2）各類Consumer

SQL Server的很多功能元件，都必須要申請記憶體來完成它們的任務。這些統稱為“Consumer”。常見有如下：

• Connection：SQL Server為每個連線分配一個資料結構，儲存關於這個連線的資訊。另外，還會分配一個輸入緩衝池，緩衝使用者端發來的指令；一個輸出緩衝池，存放SQL Server返回的結果，等待使用者端取走。
• General：一組大雜燴。包括語句的編譯、正規化化、每個鎖資料結構、事務上下文、表格和索引的後設資料等。
• Query Plan：語句和儲存過程的執行計劃。和Database Cache類似，如果SQL Server沒有記憶體壓力，它就會保留每一個生成的執行計劃，供以後的使用者重用，減少Comlile的消耗。所以Query Plan也會是一塊比較大的記憶體使用區域。
• Optimizer：SQL Server在生成執行計劃的過程中需要消耗的記憶體。
• Utilities：像BCP、Log Manager、Parallel Queries、Backup等比較特殊的操作需要的記憶體。

（3）執行緒記憶體

SQL Server會為每個程序內的每個執行緒分配0.5MB的記憶體，以存放執行緒的資料結構和相關資訊。

（4）第三方程式碼申請的記憶體（COM，XP...）

在SQL Server的程序裡，會執行一些非SQL Server自身的程式碼。例如，使用者定義的CLR或者Extended Stored Procedure程式碼，Linked Server需要載入的資料連結驅動，呼叫SQL Mail功能需要載入的MAPI動態庫等。這些程式碼也會申請記憶體，會算在SQL Server自己都不知道。

5.2按申請方式分類

有些SQL Server記憶體的申請方式，是預先Reserve一塊大的記憶體，然後在使用的時候一小塊一小塊地Commit。而另外的記憶體申請則直接從空間裡Commit。在SQL Server裡，把後一種方式叫Stolen。
在SQL Server裡，對Database Cache，SQL Server會先Reserve，再Commit。其他的所有記憶體使用，基本都是直接Commit，都是“Stolen”。要重申的是，Stolen記憶體也是正常使用的記憶體，不是洩漏掉的記憶體。
之所以要把這兩種分開，是因為SQL Server不會對Stolen的記憶體使用AWE功能。也就是說，AWE擴充套件出去的記憶體，只能用來存放Database Cache。其他記憶體還要在原來的那2GB裡想辦法。

5.3按申請大小分類

對於SQL Server自己申請的記憶體，有兩種記憶體申請單位。
小於等於8KB一個單位記憶體申請，SQL Server就分配一個8KB頁面。所有這些頁面都集中管理，這塊記憶體被稱為Buffer Pool。一次一個頁面的這種分配稱為Single Page Allocation。
對於大於8KB為單位的記憶體申請，SQL Server把它們集中在另外一個區域，稱為Multi-Page Allocation（舊稱MemToLeave）。而這種分配稱為Multi-Page Allocation。

5.4各個記憶體分類方法之間的關係：

這裡的一個例外是執行在SQL Server程序裡的CLR程式碼所申請的記憶體。這部分記憶體像第三方程式碼一樣，也是使用MemToLeave的記憶體。但是，CLR可能也會用Reserve-Commit的方式申請記憶體。所以MemToLeave的記憶體也並不是都是Stolen的。

6.SQL Server 2012記憶體

根據SQL Server記憶體架構圖，我們可以知道，在2012版本上，Single Page Allocation跟Multi-Page Allocation合併為Any Size Page Allocation了。而max server memory控制的不但是 Buffer Pool記憶體大小，而是所有大於等於小於8KB的記憶體請求。
如圖：

比如我設定最小伺服器記憶體為8G，重新啟動下SQL Server (MSSQLSERVER)服務，再使用dmv來檢視當前範例的總記憶體空間，以及佔用記憶體空間：
--Target Server Memory (KB)最多能申請的記憶體量
--Total Server Memory (KB)目前使用了多少記憶體量

SELECT counter_name, ltrim(cntr_value*1.0/1024.0/1024.0)+'G' 
AS memoryGB FROM sys.dm_os_performance_counters 
WHERE counter_name like '%target%server%memory%'or counter_name like '%total%memory%'

從查詢結果可以看到當我們在SQL Server設定最小伺服器記憶體為8G的時候，給SQL Server分配了多少記憶體，它就佔用多少多少記憶體，從而達到效能最佳。

到此這篇關於SQL Server記憶體機制的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支援it145.com。