對(duì)SQL Server來說，最重要的資源是內(nèi)存、Disk和CPU，其中內(nèi)存又是重中之重，因?yàn)镾QL Server為了性能要求，會(huì)將它所要訪問的數(shù)據(jù)全部（只要內(nèi)存足夠）放到緩存中。這篇就來介紹SQL Server的內(nèi)存管理體系。

　　SQL Server作為Windows上運(yùn)行的應(yīng)用程序，必須接受Windows的資源管理，利用Windows的API來申請(qǐng)和調(diào)度各類資源。但是，由于Windows的資源管理體系，是為了滿足大多數(shù)的應(yīng)用程序所設(shè)計(jì)的，這對(duì)于SQL Server這種定位于企業(yè)級(jí)、支持多用戶和高并發(fā)性的數(shù)據(jù)庫應(yīng)用程序來說不是很適合，為此SQL Server開發(fā)了自己的一套資源管理體系SQLOS(SQL操作系統(tǒng))。也就是說SQL Server的資源管理分兩層，第一層是在Windows上，通過Windows的API來申請(qǐng)資源。第二層是在SQL Server上，利用SQLOS來決定如何使用從Windows那里申請(qǐng)來的資源。

　　一、操作系統(tǒng)層面的SQL Server內(nèi)存管理

　　由于SQL server的內(nèi)存是通過Windows的API來申請(qǐng)的，如果Windows自己本身就缺少內(nèi)存，SQL Server由于申請(qǐng)不到內(nèi)存，性能自然受影響。因此做SQL Server的內(nèi)存檢測，第一步就是查看系統(tǒng)層面的內(nèi)存，以確保系統(tǒng)本身不缺內(nèi)存，這一步簡單但是必不可少。這里先介紹Windows的一些內(nèi)存管理理念，然后介紹如何檢查系統(tǒng)的內(nèi)存情況。

　　1、Windows的一些內(nèi)存術(shù)語

　　Virtual Address Space（虛擬地址空間）：應(yīng)用程序能夠申請(qǐng)?jiān)L問的最大地址空間。對(duì)于32位的服務(wù)器，地址尋址空間為2的32次方，也就是4GB，但是這4GB并不是都給SQL Server使用的，默認(rèn)情況下是用戶態(tài)2GB，核心態(tài)2GB，所以說對(duì)于32位的系統(tǒng)SQL Server只有2GB的內(nèi)存可供使用。不過可以通過設(shè)置/3GB boot.int參數(shù)，來調(diào)整系統(tǒng)的配置，使用戶態(tài)為3GB，核心態(tài)為1GB。或者開啟AWE（地址空間擴(kuò)展），將尋址空間擴(kuò)展為64GB，不過該設(shè)置有缺陷，下面會(huì)分析。

　　Physical Memory（物理內(nèi)存）：也就是通常所說的電腦的內(nèi)存大小。

　　Reserved Memory（保留地址）：應(yīng)用程序訪問內(nèi)存的方式之一，先保留（Reserve）一塊內(nèi)存地址空間，留著將來使用（SQL Server中的數(shù)據(jù)頁面使用的內(nèi)存就是通過這個(gè)方式申請(qǐng) 的）。被保留的地址空間，不能被其他程序訪問，不然會(huì)出現(xiàn)訪問越界的報(bào)錯(cuò)提示。

　　Committed Memory（提交內(nèi)存）：將保留（Reserve）的內(nèi)存頁面正式提交（Commit）使用。

　　Shared Memory（共享內(nèi)存）：對(duì)一個(gè)以上進(jìn)程可見的內(nèi)存。

　　Private Bytes（私有內(nèi)存）：某進(jìn)程提交的地址空間中，非共享的部分。

　　Working Set：進(jìn)程的地址空間中存放在物理內(nèi)存中的部分。

　　Page Fault（頁面訪問錯(cuò)誤）：訪問在虛擬地址空間，但不存在于Working Set中會(huì)發(fā)生Page Fault。這個(gè)又分兩種情況，第一種是目標(biāo)頁面在硬盤上，這鐘訪問會(huì)帶來硬盤讀寫，這種稱為Hard Fault。另外一種是目標(biāo)頁面在物理內(nèi)存中，但是不是該進(jìn)程的Working Set下，Windows只需要重新定向一下，成為Soft Fault。由于Soft Hard不帶來硬盤讀寫，對(duì)系統(tǒng)的性能影響很小，因此管理員關(guān)心的是Hard Fault。

