一些內(nèi)存使用錯(cuò)誤理解

在實(shí)際的場(chǎng)景中會(huì)遇到各種奇怪的問(wèn)題，為什么會(huì)感覺(jué)到奇怪，因?yàn)闆](méi)有理論支撐的東西才感覺(jué)到奇怪，SQL Server自己管理內(nèi)存，我們可以干預(yù)的方式也很少，所以日常很難遇到處理內(nèi)存問(wèn)題的案例。當(dāng)遇到了原有的知識(shí)儲(chǔ)備已經(jīng)變得模糊，這是已經(jīng)記不住第幾遍閱讀《SQL 2012實(shí)施與管理實(shí)戰(zhàn)指南》內(nèi)存管理章節(jié)，也分享給群友。

一些內(nèi)存使用錯(cuò)誤理解

    本文來(lái)澄清一些用戶(hù)經(jīng)常對(duì)SQL Server內(nèi)存使用的誤解。對(duì)這些知識(shí)的理解可以幫助數(shù)據(jù)庫(kù)管理員理解SQL Server的行為 

    1. Windows上還有很多物理內(nèi)存沒(méi)有被使用，就意味著SQL Server不缺內(nèi)存

    這個(gè)觀點(diǎn)是非常錯(cuò)誤的，因?yàn)?

    (1)SQL Server很可能設(shè)置了Max Server Memory，約束了SQL Server繼續(xù)申請(qǐng)內(nèi)存的能力。（注：實(shí)際場(chǎng)景中就遇到過(guò)客戶(hù)128GB內(nèi)存的機(jī)器SQL Server 的Max Server Memory 被設(shè)置成2048MB 導(dǎo)致嚴(yán)重的問(wèn)題）

    (2)在32位的機(jī)器上 ,由于虛擬地址空間的限制，SQL Server可能已經(jīng)無(wú)法繼續(xù)申請(qǐng)內(nèi)存。

    例如，在一臺(tái)4 GB或更大的32位服務(wù)器上，SQL Server最多只能使用2 GB物理內(nèi)存。一般Windows會(huì)使用500 MB左右的物理內(nèi)存，這臺(tái)機(jī)器可能就有1 GB多的空閑物理內(nèi)存。這部分內(nèi)存是SQL Server不開(kāi)AWE就用不到的。 所以，服務(wù)器上有很多空閑物理內(nèi)存，只能說(shuō)明Windows不缺內(nèi)存，不能說(shuō)明SQL Server自己不缺內(nèi)存。（這也是為什么建議更換老一代服務(wù)器，使用64位的操作系統(tǒng)和SQL Server的原因）

    2.  SQL Server進(jìn)程的Private Bytes(或Working Set)在不停地向上漲，說(shuō)明SQL Server有內(nèi)存泄漏的問(wèn)題

    恰恰相反，對(duì)于申請(qǐng)內(nèi)存，SQL Server有嚴(yán)格的限制。在32位的服務(wù)器上，不管是Buffer Pool還是MemToLeave，都有一個(gè)使用最大上限。當(dāng)Windows感覺(jué)到有內(nèi)存壓力的時(shí)候，SQL Server又會(huì)主動(dòng)地釋放內(nèi)存所以作為一個(gè)進(jìn)程，SQL Serve發(fā)生內(nèi)存泄漏的機(jī)會(huì)是非常小的。

    之所以會(huì)有這樣的疑惑，是因?yàn)镾QL Server啟動(dòng)的時(shí)候，僅會(huì)Commit它啟動(dòng)所需要的那部分內(nèi)存。隨著用戶(hù)的使用，SQL Server會(huì)不斷地申請(qǐng)內(nèi)存，直到Windows感覺(jué)到內(nèi)存壓力，或者SQL到了自己的上限為止,在這之前，SQL Server的內(nèi)存使用量的確會(huì)不斷地增加。對(duì)SQL Server來(lái)講，緩存越多的數(shù)據(jù)，它的性能會(huì)越好這種增長(zhǎng)是正常的，不用焦慮如果不希望SQL Server使用那么多內(nèi)存，只需設(shè)置Max Server Memory這個(gè)上限即可。

　?。ㄗⅲ簩?shí)際情況中很多人提到過(guò)，SQL Server竟然吃掉了所有服務(wù)器的內(nèi)存，是不是內(nèi)存泄露了？或問(wèn)內(nèi)存是不是這就是我系統(tǒng)的瓶頸呀？） 

    3.  Max Server Memory的值，就是SQL Server內(nèi)存使用量的最大值。超過(guò)這個(gè)值就不正常

