SQL 記憶體配置設定

一、向作業系統申請記憶體的方式

　　有2種申請方式：

1、reserved/committed pages

　　應用程式調用windows api，預先申請保留（reserve）一塊記憶體空間。reserved memory（保留位址）留着将來使用，且這塊已經保留的空間不能被其他程式通路，不然會出現通路越界的報錯提示。此時隻是保留一段虛拟位址空間（vas）上面的一段位址，尚未真正配置設定實體記憶體空間。

　　在需要使用的時候，再一點一點地将保留（reserve）的記憶體頁面正式送出（commit）給應用程式使用。此時，将正式在實體記憶體中申請一段有效的位址，送出的頁面在通路時最終轉換到實體記憶體中的有效頁面。

　　分兩步來預留和送出記憶體，通過推遲頁面送出來減少實體記憶體的使用。這對于需要潛在、大量和連續記憶體緩沖區的應用程式是很有用的。位址空間可以被保留，在需要的時候再送出，而不是為了整個區域送出頁面。這種技術在sql server中用來緩存資料頁面。

2、stolen pages

　　直接用commit方式從位址空間申請記憶體，稱為stolen方式。這部分記憶體也是正常記憶體，隻是每次需要一點記憶體就直接在實體記憶體中申請一部分頁面，類似于“偷”。

　　sql server中除了database cache是先reserve再commit，其它所有的記憶體使用基本上都是直接commit，都是stolen。

　　通過啟用awe功能可以使32位作業系統通路4gb以上的實體記憶體，但這部分記憶體不可以用于stolen方式。意味着僅database cache可用。

二、sql server的記憶體用途

　　計數器“sqlserver：memory manager：target server memory（kb）”為伺服器可供sql server使用的記憶體量。一般是由sql server能通路到的記憶體量和sql server的配置中的max server memory值中的較小值算得。

　　sql server的記憶體主要配置設定給以下4大部分：

1、database cache

　　sql server中的頁面都是以8kb為一個頁面存儲的。當sql server需要用到某個頁面上存儲的資料時，它會将該整個頁面讀到記憶體中，使用完後會緩存在記憶體中。如果該頁面被修改，變成“髒頁”，就要等待checkpoint或lazy writer集中處理。如果遇到記憶體壓力，sql server就需要釋放一些“舊”的頁面。

　　database page都是采用先reserved後commit的方式申請的。通常這塊緩沖池最大，使用頻率也最高。

2、線程（thread）記憶體

　　存放程序内每個線程的資料結構和相關資訊消耗的記憶體，每個線程需0.5mb的記憶體。

　　線程記憶體申請，一般都是按stolen方式申請的。

3、各類記憶體消費者（consumer）

　　consumer的記憶體申請，一般都是按stolen方式申請的。也就是相當數量的stolen pages都用在這裡。包括：

（1）connection，每一個連接配接的資料結構和相關資訊。

（2）general，語句的編譯、範式化、每個鎖資料結構、事務上下文、表格和索引的中繼資料等。

（3）query plan，語句和存儲過程的執行計劃。和database cache類似，sql server也會将執行計劃緩存以供将來使用，減少編譯時間。

（4）optimizer，生成執行計劃的過程中消耗的記憶體。

（5）utilities，像bcp、log manager、parallel queries、backup等比較特殊的操作消耗的記憶體。

4、第三方代碼消耗的記憶體

　　sql server的程序裡，會運作一些非sql server自身的代碼。例如：

（1）使用者定義的clr。

（2）extended stored procedure代碼。

（3）linked server需要加載的資料連接配接驅動。

（4）調用sql mail功能需要加載的mapi動态庫。

　　第三方代碼的記憶體申請一般是按stolen方式申請的，偶爾有例外。例如，個别clr中可能會用reserved/commit的方式申請。

　　這部分記憶體不是sql server自身代碼申請的，是以sql server并不清楚它們申請了多少記憶體。但是作為一個程序，windows知道sql server申請的所有記憶體，是以也能夠計算這一部分的記憶體使用。

三、緩沖池

1、架構

　　sql server 2000緩沖池的架構如下：

　　根據上圖，sql server的主要記憶體元件是緩沖池（buffer pool），除了資料緩存之外，所有的緩存都使用共同的緩存架構。該架構包括存儲（store）集和資源螢幕。存儲集包括3種類型：緩存存儲、使用者存儲、對象存儲。

　　緩存存儲的主要示例是計劃緩存（過程和查詢計劃緩存）；使用者存儲實際上與使用者無關，它的主要示例是中繼資料緩存，對象緩存的一個使用示例是sni（它使用對象存儲來共用網絡緩沖區）。

注：sql server network interface(簡稱sni)

　　sni是在伺服器和用戶端之間建立網絡連接配接的一種協定，他提供一組在資料庫引擎和sql server用戶端使用的api函數。sni代替了sql server2000下的net- libraries元件和mdac元件。

　　sql server支援共享記憶體(shared memory)、tcp/ip、命名管道(named pipes)、虛拟接口擴充卡(virtual interface adapter，即via)四種協定。不推薦使用via，sql server 2012移除了對via的支援。

　　一旦建立連接配接，sni就會向伺服器的端點（endpoint）建立一條安全的連接配接，用來進行資料的請求和傳回。

2、計數器

（1）計數器“sqlserver：memory manager：target server memory（kb）”表示sql server能夠使用的動态記憶體總量。

（2）計數器“sqlserver：memory manager：total server memory（kb）”表示sql server緩沖區送出的記憶體。不是sql server總的使用記憶體，隻是buffer pool中的大小。

