一. 前言
對(duì)于sql server 這個(gè)產(chǎn)品來說，內(nèi)存這塊是最重要的一個(gè)資源， 當(dāng)我們新建一個(gè)會(huì)話，相同的sql語句查詢第二次查詢時(shí)間往往會(huì)比第一次快，特別是在sql統(tǒng)計(jì)或大量查詢數(shù)據(jù)輸出時(shí)，會(huì)有這么感覺。除了第一次要編譯生成執(zhí)行計(jì)劃, 在CPU,I/O 的影響外，最主要的是第二次查詢是從內(nèi)存緩存中讀出，為什么是這樣，sql server 內(nèi)存里存儲(chǔ)了什么，它與windows內(nèi)存又有什么區(qū)別？ 參考了一些資料 下面來試著講講。
二. 內(nèi)存和硬盤
為什么內(nèi)存是寶貴的，在每個(gè)系統(tǒng)上都是有限的，就像你看到的1 tb的硬盤，但是你通?？吹降氖?0-200 G的內(nèi)存， 物理內(nèi)存的訪問速度非?？欤荒艹^一定的限制。在內(nèi)存有限的情況下，如果所有的進(jìn)程都使用了有限的內(nèi)存，并且新的進(jìn)程將無法為他們找到任何內(nèi)存，這就出現(xiàn)了虛擬地址空間的概念（也稱為VAS）。
Virtual Address Space(虛擬地址空間)
是指一個(gè)應(yīng)用程序能夠申請(qǐng)?jiān)L問的最大地址空間。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ù)語
3.1 BufferPool
SQL Server使用BufferPool緩沖池來有效地管理SQL Server進(jìn)程的內(nèi)存請(qǐng)求。它是SQL Server的最大內(nèi)存消耗者。緩沖區(qū)是內(nèi)存中的一個(gè)8 KB的頁面，與數(shù)據(jù)或索引頁面大小相同，您可以將緩沖區(qū)看作是一個(gè)框架，它在從磁盤到內(nèi)存的時(shí)候保存數(shù)據(jù)和索引頁。
SQL Server緩沖區(qū)管理器管理將數(shù)據(jù)頁讀入緩沖池的任務(wù)，并將其寫入磁盤。它是SQL Server的預(yù)留內(nèi)存存儲(chǔ)，如果您不為它設(shè)置值，它將占用盡可能多的內(nèi)存。因此，在spconfigure中為max server內(nèi)存設(shè)置最佳值總是被推薦為一種良好的實(shí)踐。緩沖池只將內(nèi)存分配給需要少于8 KB頁面的請(qǐng)求。
對(duì)于大于8 KB內(nèi)存的所有請(qǐng)求，都是由windows API直接分配的。所有緩存存儲(chǔ)計(jì)劃、數(shù)據(jù)和索引頁都存儲(chǔ)在這個(gè)緩沖池中。當(dāng)用戶請(qǐng)求row/rows時(shí)，如果緩沖區(qū)池中沒有，則使該頁面從磁盤進(jìn)入內(nèi)存。這種輸入/輸出可能在繁忙的系統(tǒng)上特別昂貴，因此盡可能減少SQL服務(wù)器緩存的大小，這可能會(huì)被用戶看作是內(nèi)存泄漏或SQL Server占用大量?jī)?nèi)存，但實(shí)際上它提高了性能，實(shí)際上這個(gè)特性是通過設(shè)計(jì)實(shí)現(xiàn)的。
下面這些內(nèi)存不是來自緩沖池：
SQL LCR
擴(kuò)展存儲(chǔ)過程
鏈接服務(wù)器分配的內(nèi)存
內(nèi)存管理器完成的大頁面分配（大頁面為任意頁面＞8 KB）
COM對(duì)象
3.2 single-page
這塊內(nèi)存是=8kb 的存儲(chǔ)，適用于sql server 2008及以前， 屬于buffer pool 緩沖池來分配。有存儲(chǔ)數(shù)據(jù)頁面，Consumer功能組件。
3.3 multi- page
這塊內(nèi)存是>8kb的 存儲(chǔ)，適用于sql server 2008及以前， 不屬于buffer pool 緩沖池來分配, 有存儲(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ù)頁面緩沖區(qū))
當(dāng)用戶修改了某個(gè)頁面上的數(shù)據(jù)時(shí)，sql server會(huì)在頁存中將這個(gè)頁修改。但不會(huì)立刻將這個(gè)頁面寫回硬盤，而是等后面的checkpoint 或lazy write集中處理。
1.2 各類Consumer功能組件
Connection 連接：包括輸入緩沖池和輸出緩沖池, 用來存儲(chǔ)用戶指令和返回結(jié)果。
General ：一組大雜燴: 語句，語句編譯，范式化，鎖數(shù)據(jù)結(jié)構(gòu)，事務(wù)上下文，表格，索引的元數(shù)據(jù)等。
Query paln：語句和存儲(chǔ)過程的執(zhí)行計(jì)劃。
Optimizer：sql server在生成執(zhí)行計(jì)劃的過程中需要消耗的內(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ù)庫取回大量數(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è)頁面的這種分配，被稱為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)系
|
類型
|
Database cache
數(shù)據(jù)頁面緩沖區(qū)
|
Consumer
功能組件
|
3 Party code
第三方代碼
|
Threads
線程
|
|
Reserved/Commit
|
是
|
一般不是
|
一般不是
|
不是
|
|
Stolen
|
不是
|
是
|
是
|
是
|
|
Buffer Pool
(single- page)
|
所有
|
絕大部分
|
沒有
|
沒有
|
|
MemToLeave
(Multi -page)
|
沒有
|
一小部分
|
所有
|
所有
|
五.sql server 2012 內(nèi)存
在 sql server 2012里，single page allocator 和multi page allocator 統(tǒng)一起來了，叫做any size page allocator。max server memory 不再像以前的版本那樣，只控制buffer pool的大小，也包括那些大于8kb 的內(nèi)存請(qǐng)求。也就是max server memory 能夠更準(zhǔn)確地控制SQL Server 的內(nèi)存使用了。
如下圖所示：
使用dmv 來查看當(dāng)前實(shí)例的總內(nèi)存空間，以及占用內(nèi)存空間
--Target Server Memory (KB)最多能申請(qǐng)的內(nèi)存量
--Total Server Memory (KB) 目前使用了多少內(nè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%'
六 總結(jié)
當(dāng)您啟動(dòng)Microsoft SQL Server時(shí)，SQL Server內(nèi)存使用量可能會(huì)繼續(xù)穩(wěn)步增長，而不是減少，即使服務(wù)器上的活動(dòng)很低。此外，任務(wù)管理器和性能監(jiān)視器可能顯示，計(jì)算機(jī)上可用的物理內(nèi)存會(huì)逐漸減少，直到可用內(nèi)存在4 MB到10 MB之間。這種行為本身并不表示內(nèi)存泄漏。這種行為是典型的，并且是SQL Server緩沖池的預(yù)期行為。
默認(rèn)情況下，SQL Server根據(jù)操作系統(tǒng)報(bào)告的物理內(nèi)存負(fù)載動(dòng)態(tài)地增長和縮小緩沖池（緩存）的大小。只要有足夠的內(nèi)存（4 MB和10 MB）可以防止分頁，那么SQL Server緩沖池就會(huì)繼續(xù)增長。當(dāng)與SQL Server在同一臺(tái)計(jì)算機(jī)上分配內(nèi)存時(shí)，SQL Server緩沖管理器將根據(jù)需要釋放內(nèi)存。SQL Server可以每秒釋放數(shù)兆字節(jié)的內(nèi)存。這允許SQL Server快速地適應(yīng)內(nèi)存分配更改。
您可以為SQL Server數(shù)據(jù)庫引擎使用最小服務(wù)器內(nèi)存和最大服務(wù)器內(nèi)存配置選項(xiàng)使用多少內(nèi)存（緩沖池）設(shè)置上限和下限
請(qǐng)注意，通過上圖設(shè)置內(nèi)存最大 max 只限制SQL Server緩沖池的大小。不限制SQL Server為其他組件分配的剩余未保留內(nèi)存區(qū)域，如擴(kuò)展存儲(chǔ)過程、COM對(duì)象、非共享dll、EXEs和MAPI組件。由于之前的分配，SQL Server私有字節(jié)的數(shù)量超過了最大服務(wù)器內(nèi)存配置。
后面章節(jié)在詳細(xì)介紹內(nèi)存的查看分析
好了，以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，如果有疑問大家可以留言交流，謝謝大家對(duì)腳本之家的支持。
參考文獻(xiàn)：
- SQL Server Memory and Troubleshooting
- Microsoft SQL Server企業(yè)級(jí)平臺(tái)管理實(shí)踐
- SQL Server 2012 內(nèi)存管理 (memory management) 改進(jìn)
您可能感興趣的文章:- SQL語句實(shí)現(xiàn)查詢SQL Server內(nèi)存使用狀況
- 優(yōu)化SQL Server的內(nèi)存占用之執(zhí)行緩存
- SQL Server 數(shù)據(jù)頁緩沖區(qū)的內(nèi)存瓶頸分析
- SqlServer如何通過SQL語句獲取處理器(CPU)、內(nèi)存（Memory）、磁盤（Disk）以及操作系統(tǒng)相關(guān)信息
- SQL Server 2008 R2占用cpu、內(nèi)存越來越大的兩種解決方法
- 解決SQL Server虛擬內(nèi)存不足情況
- 揭秘SQL Server 2014有哪些新特性(1)-內(nèi)存數(shù)據(jù)庫
- 淺談SQL Server 對(duì)于內(nèi)存的管理[圖文]
- SQL Server內(nèi)存遭遇操作系統(tǒng)進(jìn)程壓榨案例分析
- SQL Server在AlwaysOn中使用內(nèi)存表的“踩坑”記錄