　　Max Server Memory這個(gè)值應(yīng)該是Buffer Pool的上限(此點(diǎn)針對(duì)SQL Server 2005/2008而言，對(duì)于SQL Server 2012內(nèi)存管理發(fā)生了非常大的變化)，而不是SQL Server所有內(nèi)存使用的上限。由于SQL Server 內(nèi)存的使用包括Buffer Pool和MemToLeave，所以SQL Server實(shí)際內(nèi)存使用量一定會(huì)比Max Server Memory要大但是在正常情況下SQL Server MemToLeave的使用會(huì)遠(yuǎn)小于Buffer Pool的使用，控制好Buffer Pool，就基木控制住了SQL Server的整體內(nèi)存使用量

 　?。ㄗⅲ航ㄗh無(wú)論內(nèi)存是否存在壓力都要合理的設(shè)置最大內(nèi)存，PS：我也曾經(jīng)被使用的內(nèi)存超過(guò)設(shè)置的Max Server Memory嚇了一跳）

    4. SQL Server的內(nèi)存使用總量，就是性能監(jiān)視器里面的SQL Server:Memory Manager一Total Server Memory的值

    性能監(jiān)視器里面與SQL Server相關(guān)的counter，都是SQL Server自己負(fù)責(zé)收集的。從SQL Server 2005以后，SQL整合了所有的內(nèi)存申請(qǐng)，讓它們使用同一的接口。所以SQL Server對(duì)自己申請(qǐng)的內(nèi)存數(shù)量，是了如指掌的，但問(wèn)題是，在SQL Server進(jìn)程里運(yùn)行的代碼不都是SQL Server自己的代碼。對(duì)第三方的代碼，SQL Server是不知道它們申請(qǐng)了多少內(nèi)存的。

    SQLServer:Memory Manager - Total Server Memory的值，是SQL Server自己的代碼申請(qǐng)的內(nèi)存空間大小。真正SQL Server進(jìn)程申請(qǐng)的空間值，會(huì)比這個(gè)值大一些。(具體大多少和MemToLeave的大小有關(guān)系)

    如果SQL Server沒(méi)有開(kāi)啟AWE,SQL Server進(jìn)程申請(qǐng)的邏輯內(nèi)存數(shù)和物理內(nèi)存數(shù)可以由Process下的Private Bytes和Working Set看出。這兩個(gè)值會(huì)包含所有的內(nèi)存支出，包括SQL自己的代碼和第三方的代碼。

    如果SQL Server開(kāi)啟了AWE，問(wèn)題就比較尷尬了。因?yàn)閃indows沒(méi)有辦法正確判斷出一個(gè)使用了AWE 內(nèi)存的進(jìn)程，究竟總共用了多少內(nèi)存。我們只能借助SQLServer:Memory Manager一Total Server Memory來(lái)判斷SQL Server的Buffer Pool使用量。至于SQL Server自己申請(qǐng)的內(nèi)存總數(shù)(Buffer Pool + MemToLeave )，可以通過(guò)查詢(xún)和內(nèi)存相關(guān)的DMV計(jì)算出來(lái)，但第三方的代碼申請(qǐng)的內(nèi)存，就很難做精確計(jì)算了

    5.當(dāng)系統(tǒng)有內(nèi)存壓力的時(shí)候，SQL Server總是會(huì)自動(dòng)釋放內(nèi)存

    默認(rèn)情況下，SQL Server的確會(huì)在系統(tǒng)有內(nèi)存壓力的時(shí)候自動(dòng)釋放內(nèi)存但是有個(gè)例外:SQL Server啟動(dòng)時(shí)會(huì)試圖做“Lock Page In Memory”的動(dòng)作。如果啟動(dòng)賬號(hào)有這個(gè)權(quán)限，動(dòng)作就會(huì)成功。那么當(dāng)同一臺(tái)服務(wù)器上的其他應(yīng)用程序需要內(nèi)存的時(shí)候，SQL Server很可能不會(huì)釋放內(nèi)存。所以在這種情況下，建議SQL Server設(shè)置Max Server Memory上限。

