一. 前言

 對(duì)于sql server 這個(gè)產(chǎn)品來(lái)說(shuō)，內(nèi)存這塊是最重要的一個(gè)資源， 當(dāng)我們新建一個(gè)會(huì)話，相同的sql語(yǔ)句查詢第二次查詢時(shí)間往往會(huì)比第一次快，特別是在sql統(tǒng)計(jì)或大量查詢數(shù)據(jù)輸出時(shí)，會(huì)有這么感覺(jué)。除了第一次要編譯生成執(zhí)行計(jì)劃,  在CPU,I/O 的影響外，最主要的是第二次查詢是從內(nèi)存緩存中讀出，為什么是這樣，sql server 內(nèi)存里存儲(chǔ)了什么，它與windows內(nèi)存又有什么區(qū)別？  參考了一些資料 下面來(lái)試著講講。   

二. 內(nèi)存和硬盤

為什么內(nèi)存是寶貴的，在每個(gè)系統(tǒng)上都是有限的，就像你看到的1 tb的硬盤，但是你通常看到的是50-200 G的內(nèi)存， 物理內(nèi)存的訪問(wèn)速度非常快，不能超過(guò)一定的限制。在內(nèi)存有限的情況下，如果所有的進(jìn)程都使用了有限的內(nèi)存，并且新的進(jìn)程將無(wú)法為他們找到任何內(nèi)存，這就出現(xiàn)了虛擬地址空間的概念（也稱為VAS）。

Virtual Address Space(虛擬地址空間)

是指一個(gè)應(yīng)用程序能夠申請(qǐng)?jiān)L問(wèn)的最大地址空間。32位尋址空間最大是4G，   64位尋址空間最大是8TB。

VAS作為中間的抽象層的,  不是所有的請(qǐng)求都直接映射到物理內(nèi)存,它首先映射到VAS然后映射到物理內(nèi)存。因此，它可以更協(xié)調(diào)的方式管理對(duì)內(nèi)存的請(qǐng)求，而不是讓進(jìn)程去做，如果不是這樣，它很快就會(huì)導(dǎo)致內(nèi)存崩潰。

在Windows操作系統(tǒng)中，VAS 的內(nèi)核進(jìn)程與用戶進(jìn)程之間的劃分是相同的。對(duì)于32位系統(tǒng)，最大的VAS 是4 G的內(nèi)核/ 2 G到應(yīng)用程序的中，在這里，SQL Server是應(yīng)用程序進(jìn)程，當(dāng)我使用word進(jìn)程時(shí)，它意味SQL Server進(jìn)程差不多一樣，將得到2 G的VAS。因此，從理論上講，這意味著任何應(yīng)用程序進(jìn)程在32位上運(yùn)行的都將擁有最大限度的2 G。

三 sql server 內(nèi)存 架構(gòu)

sql server 內(nèi)存管理，在sql server 2012發(fā)生了重大改變，對(duì)內(nèi)存重新實(shí)現(xiàn)了一遍。  先看下版本之間內(nèi)存管理圖的區(qū)別

  　　名詞術(shù)語(yǔ)

　　　3.1 BufferPool　　　　　　

　　　　SQL Server使用BufferPool緩沖池來(lái)有效地管理SQL Server進(jìn)程的內(nèi)存請(qǐng)求。它是SQL Server的最大內(nèi)存消耗者。緩沖區(qū)是內(nèi)存中的一個(gè)8 KB的頁(yè)面，與數(shù)據(jù)或索引頁(yè)面大小相同，您可以將緩沖區(qū)看作是一個(gè)框架，它在從磁盤到內(nèi)存的時(shí)候保存數(shù)據(jù)和索引頁(yè)。

　　　　SQL Server緩沖區(qū)管理器管理將數(shù)據(jù)頁(yè)讀入緩沖池的任務(wù)，并將其寫入磁盤。它是SQL Server的預(yù)留內(nèi)存存儲(chǔ)，如果您不為它設(shè)置值，它將占用盡可能多的內(nèi)存。因此，在spconfigure中為max server內(nèi)存設(shè)置最佳值總是被推薦為一種良好的實(shí)踐。緩沖池只將內(nèi)存分配給需要少于8 KB頁(yè)面的請(qǐng)求。

　　　　對(duì)于大于8 KB內(nèi)存的所有請(qǐng)求，都是由windows API直接分配的。所有緩存存儲(chǔ)計(jì)劃、數(shù)據(jù)和索引頁(yè)都存儲(chǔ)在這個(gè)緩沖池中。當(dāng)用戶請(qǐng)求row/rows時(shí)，如果緩沖區(qū)池中沒(méi)有，則使該頁(yè)面從磁盤進(jìn)入內(nèi)存。這種輸入/輸出可能在繁忙的系統(tǒng)上特別昂貴，因此盡可能減少SQL服務(wù)器緩存的大小，這可能會(huì)被用戶看作是內(nèi)存泄漏或SQL Server占用大量?jī)?nèi)存，但實(shí)際上它提高了性能，實(shí)際上這個(gè)特性是通過(guò)設(shè)計(jì)實(shí)現(xiàn)的。

