一些記憶體使用錯誤了解一些記憶體使用錯誤了解

在實際的場景中會遇到各種奇怪的問題，為什麼會感覺到奇怪，因為沒有理論支撐的東西才感覺到奇怪，SQL Server自己管理記憶體，我們可以幹預的方式也很少，是以日常很難遇到處理記憶體問題的案例。當遇到了原有的知識儲備已經變得模糊，這是已經記不住第幾遍閱讀《SQL 2012實施與管理實戰指南》記憶體管理章節，也分享給群友。

    本文來澄清一些使用者經常對SQL Server記憶體使用的誤解。對這些知識的了解可以幫助資料庫管理者了解SQL Server的行為 

    1. Windows上還有很多實體記憶體沒有被使用，就意味着SQL Server不缺記憶體

    這個觀點是非常錯誤的，因為:

    (1)SQL Server很可能設定了Max Server Memory，限制了SQL Server繼續申請記憶體的能力。（注：實際場景中就遇到過客戶128GB記憶體的機器SQL Server 的Max Server Memory 被設定成2048MB 導緻嚴重的問題）

    (2)在32位的機器上 ,由于虛拟位址空間的限制，SQL Server可能已經無法繼續申請記憶體。

    例如，在一台4 GB或更大的32位伺服器上，SQL Server最多隻能使用2 GB實體記憶體。一般Windows會使用500 MB左右的實體記憶體，這台機器可能就有1 GB多的空閑實體記憶體。這部分記憶體是SQL Server不開AWE就用不到的。 是以，伺服器上有很多空閑實體記憶體，隻能說明Windows不缺記憶體，不能說明SQL Server自己不缺記憶體。（這也是為什麼建議更換老一代伺服器，使用64位的作業系統和SQL Server的原因）

    2.  SQL Server程序的Private Bytes(或Working Set)在不停地向上漲，說明SQL Server有記憶體洩漏的問題

    恰恰相反，對于申請記憶體，SQL Server有嚴格的限制。在32位的伺服器上，不管是Buffer Pool還是MemToLeave，都有一個使用最大上限。當Windows感覺到有記憶體壓力的時候，SQL Server又會主動地釋放記憶體是以作為一個程序，SQL Serve發生記憶體洩漏的機會是非常小的。

    之是以會有這樣的疑惑，是因為SQL Server啟動的時候，僅會Commit它啟動所需要的那部分記憶體。随着使用者的使用，SQL Server會不斷地申請記憶體，直到Windows感覺到記憶體壓力，或者SQL到了自己的上限為止,在這之前，SQL Server的記憶體使用量的确會不斷地增加。對SQL Server來講，緩存越多的資料，它的性能會越好這種增長是正常的，不用焦慮如果不希望SQL Server使用那麼多記憶體，隻需設定Max Server Memory這個上限即可。

　　（注：實際情況中很多人提到過，SQL Server竟然吃掉了所有伺服器的記憶體，是不是記憶體洩露了？或問記憶體是不是這就是我系統的瓶頸呀？） 

    3.  Max Server Memory的值，就是SQL Server記憶體使用量的最大值。超過這個值就不正常

　　Max Server Memory這個值應該是Buffer Pool的上限(此點針對SQL Server 2005/2008而言，對于SQL Server 2012記憶體管理發生了非常大的變化)，而不是SQL Server所有記憶體使用的上限。由于SQL Server 記憶體的使用包括Buffer Pool和MemToLeave，是以SQL Server實際記憶體使用量一定會比Max Server Memory要大但是在正常情況下SQL Server MemToLeave的使用會遠小于Buffer Pool的使用，控制好Buffer Pool，就基木控制住了SQL Server的整體記憶體使用量

 　　（注：建議無論記憶體是否存在壓力都要合理的設定最大記憶體，PS：我也曾經被使用的記憶體超過設定的Max Server Memory吓了一跳）

　　

    4. SQL Server的記憶體使用總量，就是性能螢幕裡面的SQL Server:Memory Manager一Total Server Memory的值

    性能螢幕裡面與SQL Server相關的counter，都是SQL Server自己負責收集的。從SQL Server 2005以後，SQL整合了所有的記憶體申請，讓它們使用同一的接口。是以SQL Server對自己申請的記憶體數量，是了如指掌的，但問題是，在SQL Server程序裡運作的代碼不都是SQL Server自己的代碼。對第三方的代碼，SQL Server是不知道它們申請了多少記憶體的。

    SQLServer:Memory Manager - Total Server Memory的值，是SQL Server自己的代碼申請的記憶體空間大小。真正SQL Server程序申請的空間值，會比這個值大一些。(具體大多少和MemToLeave的大小有關系)

    如果SQL Server沒有開啟AWE,SQL Server程序申請的邏輯記憶體數和實體記憶體數可以由Process下的Private Bytes和Working Set看出。這兩個值會包含所有的記憶體支出，包括SQL自己的代碼和第三方的代碼。

    如果SQL Server開啟了AWE，問題就比較尴尬了。因為Windows沒有辦法正确判斷出一個使用了AWE 記憶體的程序，究竟總共用了多少記憶體。我們隻能借助SQLServer:Memory Manager一Total Server Memory來判斷SQL Server的Buffer Pool使用量。至于SQL Server自己申請的記憶體總數(Buffer Pool + MemToLeave )，可以通過查詢和記憶體相關的DMV計算出來，但第三方的代碼申請的記憶體，就很難做精确計算了

 　　

    5.當系統有記憶體壓力的時候，SQL Server總是會自動釋放記憶體

    預設情況下，SQL Server的确會在系統有記憶體壓力的時候自動釋放記憶體但是有個例外:SQL Server啟動時會試圖做“Lock Page In Memory”的動作。如果啟動賬号有這個權限，動作就會成功。那麼當同一台伺服器上的其他應用程式需要記憶體的時候，SQL Server很可能不會釋放記憶體。是以在這種情況下，建議SQL Server設定Max Server Memory上限。

