一些內存使用錯誤理解

在實際的場景中會遇到各種奇怪的問題，為什么會感覺到奇怪，因為沒有理論支撐的東西才感覺到奇怪，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