　　　　下面這些內(nèi)存不是來(lái)自緩沖池：
　　　　　　SQL LCR
　　　　　　擴(kuò)展存儲(chǔ)過(guò)程
　　　　　　鏈接服務(wù)器分配的內(nèi)存
　　　　　　內(nèi)存管理器完成的大頁(yè)面分配（大頁(yè)面為任意頁(yè)面＞8 KB）
　　　　　　COM對(duì)象

         3.2   single-page

                     這塊內(nèi)存是<=8kb 的存儲(chǔ)，適用于sql server 2008及以前， 屬于buffer pool 緩沖池來(lái)分配。有存儲(chǔ)數(shù)據(jù)頁(yè)面，Consumer功能組件。

         3.3 multi- page

                     這塊內(nèi)存是>8kb的 存儲(chǔ)，適用于sql server 2008及以前， 不屬于buffer pool 緩沖池來(lái)分配,  有存儲(chǔ)Consumer功能組件, 第三方代碼, Threads線程。

    　 3.4  any size page

                      這個(gè)適用于sql server 2012及以上，整合了single-page,multi-page 統(tǒng)稱pages。

四. sql server 2008 內(nèi)存

從內(nèi)存圖我們可以看到有 page reservation  需預(yù)先申請(qǐng)的內(nèi)存， 有momory objects 從windows api申請(qǐng)的內(nèi)存，  有clr第三方申請(qǐng)的內(nèi)存。

內(nèi)存的分類方式有很多，下面介紹三種方式：

　　1. 按用途分類

                 1.1 Database Cache(數(shù)據(jù)頁(yè)面緩沖區(qū))

                          當(dāng)用戶修改了某個(gè)頁(yè)面上的數(shù)據(jù)時(shí)，sql server會(huì)在頁(yè)存中將這個(gè)頁(yè)修改。但不會(huì)立刻將這個(gè)頁(yè)面寫回硬盤，而是等后面的checkpoint 或lazy write集中處理。

                 1.2 各類Consumer功能組件

                            Connection 連接：包括輸入緩沖池和輸出緩沖池, 用來(lái)存儲(chǔ)用戶指令和返回結(jié)果。

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

                            Query paln：語(yǔ)句和存儲(chǔ)過(guò)程的執(zhí)行計(jì)劃。

                            Optimizer：sql server在生成執(zhí)行計(jì)劃的過(guò)程中需要消耗的內(nèi)存。

                            Utilities：像BCP, Log Manager,Parallel Queries,Backup

                 1.3    線程內(nèi)存

                             為每個(gè)線程分配0.5MB的內(nèi)存

                 1.4    第三方代碼申請(qǐng)的內(nèi)存

　　　　　　　　如用戶定義的CLR,Linked Server分布式查詢從遠(yuǎn)程數(shù)據(jù)庫(kù)取回大量數(shù)據(jù)。

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

 　　　  申請(qǐng)方式是指要先預(yù)先Reserve一塊大的內(nèi)存，然后再一小塊一小塊的commit。對(duì)Database Cache是會(huì)先Reserve，再commit。

　　　　其他所有內(nèi)存使用，基本都是直接commit，都叫Stolen。

　　3. 按申請(qǐng)大小分類（上面的內(nèi)存圖就是這種分類）

　　　　有二種內(nèi)存申請(qǐng)單位:  一種是小于或等于8KB的，稱為Buffer Pool，一次一個(gè)頁(yè)面的這種分配，被稱為single page allocation.

 　　　  一種是大于8kb的，稱為Multi-page（以前叫MemToLeave）,這種分配，被稱為 Multiple Page Allocation.

　　　　注意這里的很大一部分內(nèi)存不受 sql server本身控制.因?yàn)榈谌酱a申請(qǐng)的內(nèi)存都放在Multi-page里.

 　內(nèi)存分類方法之間的關(guān)系

五.sql server 2012 內(nèi)存

在 sql server 2012里，single page  allocator 和multi page allocator 統(tǒng)一起來(lái)了，叫做any size page allocator。max server memory 不再像以前的版本那樣，只控制buffer pool的大小，也包括那些大于8kb 的內(nèi)存請(qǐng)求。也就是max server memory 能夠更準(zhǔn)確地控制SQL Server 的內(nèi)存使用了。

　　如下圖所示：

         使用dmv 來(lái)查看當(dāng)前實(shí)例的總內(nèi)存空間，以及占用內(nèi)存空間

　　　　–Target Server Memory (KB)最多能申請(qǐng)的內(nèi)存量
　　　　–Total Server Memory (KB) 目前使用了多少內(nèi)存量

         從下面的空間占用也可以看出來(lái)， 給sql server有分配多少內(nèi)存， 它就會(huì)占用多少內(nèi)存，以達(dá)到性能的最優(yōu)。

select counter_name, ltrim(cntr_value*1.0/1024.0/1024.0)+’G’
as memoryGB from master.sys.dm_os_performance_counters
where counter_name like ‘%target%server%memory%’or counter_name like ‘%total%memory%’