SQL Server內存理解

內存的讀寫速度要遠遠大于磁盤，對于數據庫而言，會充分利用內存的這種優勢，將數據盡可能多地從磁盤緩存到內存中，從而使數據庫可以直接從內存中讀寫數據，減少對機械磁盤的IO請求，提高數據讀寫的效率。

內存對數據庫而言是如此的重要，因此只要在涉及數據庫優化的地方，我們都可以看到內存的身影。我們通常會想盡各種辦法來優化數據庫內存的使用，比如開啟AWE、設置最大內存、鎖定內存頁等，但在很多時候，我們實際上都不知道某個配置是否一定能夠解決當前的問題，或者我們誤以為會解決當前的問題，出現這種現象的原因是我們對數據庫的內存理解還不夠透徹或者理解存在誤區，本文我希望將結合自己的經驗和《SQL Server 2012實施與管理實戰指南》的內容，通過以【介紹SQL server常見內存誤區】的方式跟大家分享下我對SQL server內存的理解。

誤區一：SQL Server在32位操作系統只能使用4G內存

對于這個問題，我們從兩個方面來看：

1. 在32位操作系統中，應用進程可以使用的虛擬地址空間大小為4G，其中2G是給核心態（操作系統），另外2G留給用戶態（應用程序）。因此，SQL server其實能最大利用的內存只有2G。

不過，這種核心態和用戶態的分配方式是可以改變的，當在操作系統的啟動文件（windows server 2003的boot.ini）中開啟3G開關后，便可以使得用戶態的內存增加到3G，而只留1G給核心態，這樣做的好處是可以使得SQL server使用到3G的內存，不過壞處也很明顯，因為減少了核心態的地址空間，在操作系統系統負載比較大時，可能會出現不穩定的風險。

（目前使用使用32位操作系統的客戶越cong來越少，而且微軟新推出的操作系統也都是64位，這種問題以后幾乎都碰不到了。）

2. 從windows server 2003到windows server 2008，只要是標準版或者WEB版，且為32位架構時，操作系統最大也只能支持4G內存，即使按照上文1介紹的方法開啟了3G開關，SQL server也只能使用最大3G的內存。

但如果是企業版或者數據中心版的操作系統時，同樣是32位的架構，操作系統卻最大可以支持到64G的內存（開啟PAE的前提下）。但默認情況下SQL server依然只能使用最大2G的內存。

因此，相比64G的操作系統內存來說，2G對數據庫而言簡直就是杯水車薪，資源浪費。為解決這個問題，微軟為企業版和標準版的SQL server（2005、2008）引入了AWE（地址窗口擴展）功能，你可以通過開啟AWE來擴展SQL server的可用內存，使之最大可以達到64G內存（實際上會小一點，因為操作系統本身需要占用部分內存）。

總結起來，可以用一個表格來描述上面兩個場景：

誤區二：在開啟了AWE后，SQL Server的所有功能一定能使用超過2G的內存。

在SQL server進程中，內存并非全部由SQL server的數據緩存所使用，部分通過SQL server調用的第三方代碼、加載在SQL server內部的其他dll、SQL server連接、鏈接服務器、編譯緩存、查詢計劃緩存等也會使用SQL server進程中的內存。

這部分組件或者功能在申請內存時與傳統的數據緩存申請的方式不同，因為他們通常會申請大于8KB的內存頁，這種內存區域為multi-page（以前叫memtoleave）。對于multi-page占用的內存，是沒法使用SQL Server的AWE特性的，也就是說，在32位的SQL Server中，數據庫即使開啟了AWE，也只能使用到2G的內存（用戶態）。由此可見，AWE更多的是提升了data page buffer pool的內存空間。

備注：在32位的SQL Server中，multi-page的默認大小為256MB+sp_configure配置的最大線程數X512KB，它是SQL server啟動時就已經設定好的。

備注：在64位的SQL server中，multi-page的大小沒有限制。

誤區三：SQL Server進程不會使用超過最大內存設置的內存大小。

在SQL server的sp_configure中有一個max server memory (MB)的配置項（SSMS中右擊實例，在屬性中選擇內存也可以看到），我們很多人以為設置了這個值以后SQL server的進程不會使用超過這個大小的內存。

其實不然，max server memory (MB)只是buffer pool的上限。但是在SQL server的內存中，不僅僅包括buffer pool，還有multi-page的內存，對于這部分內存，是無法通過max server memory (MB)來限制的，所以，在實際環境中，我們可能會看到sqlservr.exe這個進程會占用的內存會超過max server memory (MB)設定的值。

備注：一般情況下，multi-page占用的空間不會很大，因此，通常我們將max server memory (MB)約等于SQL server進程占用的內存大小。

誤區四：設置了SQL Server的最小內存，表示SQL server啟動時就會使用到這個大小的內存。

在SQL server的最小內存也是通過sp_configure配置，配置項為min server memory (MB)。該配置項默認為0，表示不限定最小內存，如果設置成某個具體值時，比如2G，表示當SQL server占用的內存超過這個大小后，就不會再低于這個值。而不是說SQL server啟動時就馬上達到這個值。

