SQLSERVER內(nèi)存分配及常見內(nèi)存問題（1）簡(jiǎn)介

轉(zhuǎn)自：http://blog.csdn.net/dba_huangzj/article/details/7527543 一、 問題 ： 1、SQL Server 所占用 內(nèi)存 數(shù)量從啟動(dòng)以后就不斷地增加： 首先，作為成熟的產(chǎn)品， 內(nèi)存 溢出的機(jī)會(huì)微乎其微。對(duì)此要了解SQL SERVER與windows是如何協(xié)調(diào)、共享 內(nèi)存 。并且SQL

轉(zhuǎn)自：http://blog.csdn.net/dba_huangzj/article/details/7527543

一、問題：

1、SQL Server 所占用內(nèi)存數(shù)量從啟動(dòng)以后就不斷地增加：

首先，作為成熟的產(chǎn)品，內(nèi)存溢出的機(jī)會(huì)微乎其微。對(duì)此要了解SQL SERVER與windows是如何協(xié)調(diào)、共享內(nèi)存。并且SQL SERVER的內(nèi)部對(duì)內(nèi)存的管理機(jī)制。

2、在Windows 2003以上版本運(yùn)行的SQL Server，內(nèi)存使用量突然急劇下降：

內(nèi)存是Sqlserver的生命線。在errorlog中，出現(xiàn)一下情況：

這類問題往往不是sql server導(dǎo)致的，而是Windows感覺到急迫的內(nèi)存壓力，迫使sql server 釋放內(nèi)存。

3、用戶在做操作時(shí)，遇到內(nèi)存申請(qǐng)失?。翰皇怯脩粝肷暾?qǐng)多少就有多少

4、內(nèi)存壓力導(dǎo)致的性能下降：內(nèi)存壓力是性能問題最常見的原因之一。

二、操作系統(tǒng)層面看SQL SERVER 內(nèi)存分配：

操作系統(tǒng)不缺內(nèi)存不代表sql server不缺內(nèi)存

專用術(shù)語：

Virtual Address Space(虛擬地址空間)：一個(gè)應(yīng)用程序能訪問的最大地址空間。虛擬地址空間存放的數(shù)據(jù)信息不一定在物理內(nèi)存中，可能會(huì)放在緩存文件中（Paging file）

Physical Memory（物理內(nèi)存）：要頻繁訪問的數(shù)據(jù)對(duì)象必須能長(zhǎng)時(shí)間地緩存在物理內(nèi)存中，才能達(dá)到最優(yōu)化的效率。

Reserved Memory（保留內(nèi)存）：是虛擬地址空間的一段地址，不是真正物理空間，由某些API提前申請(qǐng)保留。

Committed Memory（提交內(nèi)存）：將保留內(nèi)存的頁(yè)面正式提交。最終到物理內(nèi)存中，

Shared Memory（共享內(nèi)存）：windows提供了在進(jìn)程和操作系統(tǒng)間共享內(nèi)存的機(jī)制。可理解為在多個(gè)進(jìn)程中可見的內(nèi)存。

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

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

Page Fault（頁(yè)面訪問錯(cuò)誤）：分為Soft/Hard ，訪問一個(gè)存在虛擬地址空間，但不存在物理內(nèi)存中的頁(yè)面時(shí)，就會(huì)發(fā)生PageFault，如果不是訪問越界而導(dǎo)致的，一種目標(biāo)頁(yè)面存在于硬盤，會(huì)產(chǎn)生硬盤讀寫，成為Hard Fault。另一種存在物理內(nèi)存中，但沒有直接放在這個(gè)進(jìn)程的working set下。需要windows重定向，這種不會(huì)導(dǎo)致硬盤操作，成為soft fault，一般soft帶來的性能影響可以忽略，只要關(guān)系hard fault。

System Working Set：Windows系統(tǒng)也有Working set 可以通過性能監(jiān)視器里的memory：cache bytes來監(jiān)控，發(fā)生在系統(tǒng)內(nèi)存上的page fault可以用過Mmeory：cache faults/sec看到。

System Cache（系統(tǒng)高速緩存）：提高磁盤I/O速度，可以用過性能監(jiān)視器：Memory：cache resident bytes監(jiān)控。

Non Paged Pool （非頁(yè)交換區(qū)）：包含一定范圍內(nèi)的系統(tǒng)虛擬地址的內(nèi)存交換區(qū)，可以保證在任何時(shí)候都駐留在物理內(nèi)存中，可以通過Memory:pool Nonpaged Bytes來監(jiān)控。這一塊緩存可以被所有的經(jīng)常共享，一個(gè)最常見的用途是存放所有對(duì)象的指針（Object Handles）

Paged pool（頁(yè)交換區(qū)）：系統(tǒng)空間中可以調(diào)入或調(diào)出系統(tǒng)進(jìn)程工作集（Working set）的虛擬內(nèi)存區(qū)域?？梢酝ㄟ^Memory：pool Paged Bytes和Memory：pool paged resident bytes監(jiān)控。

