（1）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分配了多少記憶體，它就占用多少多少記憶體，從而達到性能最佳。

7.總結

這是我初次學習SQL Server性能調優方面的知識，謝謝博友花陰偷移指點。很多知識點方面可能理解有偏差，希望各個路過大神指點一二。

參考文獻：
Microsoft.SQL.Server企業級平臺管理實踐
SQL Server 2012 記憶體管理 (memory management) 改進