sql server中的內(nèi)存基礎(chǔ)知識(shí)有哪些

今天就跟大家聊聊有關(guān)sql server中的內(nèi)存基礎(chǔ)知識(shí)有哪些，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結(jié)了以下內(nèi)容，希望大家根據(jù)這篇文章可以有所收獲。

一. 前言

對(duì)于sql server 這個(gè)產(chǎn)品來說，內(nèi)存這塊是最重要的一個(gè)資源， 當(dāng)我們新建一個(gè)會(huì)話，相同的sql語(yǔ)句查詢第二次查詢時(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)程將無(wú)法為他們找到任何內(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ù)語(yǔ)

　　　3.1 BufferPool　　　　　　

　　　　SQL Server使用BufferPool緩沖池來有效地管理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ū)池中沒有，則使該頁(yè)面從磁盤進(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)存管理器完成的大頁(yè)面分配（大頁(yè)面為任意頁(yè)面＞8 KB）　　　　　　COM對(duì)象

3.2 single-page

這塊內(nèi)存是<=8kb 的存儲(chǔ)，適用于sql server 2008及以前， 屬于buffer pool 緩沖池來分配。有存儲(chǔ)數(shù)據(jù)頁(yè)面，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ù)頁(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 連接：包括輸入緩沖池和輸出緩沖池, 用來存儲(chǔ)用戶指令和返回結(jié)果。

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

Query paln：語(yǔ)句和存儲(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ù)庫(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)系

類型

Database cache

數(shù)據(jù)頁(yè)面緩沖區(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%'

當(dāng)您啟動(dòng)Microsoft SQL Server時(shí)，SQL Server內(nèi)存使用量可能會(huì)繼續(xù)穩(wěn)步增長(zhǎng)，而不是減少，即使服務(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)地增長(zhǎng)和縮小緩沖池（緩存）的大小。只要有足夠的內(nèi)存（4 MB和10 MB）可以防止分頁(yè)，那么SQL Server緩沖池就會(huì)繼續(xù)增長(zhǎng)。當(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ù)庫(kù)引擎使用最小服務(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)存配置。

看完上述內(nèi)容，你們對(duì)sql server中的內(nèi)存基礎(chǔ)知識(shí)有哪些有進(jìn)一步的了解嗎？如果還想了解更多知識(shí)或者相關(guān)內(nèi)容，請(qǐng)關(guān)注億速云行業(yè)資訊頻道，感謝大家的支持。