Stack（棧）：每個(gè)線程有兩個(gè)棧，一個(gè)給內(nèi)核模式（kernel mode），一個(gè)給用戶模式（user mode）。每個(gè)棧是一塊內(nèi)存空間，存放線程運(yùn)行的過程或函數(shù)的調(diào)用地址，以及所有參數(shù)的值。

In Process:運(yùn)行在同一個(gè)進(jìn)程的地址空間里。

Out of Process：運(yùn)行在不同的進(jìn)程地址空間里。

Memory Leak（內(nèi)存泄漏）：一種是sql server作為進(jìn)程，不斷地向windows申請(qǐng)內(nèi)存資源知道整個(gè)windows內(nèi)存耗盡。另一種是sql server 內(nèi)部，某個(gè)sql server組件不斷地申請(qǐng)內(nèi)存，知道把能申請(qǐng)到的內(nèi)存耗盡，使得其他組件不能正常運(yùn)行。前一種情況非常少見。

32位下windows的地址空間及AWE：

默認(rèn)情況下，windows進(jìn)程有4G虛擬地址空間，2G給核心態(tài)（kernel mode），剩下的2G給用戶態(tài)（user mode）。這兩部分會(huì)嚴(yán)格區(qū)分。

任何一個(gè)用戶進(jìn)程的地址空間分布圖：

方法1：在boot.ini文件中使用/3GB參數(shù)，可以在企業(yè)版的windows下講核心態(tài)降到1G，講用戶態(tài)升到3G。

方法2：使用Address Windowsing Extensions(地址空間擴(kuò)展，awe)，是一種允許32位應(yīng)用分配64GB物理內(nèi)存，并把視圖或窗口映射到2G虛擬地址空間的機(jī)制。不是所有的內(nèi)存申請(qǐng)都使用awe，只有先reserve，再commit的內(nèi)存調(diào)用才使用awe。

Windows層面上的內(nèi)存使用檢查：

在檢查sql server內(nèi)存使用情況時(shí)，首先要檢查windows層面的內(nèi)存使用情況。

需要檢查的內(nèi)容有：windows系統(tǒng)自身內(nèi)存使用數(shù)量及內(nèi)存分布。是否有內(nèi)存壓力，壓力是否比較嚴(yán)重。甚至每個(gè)進(jìn)程的內(nèi)存使用情況。最后就是是否互相影響。

可以使用性能監(jiān)視器實(shí)現(xiàn)。

Windows系統(tǒng)使用情況：

1、整體使用分析：

Committed bytes：

整個(gè)windows系統(tǒng)的內(nèi)存總數(shù)，包括物理內(nèi)存的數(shù)據(jù)和文件緩存中的數(shù)據(jù)。

Commit Limit：

整個(gè)windows系統(tǒng)能夠申請(qǐng)的最大內(nèi)存數(shù)，等于物理內(nèi)存加上文件緩存的大小。如果Commit Limit≈Committed bytes，說明系統(tǒng)內(nèi)存接近極限。如果緩存文件不能自動(dòng)增長(zhǎng)，系統(tǒng)將不能提供更多的內(nèi)存空間。

Available MBytes：

現(xiàn)在系統(tǒng)空閑的物理內(nèi)存，直接反映windows層面有沒有內(nèi)存壓力。

Page File:%Usage 和Page File:%Peak Usage：

反映緩存文件使用量的多少，數(shù)據(jù)在文件緩存中存得越多，說明物理內(nèi)存數(shù)量和實(shí)際需求量的差距越大，性能也越差。

Pages/sec：

Hard Page Fault每秒需要從磁盤讀取或?qū)懭氲捻?yè)面數(shù)目。是Memory:pages input/sec + Memory:pages output/sec之和。

Memory:page faults/sec 是soft page fault 和 hard page fault的總和。但由于soft page fault 對(duì)性能影響不大，所以用處沒pages/sec那么有用。pages/sec不能長(zhǎng)時(shí)間保持在一個(gè)比較高的值。

2、Windows系統(tǒng)自身內(nèi)存使用情況：

Memory：cache bytes：

系統(tǒng)的working set ，也就是系統(tǒng)使用的物理內(nèi)存數(shù)目。

Memory：System cache resident bytes (system cache)：系統(tǒng)告訴緩存消耗的物理內(nèi)存。

Memory：Pool paged resident bytes：頁(yè)交換區(qū)消耗的物理內(nèi)存。

Memory：System Driver resident bytes：可調(diào)頁(yè)的設(shè)備驅(qū)動(dòng)程序代碼消耗的物理內(nèi)存。

Memory：System Code resident bytes：Ntoskrnl.exe中可調(diào)頁(yè)代碼消耗的內(nèi)存。

3、System pool：Memory :pool Nonpaged bytes(非頁(yè)交換區(qū))和Memory：pool paged resident bytes（頁(yè)交換區(qū)）

單個(gè)Process使用情況：