（3）計數器“sqlserver：buffer manager：database pages”表示buffer pool中database cache的大小，即資料庫被加載到記憶體的這部分大小。

（4）計數器“sqlserver：buffer manager：free pages”表示buffer pool中所有的空閑可用的總頁數。

（5）計數器“sqlserver：buffer manager：stolen pages”表示buffer pool中stolen的大小，即用于其他用途，包括過程緩存。

（6）計數器“sqlserver：buffer manager：free pages”表示buffer pool中目前可用的頁數。

（7）計數器“sqlserver：buffer manager：total pages”表示buffer pool的總大小（等于database pages+free pages+stolen pages）。該值乘以8kb，應該等于計數器“memory manager：total server memory”的值。

注：“sqlserver：buffer manager：”計數器組一共包括以下計數器：awe lookup maps/sec、awe stolen maps/sec、awe unmap calls/sec、awe unmap pages/sec、awe write maps/sec、buffer cache hit ratio、checkpoint pages/sec、database pages、free list stalls/sec、free pages、lazy writes/sec、page life expectancy、page lookups/sec、page reads/sec、page writes/sec、readahead pages/sec、reserved pages、stolen pages、target pages、total pages 。

四、記憶體的頁面

　　sql server自己申請的記憶體，有兩種記憶體機關。

1、buffer pool（緩沖池）

　　所有可以分成小于或等于8kb為一個機關的記憶體申請，sql server就配置設定給一個8kb頁面。所有這些頁面都集中管理，這塊記憶體被稱為buffer pool。一次一個頁面的這種配置設定，被稱為single page allocation。

（1）database page都是以8kb為機關進行申請的。

　　也就是說，buffer pool的設計和sql server的資料頁面大小一緻，能夠有效地配置設定和管理sql server程序裡的記憶體。對于sql server來講，大部分的記憶體需求都可以以8kb為機關，放在buffer pool裡。

（2）各類consumer大多數以8kb為機關進行申請。

　　對于connection，與network package size（用戶端和sql server通信的每個資料包的大小）有關系。預設的連接配接的資料包是4kb，是以輸入/輸出緩存會放在buffer pool。一台sql server的預設network package size大小可以由sp_configure控制。但是任何一個用戶端都可以指定它建立的連接配接自己的network package size。

　　對于general，絕大部分記憶體，例如每個鎖的資料結構、事務上下文、表格和索引的中繼資料等，都會以8kb為機關申請。

　　對于query plan、optimizer、utilities等，和general類似，絕大部分記憶體使用都會在buffer pool裡。

2、multi-page（保留記憶體）

　　對于大于8kb為一個機關的記憶體申請，sql server把它們集中在另外一個區域，這塊記憶體被稱為multi-page（以前叫做memtoleave）。這種配置設定被稱為multiple page allocation。這個區域不同于buffer pool，因為在這個區域中配置設定的記憶體很大一部分不受sql server控制。即，隻在sql server内部配置設定，卻由加載在sql server内部的其它dll來配置設定。

（1）線程的記憶體每個都以0.5mb的方式申請，自然是放在memtoleave中。

（2）各類consumer僅少量使用multi-page。

　　對于connection，如果用戶端特别設定了超過8kb或更大，輸入/輸出緩存會放在multi-page裡。

　　對于general，如果偶爾一個語句特别長，它的編譯和範式化需要使用大于8kb為機關的記憶體，這會使用multi-page。

　　對于query plan、optimizer、utilities等，遇到特别長的語句，它的query plan、optimizer等記憶體使用一部分multi-page。

（3）第三方代碼使用multi-page。

　　由于sql server不清楚這些代碼申請的記憶體大小，是以sql server把它們申請的記憶體都放在multi-page裡。

說明：

（1）memtoleave。在32位作業系統，sql server啟動時會嘗試保留我們所配置的memtoleave大小（包含worker thread所需）的地下空間，然後把其它位址空間都給了buffer pool，而剩下的這塊空間就可以由第三方的dll或者其它加載在sql server的内部代碼來使用了。

（2）multi-page。在64位作業系統中，位址空間可以了解為無限大，sql server不再控制multi-page這段空間的大小。是以就不再使用memtoleave這個名詞。

3. large page（大頁面）

　　sql server有可能申請一種large page。由于曆史原因，在windows層面，每一個頁面都是4kb，為了建立起頁式記憶體管理的映射關系，需要有兩級或者三級頁表，而整個轉換過程是需要多次的記憶體通路。從某種程度上來說，這部分的開銷是昂貴的，可以進一步優化。為此，計算機體系結構裡引入了轉換後頁表（tlb）。但是這塊tlb緩存的大小是有限制的，為了能夠通過這段固定大小的緩存來盡可能通路更多的記憶體映射操作獲益，sql server引入large page的記憶體，讓單次映射能夠通路的記憶體數量變大，以期獲得更好的性能。

五、32位作業系統的記憶體限制

　　如果使用了awe技術，可以将系統的擴充位址空間達到64gb，但由于awe擴充出來的位址隻能用reserved/commit方式申請，是以對于sql server來說，awe擴充出來的記憶體主要用于給database pages作為緩存。

　　由于32位的sql server會在啟動的時候配置設定好multi-page的大小，而且比較小，預設是384mb，是以對于32位的sql server比較容易發生multi-page memory的壓力。

　　線程堆棧、記憶體鎖、過程計劃等所需的記憶體都不能在awe中配置設定。sql server 分析服務不能利用awe映射的記憶體。

　　64位的sql server忽略awe選項。

本文出自 “我們一起追過的mssql” 部落格，保留此出處http://jimshu.blog.51cto.com/3171847/590409