SQL Server記憶體理解的誤區

SQL Server記憶體理解的誤區
SQL Server記憶體理解

記憶體的讀寫速度要遠遠大於磁碟，對於資料庫而言，會充分利用記憶體的這種優勢，將資料儘可能多地從磁碟緩衝到記憶體中，從而使資料庫可以直接從記憶體中讀寫資料，減少對機械磁碟的IO請求，提高資料讀寫的效率。

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

SQL Server 2012實施與管理實戰指南 (俞榕剛，朱樺) PDF高清掃描版[250M] ：

誤區一：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記憶體（實際上會小一點，因為作業系統本身需要佔用部分記憶體）。

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

作業系統類別	SQL Server可用地址空間	備忘
32位	2G
32位+/3G開關	3G
32位（標準版、企業版）+SQL Server AWE	64G（標準版、企業版）
32位（標準版、企業版）+/3G開關+SQL Server AWE	16G（標準版、企業版）	這個是參考資料的，實際情境下我沒有用過。

 

誤區二：在開啟了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的推薦配置中，我們經常建議客戶這樣做。不夠為了避免記憶體佔用太大，可以通過設定最大記憶體來限定記憶體的使用上限。