Process:%processor Time：目標(biāo)進(jìn)程消耗的CPU資源數(shù)，包括用戶態(tài)和核心態(tài)的時(shí)間。

Process:Page Faults/sec 目標(biāo)進(jìn)程上發(fā)生的PageFaults的數(shù)目。

Process:Handle Count 目標(biāo)進(jìn)程handle數(shù)據(jù)，如果進(jìn)程內(nèi)部有對(duì)象老是創(chuàng)建不及時(shí)回收，就會(huì)發(fā)生Thread Leak

Process:Pool Paged Bytes目標(biāo)進(jìn)程所使用的Paged Pool的大小。

Process:Pool Nonpaged Bytes目標(biāo)進(jìn)程所使用的Non-Paged pool大小。

Process:working set 某個(gè)進(jìn)程的地址空間中，存放在物理內(nèi)存的那部分。

Process:Virual Bytes:某個(gè)進(jìn)程所申請(qǐng)的虛擬地址空間大小。

Process:Private bytes：某個(gè)進(jìn)程提交了地址空間中非共享的部分。

內(nèi)存永遠(yuǎn)是最重要的系統(tǒng)資源。

SQL SERVER內(nèi)存使用和Windows之間的關(guān)系：

Sqlserver有兩個(gè)重要的內(nèi)存計(jì)數(shù)器：Total Server Memory 和Target Server Memory。

Total Server Memory：自己分配的Buffer pool 內(nèi)存總和。

Target Server Memory：理論上能夠使用的最多內(nèi)存數(shù)目。

SQL Server 內(nèi)存使用量陡然下降現(xiàn)象：

原因：

1、windows在某種情況下申請(qǐng)了太多內(nèi)核態(tài)內(nèi)存，反而壓縮了用戶態(tài)可以使用的物理內(nèi)存。

2、有些硬件驅(qū)動(dòng)程序申請(qǐng)了太多內(nèi)核態(tài)內(nèi)存，也占用太多物理內(nèi)存。

3、某些應(yīng)用突然申請(qǐng)大量物理內(nèi)存。

使用下面方法避免SQL SERVER 內(nèi)存被急劇搶占：

1、開啟Lock page in memory功能

只有05/08企業(yè)版才有

2、使用sp_configure 設(shè)置sql 的MAX SERVER MEMORY。

3、升級(jí)新版本或者補(bǔ)丁。

4、升級(jí)硬件驅(qū)動(dòng)。

合理配置SQL SERVER 內(nèi)存

兩條原則：

（1）Windows 系統(tǒng)和其他關(guān)鍵應(yīng)用服務(wù)要有足夠的內(nèi)存，不要在運(yùn)行過程中因?yàn)閮?nèi)存不足，而搶SQL SERVER已經(jīng)申請(qǐng)的內(nèi)存。

（2）在滿足第一點(diǎn)的前提下，SQL SERVER使用盡可能多的內(nèi)存，并保證內(nèi)存使用數(shù)量的穩(wěn)定性。

方法：

1、使用64位

2、專用服務(wù)器

3、設(shè)置SQL Server Max Server Memory

4、給SQL Server 啟動(dòng)賬號(hào)賦予Lock Pages in memory權(quán)限。

5、“set working set size”不要使用。

三、SQL Server 內(nèi)部獨(dú)特的內(nèi)存管理模式：

SQL SERVER 開放的內(nèi)存調(diào)節(jié)接口：

1、Min Server Memory (sp_configure)：最終由windows確定，不保證SQL Server使用最小物理內(nèi)存數(shù)。

2、Max Server Memory(sp_configure)：數(shù)據(jù)放在物理內(nèi)存還是緩沖文件中，由windows決定。

3、Set Working Set Size (sp_configure)：不要使用。

4、AWE Enalbed（sp_configure）：對(duì)32位系統(tǒng)有意義。

5、Lock Pages in memory （企業(yè)版會(huì)自動(dòng)開啟）：有一定機(jī)會(huì)確保sql server的物理內(nèi)存數(shù)。

內(nèi)存使用分類：

按用途分類：

Database Cache：存放數(shù)據(jù)頁(yè)的緩沖區(qū)。

各類Consumer：

Connection:：默認(rèn)4K

General：包含語句的編譯、范式化、每個(gè)鎖數(shù)據(jù)結(jié)構(gòu)、事務(wù)上下文、表格和索引的元數(shù)據(jù)等。默認(rèn)8K。

Query Plan：默認(rèn)8k，

Optimizer：默認(rèn)8k，

Utilities：像BCP、Log Manager、Parallel Queries、Backup的特殊操作。默認(rèn)8k，

線程內(nèi)存：進(jìn)程內(nèi)的每個(gè)線程分配0.5MB內(nèi)存。存放線程的數(shù)據(jù)結(jié)構(gòu)和相關(guān)信息。默認(rèn)512K

第三方代碼申請(qǐng)的內(nèi)存（COM,XP...）

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

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com