　　System Working Set：Windows系統(tǒng)的Working Set。

　　2、Windows的內(nèi)存檢測

　　可以通過Windows的性能監(jiān)視器來檢測Windows的內(nèi)存使用情況，如何使用性能監(jiān)視器，可以看這篇文章《使用“性能監(jiān)視器”監(jiān)視系統(tǒng)性能/運(yùn)行情況》 。在檢測內(nèi)存上，比較重要的計(jì)數(shù)器有下面一些：

　　分析Windows系統(tǒng)的內(nèi)存總體使用情況的計(jì)數(shù)器：

　　Memory：Available MBytes：系統(tǒng)中空閑的物理內(nèi)存數(shù)。

　　Memory：Pages/Sec：由于Hard Page的發(fā)生，每秒鐘從硬盤中讀取或者寫入的頁面數(shù)。該計(jì)數(shù)器等于　　Memory：Pages Input/Sec與Memory：Pages Output/Sec之和。
　　分析Windows系統(tǒng)自身的內(nèi)存使用情況的計(jì)數(shù)器：

　　Memory：Cache Bytes：系統(tǒng)的Working Set，也就是Windows系統(tǒng)使用的物理內(nèi)存數(shù)。

　　對(duì)于每個(gè)進(jìn)程的內(nèi)存使用情況的計(jì)數(shù)器：

　　Process：Private Bytes：進(jìn)程提交的地址空間中非共享的部分。

　　Process：Working Set：進(jìn)程的地址空間中存放在物理內(nèi)存中的那部分。

　　從這些計(jì)數(shù)器中，我們可以看到系統(tǒng)中是否還有空閑內(nèi)存，哪個(gè)進(jìn)程使用的內(nèi)存最多，在發(fā)生問題的時(shí)候是否有內(nèi)存使用量突變等情況。這為接下來分析SQL Server的使用提供一個(gè)前提條件。

　　二、SQL Server內(nèi)部的內(nèi)存管理

　　1、內(nèi)存使用分類

　　按用途分類

　　1）Database cache（數(shù)據(jù)頁面）。SQL Server中的頁面都是以8KB為一個(gè)頁面存儲(chǔ)的。當(dāng)SQL Server需要用到某個(gè)頁面時(shí)，它會(huì)將該頁面讀到內(nèi)存中，使用完后會(huì)緩存在內(nèi)存中。在內(nèi)存沒有壓力的情況下，SQL Server不會(huì)將頁面從內(nèi)存中刪除。如果SQL Server感覺到內(nèi)存的壓力時(shí)，會(huì)將最長時(shí)間沒有使用的頁面從內(nèi)存中刪除來空出內(nèi)存。

　　2）各類Consumer（功能組件）

　　Connection的連接信息

　　General：一組大雜燴。語句的編譯、范式化、每個(gè)鎖數(shù)據(jù)結(jié)構(gòu)、事務(wù)上下文、表格和索引的元數(shù)據(jù)等

Query Plan：語句和存儲(chǔ)過程的執(zhí)行計(jì)劃。和Database cache類似，SQL Server也會(huì)將執(zhí)行計(jì)劃緩存以供將來使用，減少編譯時(shí)間。

　　Optimizer：生成執(zhí)行計(jì)劃的過程中消耗的內(nèi)存。

　　Utilities：像BCP、Log Manager、Backup等比較特殊的操作消耗的內(nèi)存。

　　3）線程內(nèi)存：存放進(jìn)程內(nèi)每個(gè)線程的數(shù)據(jù)結(jié)構(gòu)和相關(guān)信息消耗的內(nèi)存，每個(gè)線程需0.5MB的內(nèi)存。

　　4）第三方代碼消耗的內(nèi)存：SQL Server的進(jìn)程里，會(huì)運(yùn)行一些非SQL Server自身的代碼。例如：用戶定義的CLR或Extended Stored Procedure代碼。

　　按申請(qǐng)方式分類

　　1）預(yù)先Reserve一塊大的內(nèi)存，然后在使用的時(shí)候一塊一塊的Commit。Database Page是按這種方式申請(qǐng)的。

　　2）直接用Commit方式申請(qǐng)的內(nèi)存，成為Stolen方式。除了Database Page之外其他內(nèi)存基本都是按這種方式申請(qǐng)的。

　　按申請(qǐng)內(nèi)存的大小分類