　　（注：Lock Page In Memory很多資料上寫到SQL的記憶體不會被釋放了，但實際情況中，當作業系統感覺到壓力一樣會把SQL的記憶體釋放掉，也是錯誤了解6的由來） 

    6.  SQL Server有辦法将自己的記憶體綁定在實體記憶體裡

    SQL Server的确想通過Lock Page In Memory的方法達到這個目的。但是，作為一個使用者态為主的應用程式，它還是會受限于核心态。如果核心态裡發出記憶體要求，SQL Server就會被迫把自己的記憶體釋放出來。

    7.增加MemToLeave的大小可以提高SQL Server的性能

    在32位的SQL Server上，預設MemToLeave是256 MB+0.5 MB x ( Max Thread數目)。如果MemToLeave 用完了，SQL Server的一些重要功能就不能進行，甚至新的連接配接都建立不起來是以一些對MemToLeave需求比較大的SQL Server，例如，一些經常運作Linked Server分布式查詢的SQL Server，或者是一些運作CLR,Extended Stored Procedur的SQL Server，可能不得不再加一些MemToLeave空間。這可以使用SQL Server的

一個啟動參數一g完成。例如，如果想把MemToLeave設成512 MB+0.5 MB x ( Max Thread數目)，可以加啟動參數一g512。

    但是需要想清楚的是，SQL Server的虛拟位址空間隻有2 GB，給MemToLeave越多，Buffer Pool能拿到的就越少。Data Cache的記憶體還可以從AWE擴充的記憶體補，Buffer Pool裡的Stolen記憶體就沒辦法了。是以其實這是拆東牆補西牆，關鍵要看哪一塊記憶體對SQL Server的性能和穩定運作最重要。不必要多給，這會浪費資源，影響Buffer Pool裡面的性能。隻有确定了SQL Server的MemToLeave的确不夠，才應該去增大它

　 

　8.增加實體記憶體一定會提高SQL Server的性能

    既然SQL Server這麼喜歡記憶體，那管理者多給伺服器配備一些記憶體，是不是就一定能夠提高性能呢?很多時候這個想法是對的，但是并不是總是正确。這是因為增加的記憶體SQL Server不一定用得到。

    (1)首先要檢查是哪一部分的記憶體有瓶頸，是Database Cache，還是Stolen，是Buffer Pool，還是MemToLeave。

    (2)要檢查缺的那部分記憶體是不是已經到了理論上的上限，新加記憶體SQL Server用不用得上例如，在32位+AWE的伺服器上，Buffer Pool裡的Stolen Memory最多也不過1.6 GB。如果是這一部分記憶體不夠，再加記憶體也沒有用。隻有将系統更新到64位，才能突破這些限制。

    (3)加人的記憶體，一般大部分都會被Buffer Pool使用，這樣SQL Server就會緩存更多的資料頁面和執行計劃，大多數時候這會對性能有幫助。如果Database Pages沒有壓力，SQL Server會緩存太多的動态T-SQL執行計劃，對性能沒什麼好處，反而會增加SQL Server的維護成本。隻有在SQL Server的Database Page缺記憶體的時候，增加記憶體才會對性能有明顯幫助。如果資料庫比較小，常用資料頁面已經緩存在了記憶體裡，增加記憶體對性能不會有太大幫助。

　　（注：記憶體這個東西對于一些初級DBA分析起來還是有些難度，而很多情況下隻要看到記憶體壓力就要添加記憶體這樣也是不對的，很多時候記憶體的消耗很自己程式的語句優化程度也有着非常大的關系） 

　9. Stolen的記憶體真的是偷來的嗎

   很多在描述Stolen的記憶體塊的時候都說這塊記憶體是不需要保留(Reserve)而直接送出(Commit )使用的，是以這塊記憶體叫作Stolen。這麼說錯了嗎?其實，從Windows的層面，對于任何記憶體的使用，都必須遵循先Reserve再Commit的過程。對于一塊記憶體，如果不Reserve而直接使用會導緻通路違例(Access Violation)因為SQL Server内部的記憶體管理機制，所有要使用的記憶體，Buffer Pool都已經幫我們保留(Reserve)好了如果SQL所要做的，是用Buffer Pool已經保留(Reserve)好了的那部分位址空間去直接送出(Commit )，而且，這一部分記憶體又不是用來存放Buffer的，就被稱為Stolen

本文轉自  zddnd  51CTO部落格，原文連結:http://blog.51cto.com/13013666/1939697