這種機制的好處就是避免SQL server的內存被操作系統不斷擠占，這樣當負載一旦起來，數據庫可以立即使用內存而不需要向操作系統申請內存。（當數據庫申請時不見得操作系統有多余的內存分配給數據庫，這時數據庫就只能歇菜了）。

因此合理設置數據庫的最低內存也是十分重要的。微軟建議的最小內存值為服務器總內存減去1-2G，跟最大內存的值差不多。（前提是這個服務器制作數據庫服務器）

參考資料：http://msdn.microsoft.com/en-us/library/ms178067.as

誤區五：SQL server占用內存特別大，說明SQL server有問題。

常見的關系型數據庫都有這個特點：數據庫就會盡可能的占用服務器的內存，而且這些占用的內存中即使很大部分空閑也不會釋放，除非操作系統遇到內存壓力，才會被操作系統重新分配。因此我們可以看到數據庫服務器的內存使用率一般都會很高。

這其實并不是一個問題，反而是數據庫的一個特性，就如文中首段說的，只有越多的數據緩存在內存中，數據庫的讀寫效率才會越高，響應速度才會越快。這才是使用數據庫的最佳方法。

不過，在很多場景下，服務器并非專用于數據庫，為確保服務器上其他應用程序也能正常運行，我們必須為數據庫設置最大內存，否則其他應用程序就會因為內存不足出現訪問不流暢的問題。筆者曾碰到一個案例，某醫院的HIS服務器必須每隔幾天重啟一次才能保證HIS業務能夠被正常訪問。原因就是因為數據庫內存沒有設置最大值，導致應用程序的內存被數據庫擠占。

誤區六：內存使用率高說明數據庫服務器的遇到了內存壓力。

（影響SQL Server性能的因素很多，不過因為本文的中心是談談內存因素與SQL server的關系，所以其他因素不在本文考慮范圍內。）

如果一個DBA因為數據庫服務器的內存使用率很高而做出內存存在壓力的判斷，說明這個人還不夠全面。前文已經說過，數據庫“喜歡”內存是天性，幾乎所有DB服務器都會出現內存使用率很高的問題（除非設置最大內存，且最大內存遠低于服務器內存），因此我們不能據此來判斷數據庫的內存壓力。

我們通常會通過一些性能計數器來監控數據庫的內存使用情況，據此作出壓力的判斷：

SQLServer:Buffer Manager\Buffer cache hit ratio

（一般要求OLTP的cache hit ratio在95%以上，OLAP則需要在90%以上才能稱之

為性能良好）

SQLServer:Buffer Manager\Page life expectancy

SQLServer:Buffer Manager\free pages

SQLServer:Memory Manager\Target Server Memory

SQLServer:Memory Manager\Total Server Memory

上述后三個指標最能直觀反映SQL server是否存在內存壓力，free pages表示SQL server占用的內存中，有多少頁面是free狀態，將這個數值乘以8KB就得到了可用緩存的大小，這個值越大，說明SQL server占用的內存有很多還未被使用，因此說明了內存沒有壓力問題。

Total Server Memory表示SQL server為自己分配的buffer pool的總大小（已使用和未使用）。Target Server Memory表示操作系統能夠分配給SQL server的目標內存大小，其最大值約等于【誤區一】中描述的SQL server可用地址空間的大小，但會隨著操作系統的壓力增加而減少。正常情況下，Target server memory會大于total server memory，說明SQL server為自己分配的buffer pool還比較少，小于操作系統為SQL server設定的目標內存大小，數據庫此時不會有內存壓力。不過，隨著操作系統的壓力增加，它會調小Target Server memory的值，使其小于SQL Server的Total Server Memory，這樣一來，SQL Server不得不釋放已占用的緩存，減小total server memory，這種情況，說明數據庫服務器存在內存壓力。

誤區七：增加內存一定能夠提升SQL server的性能。

數據庫雖然會盡可能多的占用內存，但并不意味增加內存就一定是越多越好，就如同上文說的，如果數據庫的內存長期沒有什么壓力，增加內存也不會帶來性能的提升。

另外，在32位 的SQL server中，在數據庫啟動時就為連接、查詢計劃、第三方dll、鏈接服務器等分配了固定大小的multi-page（上文在介紹AWE時已有介紹），因為multi-page的大小不會隨著內存的增加而改變，所以即使增加內存，也無益于這些功能、組件，而只是為增大了數據緩存。

備注：在64位的SQL server中，multi-page的大小沒有限制。

誤區八：如果其他應用程序也需要內存，SQL server會釋放一部分自己的內存，以保證其他應用程序能夠正常運行。

SQL Server不會為其他程序釋放自己以占用的內存，只有在操作系統遇到內存壓力時，才會根據操作系統的要求減少自己的內存占用量。

但如果SQL server啟用了鎖定內存頁的，那即使是操作系統有要求，其內存也不會釋放。因為鎖定內存頁會使SQL server占用的內存長久保留在物理內存中，避免被分頁到虛擬內存中去，這是提升SQL server性能的常見做法。在SQL Server的推薦配置中，我們經常建議客戶這樣做。不夠為了避免內存占用太大，可以通過設置最大內存來限定內存的使用上限。