　　1）申請(qǐng)小于等于8KB為一個(gè)單位的內(nèi)存，這些內(nèi)存稱為Buffer Pool

　　2）申請(qǐng)大于8KB為一個(gè)單位的內(nèi)存，這些內(nèi)存稱為Multi-Page（或MemToLeave）

　　SQL Server對(duì)于Database Page都是采用先Reserved后Commit的方式申請(qǐng)的，而數(shù)據(jù)頁都是以8KB為單位進(jìn)行申請(qǐng)的。

　　對(duì)于Consumer中的內(nèi)存申請(qǐng)，一般都是按Stolen方式申請(qǐng)的，且大多數(shù)的執(zhí)行計(jì)劃的大小都是小于8KB的，少數(shù)特別復(fù)雜的存儲(chǔ)過程的執(zhí)行計(jì)劃會(huì)超過8KB，默認(rèn)的連接的數(shù)據(jù)包是4KB，除非客戶端特別設(shè)置了超過8KB（不建議）

　　第三方代碼的內(nèi)存申請(qǐng)一般是按Stolen方式申請(qǐng)的，個(gè)別比如CLR中可能會(huì)用Reserved/Commit的方式申請(qǐng)。

線程的內(nèi)存每個(gè)都以0.5MB的方式申請(qǐng)，自然是放在MemToLeave中。

　　之所以花了這么大篇幅來講SQL Server的內(nèi)存分類，是因?yàn)镾QL Server尤其是32位的SQL Server對(duì)不同種類的內(nèi)存的申請(qǐng)大小是不一樣的，對(duì)Commit、Stolen和MemTOLeave等類型的內(nèi)存是有限制的。因此會(huì)出現(xiàn)系統(tǒng)中還有空閑內(nèi)存，但是SQL Server不會(huì)申請(qǐng)使用的現(xiàn)象。

　　2、各部分內(nèi)存的大小限制

　　1）32位的Windows

　　在SQL Server啟動(dòng)時(shí)，會(huì)預(yù)先分配好MemToLeave區(qū)域的大小。默認(rèn)大小為256MB+256（SQL Server配置的允許最大線程數(shù)）* 0.5MB=384MB，因此Buffer Pool中的最大值為2GB-384MB=1.664G。如果使用了AWE技術(shù)，可以將系統(tǒng)的擴(kuò)展地址空間達(dá)到64GB，但由于AWE擴(kuò)展出來的地址只能用Reserved/Commit方式申請(qǐng)，為此MemToLeave的內(nèi)存還是384MB，Buffer Pool中的Stolen的最大內(nèi)存為1.664G，剩余的內(nèi)存都可以為Database Page頁面使用。

　　2）64位的Windows

　　32位的SQL Server。由于64位的操作系統(tǒng)，核心態(tài)不再占用32位進(jìn)程的虛擬地址空間，因此MemToLeave的大小還是為384MB，Buffer Pool可以達(dá)到3.664G。如果還開啟了AWE，這3.664GB可以全部用于Buffer Pool中的Stolen，剩余的內(nèi)存都可以給Database Page頁面使用。不過這種情況很少見，哪里用64位操作系統(tǒng)的機(jī)器裝32位的哦-_- 。

　　64位的SQL Server。所有的內(nèi)存都無限申請(qǐng)的，有需要就申請(qǐng)。

　　3、SQL Server內(nèi)存使用情況的分析

　　一般來說有兩種方式，第一種就是用來分析系統(tǒng)內(nèi)存情況時(shí)使用的用性能計(jì)數(shù)器來分析，第二種是使用動(dòng)態(tài)管理視圖（DMV，只適用于SQL Server2005和2008）

　　1）SQL Server性能計(jì)數(shù)器

　　SQLServer：Memory Manager：Total Server Memory（KB)：SQL Server緩沖區(qū)提交的內(nèi)存。不是SQL Server總的使用內(nèi)存，只是Buffer Pool中的大小。

　　SQLServer：Memory Manager：Target Server Memory（KB）：服務(wù)器可供SQL Server使用的內(nèi)存量。一般是由SQL Server能訪問到的內(nèi)存量和SQL Server的sp_Configure配置中的Max Server Memory值中的較小值算得。

　　SQLServer：Memory Manger：Memory Grants Pending：等待內(nèi)存授權(quán)的進(jìn)程總數(shù)。如果該值不為0，說明當(dāng)前有用戶的內(nèi)存申請(qǐng)由于內(nèi)存壓力被延遲，這意味著比較嚴(yán)重的內(nèi)存瓶頸。