　?。ㄗⅲ篖ock Page In Memory很多資料上寫(xiě)到SQL的內(nèi)存不會(huì)被釋放了，但實(shí)際情況中，當(dāng)操作系統(tǒng)感覺(jué)到壓力一樣會(huì)把SQL的內(nèi)存釋放掉，也是錯(cuò)誤理解6的由來(lái)） 

    6.  SQL Server有辦法將自己的內(nèi)存綁定在物理內(nèi)存里

    SQL Server的確想通過(guò)Lock Page In Memory的方法達(dá)到這個(gè)目的。但是，作為一個(gè)用戶(hù)態(tài)為主的應(yīng)用程序，它還是會(huì)受限于核心態(tài)。如果核心態(tài)里發(fā)出內(nèi)存要求，SQL Server就會(huì)被迫把自己的內(nèi)存釋放出來(lái)。

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

    在32位的SQL Server上，默認(rèn)MemToLeave是256 MB+0.5 MB x ( Max Thread數(shù)目)。如果MemToLeave 用完了，SQL Server的一些重要功能就不能進(jìn)行，甚至新的連接都建立不起來(lái)所以一些對(duì)MemToLeave需求比較大的SQL Server，例如，一些經(jīng)常運(yùn)行Linked Server分布式查詢(xún)的SQL Server，或者是一些運(yùn)行CLR,Extended Stored Procedur的SQL Server，可能不得不再加一些MemToLeave空間。這可以使用SQL Server的

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

    但是需要想清楚的是，SQL Server的虛擬地址空間只有2 GB，給MemToLeave越多，Buffer Pool能拿到的就越少。Data Cache的內(nèi)存還可以從AWE擴(kuò)展的內(nèi)存補(bǔ)，Buffer Pool里的Stolen內(nèi)存就沒(méi)辦法了。所以其實(shí)這是拆東墻補(bǔ)西墻，關(guān)鍵要看哪一塊內(nèi)存對(duì)SQL Server的性能和穩(wěn)定運(yùn)行最重要。不必要多給，這會(huì)浪費(fèi)資源，影響B(tài)uffer Pool里面的性能。只有確定了SQL Server的MemToLeave的確不夠，才應(yīng)該去增大它

　8.增加物理內(nèi)存一定會(huì)提高SQL Server的性能

    既然SQL Server這么喜歡內(nèi)存，那管理員多給服務(wù)器配備一些內(nèi)存，是不是就一定能夠提高性能呢?很多時(shí)候這個(gè)想法是對(duì)的，但是并不是總是正確。這是因?yàn)樵黾拥膬?nèi)存SQL Server不一定用得到。

    (1)首先要檢查是哪一部分的內(nèi)存有瓶頸，是Database Cache，還是Stolen，是Buffer Pool，還是MemToLeave。

    (2)要檢查缺的那部分內(nèi)存是不是已經(jīng)到了理論上的上限，新加內(nèi)存SQL Server用不用得上例如，在32位+AWE的服務(wù)器上，Buffer Pool里的Stolen Memory最多也不過(guò)1.6 GB。如果是這一部分內(nèi)存不夠，再加內(nèi)存也沒(méi)有用。只有將系統(tǒng)升級(jí)到64位，才能突破這些限制。

    (3)加人的內(nèi)存，一般大部分都會(huì)被Buffer Pool使用，這樣SQL Server就會(huì)緩存更多的數(shù)據(jù)頁(yè)面和執(zhí)行計(jì)劃，大多數(shù)時(shí)候這會(huì)對(duì)性能有幫助。如果Database Pages沒(méi)有壓力，SQL Server會(huì)緩存太多的動(dòng)態(tài)T-SQL執(zhí)行計(jì)劃，對(duì)性能沒(méi)什么好處，反而會(huì)增加SQL Server的維護(hù)成本。只有在SQL Server的Database Page缺內(nèi)存的時(shí)候，增加內(nèi)存才會(huì)對(duì)性能有明顯幫助。如果數(shù)據(jù)庫(kù)比較小，常用數(shù)據(jù)頁(yè)面已經(jīng)緩存在了內(nèi)存里，增加內(nèi)存對(duì)性能不會(huì)有太大幫助。

　?。ㄗⅲ簝?nèi)存這個(gè)東西對(duì)于一些初級(jí)DBA分析起來(lái)還是有些難度，而很多情況下只要看到內(nèi)存壓力就要添加內(nèi)存這樣也是不對(duì)的，很多時(shí)候內(nèi)存的消耗很自己程序的語(yǔ)句優(yōu)化程度也有著非常大的關(guān)系） 

　9. Stolen的內(nèi)存真的是偷來(lái)的嗎

   很多在描述Stolen的內(nèi)存塊的時(shí)候都說(shuō)這塊內(nèi)存是不需要保留(Reserve)而直接提交(Commit )使用的，所以這塊內(nèi)存叫作Stolen。這么說(shuō)錯(cuò)了嗎?其實(shí)，從Windows的層面，對(duì)于任何內(nèi)存的使用，都必須遵循先Reserve再Commit的過(guò)程。對(duì)于一塊內(nèi)存，如果不Reserve而直接使用會(huì)導(dǎo)致訪問(wèn)違例(Access Violation)因?yàn)镾QL Server內(nèi)部的內(nèi)存管理機(jī)制，所有要使用的內(nèi)存，Buffer Pool都已經(jīng)幫我們保留(Reserve)好了如果SQL所要做的，是用Buffer Pool已經(jīng)保留(Reserve)好了的那部分地址空間去直接提交(Commit )，而且，這一部分內(nèi)存又不是用來(lái)存放Buffer的，就被稱(chēng)為Stolen