　　SQLServer：Buffer Manager：Buffer Cache Hit Ratio：數(shù)據(jù)從緩沖區(qū)中找到而不需要從硬盤中去取的百分比。SQL Server在運(yùn)行一段時(shí)間后，該比率的變化應(yīng)該很小，而且都應(yīng)該在98%以上，如果在95%以下，說明有內(nèi)存不足的問題。

　　SQLServer：Buffer Manager：Lazy Writes/Sec：每秒鐘被惰性編輯器（Lazy writer）寫入的緩沖數(shù)。當(dāng)SQL Server感覺到內(nèi)存壓力的時(shí)候，會(huì)將最久沒有使用的數(shù)據(jù)頁面和執(zhí)行計(jì)劃從緩沖池中清理掉，做這個(gè)動(dòng)作的就是Lazy Writer。
　　Page Life Expectancy：頁面不被引用后，在緩沖池中停留的秒數(shù)。在內(nèi)存沒有壓力的情況下，頁面會(huì)一直待在緩沖池中，Page Life Expectancy會(huì)維持在一個(gè)比較高的值，如果有內(nèi)存壓力時(shí)，Page Life Expectancy會(huì)下降。所以如果Page Life Expectancy不能維持在一個(gè)值上，就代表SQLServer有內(nèi)存瓶頸。
　　SQLServer：Buffer Manager：Database Pages ：就是Database Cache的大小。
　　SQLServer：Buffer Manager：Free Pages：SQL Server中空閑可用的大小。

　　SQLServer：Buffer Manager：Stolen Pages：Buffer Pool中Stolen的大小。

　　SQLServer：Buffer Manager：Total Pages：Buffer Pool的總大小（等于Database Pages+Free Pages+Stolen Pages）。該值乘以8KB，應(yīng)該等于Memory Manager：Total Server Memory的值。

　　從上面這些計(jì)數(shù)器中我們就能了解SQL Server的內(nèi)存使用情況，結(jié)合前面說的系統(tǒng)層的計(jì)數(shù)器大概能看出是否存在內(nèi)存瓶頸。

　　2）內(nèi)存動(dòng)態(tài)管理視圖

　　在SQL Server 2005以后，SQL Server的內(nèi)存管理是使用Memory Clerk的方式統(tǒng)一管理。所有的SQL Server的內(nèi)存的申請(qǐng)或釋放，都需要通過它們的Clerk，SQL Server也通過這些Clerk的協(xié)調(diào)來滿足不同需求。通過查詢這些DMV，可以得到比用性能計(jì)數(shù)器更加詳細(xì)的內(nèi)存使用情況。

　　我們可以通過下面的查詢語句來檢測SQL Server的Clerk的內(nèi)存使用情況。

　　使用sys.dm_os_memory_clerks查看內(nèi)存使用情況

   1. SELECT type, --Clerk的類型  
   2.     sum(virtual_memory_reserved_kb) as vm_Reserved_kb, -- 保留的內(nèi)存  
   3.     sum(virtual_memory_committed_kb) as vm_Committed_kb, --提交的內(nèi)存  
   4.     sum(awe_allocated_kb) as awe_Allocated_kb, -- 開啟AWE后使用的內(nèi)存  
   5.     sum(shared_memory_reserved_kb) as sm_Reserved_kb, -- 共享的保留內(nèi)存  
   6.     sum(shared_memory_committed_kb) as sm_Committed_kb, -- 共享的提交內(nèi)存  
   7.     sum(single_pages_kb) as SinlgePage_kb, -- Buffer Pool中的Stolen的內(nèi)存  
   8.     sum(multi_pages_kb) as MultiPage_kb -- MemToLeave的內(nèi)存  
   9. FROM sys.dm_os_memory_clerks   
  10. GROUP BY type  
  11. ORDER BY type         

        

        
        
      
        
it知識(shí)庫：SQL Server性能調(diào)優(yōu)：資源管理之內(nèi)存管理篇（上），轉(zhuǎn)載需保留來源！
        
鄭重聲明：本文版權(quán)歸原作者所有，轉(zhuǎn)載文章僅為傳播更多信息之目的，如作者信息標(biāo)記有誤，請(qǐng)第一時(shí)間聯(lián)系我們修改或刪除，多謝。