SQL SERVER 内存分配及常见内存问题 简介

时间:2022-11-30 12:47:01

一、问题:

1、SQL Server 所占用内存数量从启动以后就不断地增加:

首先,作为成熟的产品,内存溢出的机会微乎其微。对此要了解SQL SERVER与windows是如何协调、共享内存。并且SQL SERVER的内部对内存的管理机制。

2、在Windows 2003以上版本运行的SQL Server,内存使用量突然急剧下降:

内存是Sqlserver的生命线。在errorlog中,出现一下情况:      SQL SERVER 内存分配及常见内存问题 简介

这类问题往往不是sql server导致的,而是Windows感觉到急迫的内存压力,迫使sql server 释放内存。

3、用户在做操作时,遇到内存申请失败:不是用户想申请多少就有多少

4、内存压力导致的性能下降:内存压力是性能问题最常见的原因之一。

二、操作系统层面看SQL SERVER 内存分配:

操作系统不缺内存不代表sql server不缺内存

专用术语:

Virtual Address Space(虚拟地址空间):一个应用程序能访问的最大地址空间。虚拟地址空间存放的数据信息不一定在物理内存中,可能会放在缓存文件中(Paging file)

Physical Memory(物理内存):要频繁访问的数据对象必须能长时间地缓存在物理内存中,才能达到最优化的效率。

Reserved Memory(保留内存):是虚拟地址空间的一段地址,不是真正物理空间,由某些API提前申请保留。

Committed Memory(提交内存):将保留内存的页面正式提交。最终到物理内存中,

Shared Memory(共享内存):windows提供了在进程和操作系统间共享内存的机制。可理解为在多个进程中可见的内存。

Private Bytes:某个进程提交的地址空间中,非共享部分。

Working Set:某个进程的地址空间中,存放在物理内存的那一部分。

Page Fault(页面访问错误):分为Soft/Hard ,访问一个存在虚拟地址空间,但不存在物理内存中的页面时,就会发生PageFault,如果不是访问越界而导致的,一种目标页面存在于硬盘,会产生硬盘读写,成为Hard Fault。另一种存在物理内存中,但没有直接放在这个进程的working set下。需要windows重定向,这种不会导致硬盘操作,成为soft fault,一般soft带来的性能影响可以忽略,只要关系hard fault。

System Working Set:Windows系统也有Working set 可以通过性能监视器里的memory:cache bytes来监控,发生在系统内存上的page fault可以用过Mmeory:cache faults/sec看到。

System Cache(系统高速缓存):提高磁盘I/O速度,可以用过性能监视器:Memory:cache resident bytes监控。

Non Paged Pool (非页交换区):包含一定范围内的系统虚拟地址的内存交换区,可以保证在任何时候都驻留在物理内存中,可以通过Memory:pool Nonpaged Bytes来监控。这一块缓存可以被所有的经常共享,一个最常见的用途是存放所有对象的指针(Object Handles)

Paged pool(页交换区):系统空间中可以调入或调出系统进程工作集(Working set)的虚拟内存区域。可以通过Memory:pool Paged Bytes和Memory:pool paged resident bytes监控。

Stack(栈):每个线程有两个栈,一个给内核模式(kernel mode),一个给用户模式(user mode)。每个栈是一块内存空间,存放线程运行的过程或函数的调用地址,以及所有参数的值。

In Process:运行在同一个进程的地址空间里。

Out of Process:运行在不同的进程地址空间里。

Memory Leak(内存泄漏):一种是sql server作为进程,不断地向windows申请内存资源知道整个windows内存耗尽。另一种是sql server 内部,某个sql server组件不断地申请内存,知道把能申请到的内存耗尽,使得其他组件不能正常运行。前一种情况非常少见。

32位下windows的地址空间及AWE:

默认情况下,windows进程有4G虚拟地址空间,2G给核心态(kernel mode),剩下的2G给用户态(user mode)。这两部分会严格区分。

任何一个用户进程的地址空间分布图:

SQL SERVER 内存分配及常见内存问题 简介

方法1:在boot.ini文件中使用/3GB参数,可以在企业版的windows下讲核心态降到1G,讲用户态升到3G。

SQL SERVER 内存分配及常见内存问题 简介

方法2:使用Address Windowsing Extensions(地址空间扩展,awe),是一种允许32位应用分配64GB物理内存,并把视图或窗口映射到2G虚拟地址空间的机制。不是所有的内存申请都使用awe,只有先reserve,再commit的内存调用才使用awe。

Windows层面上的内存使用检查:

在检查sql server内存使用情况时,首先要检查windows层面的内存使用情况。

需要检查的内容有:windows系统自身内存使用数量及内存分布。是否有内存压力,压力是否比较严重。甚至每个进程的内存使用情况。最后就是是否互相影响。

可以使用性能监视器实现。

Windows系统使用情况:

1、整体使用分析:

Committed bytes:

整个windows系统的内存总数,包括物理内存的数据和文件缓存中的数据。

Commit Limit:

整个windows系统能够申请的最大内存数,等于物理内存加上文件缓存的大小。如果Commit Limit≈Committed bytes,说明系统内存接近极限。如果缓存文件不能自动增长,系统将不能提供更多的内存空间。

Available MBytes:

现在系统空闲的物理内存,直接反映windows层面有没有内存压力。

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

反映缓存文件使用量的多少,数据在文件缓存中存得越多,说明物理内存数量和实际需求量的差距越大,性能也越差。

Pages/sec:

Hard Page Fault每秒需要从磁盘读取或写入的页面数目。是Memory:pages input/sec + Memory:pages output/sec之和。

Memory:page faults/sec 是soft page fault 和 hard page fault的总和。但由于soft page fault 对性能影响不大,所以用处没pages/sec那么有用。pages/sec不能长时间保持在一个比较高的值。

2、Windows系统自身内存使用情况:

Memory:cache bytes:

系统的working set ,也就是系统使用的物理内存数目。

Memory:System cache resident bytes (system cache):系统告诉缓存消耗的物理内存。

Memory:Pool paged resident bytes:页交换区消耗的物理内存。

Memory:System Driver resident bytes:可调页的设备驱动程序代码消耗的物理内存。

Memory:System Code resident bytes:Ntoskrnl.exe中可调页代码消耗的内存。

3、System pool:Memory :pool Nonpaged bytes(非页交换区)和Memory:pool paged resident bytes(页交换区)

单个Process使用情况:

Process:%processor Time:目标进程消耗的CPU资源数,包括用户态和核心态的时间。

Process:Page Faults/sec 目标进程上发生的PageFaults的数目。

Process:Handle Count 目标进程handle数据,如果进程内部有对象老是创建不及时回收,就会发生Thread Leak

Process:Pool Paged Bytes目标进程所使用的Paged Pool的大小。

Process:Pool Nonpaged Bytes目标进程所使用的Non-Paged pool大小。

Process:working set 某个进程的地址空间中,存放在物理内存的那部分。

Process:Virual Bytes:某个进程所申请的虚拟地址空间大小。

Process:Private bytes:某个进程提交了地址空间中非共享的部分。

内存永远是最重要的系统资源。

SQL SERVER内存使用和Windows之间的关系:

Sqlserver有两个重要的内存计数器:Total Server Memory 和Target Server Memory。
Total Server Memory:自己分配的Buffer pool 内存总和。

Target Server Memory:理论上能够使用的最多内存数目。

SQL Server 内存使用量陡然下降现象:

原因:
1、windows在某种情况下申请了太多内核态内存,反而压缩了用户态可以使用的物理内存。
2、有些硬件驱动程序申请了太多内核态内存,也占用太多物理内存。
3、某些应用突然申请大量物理内存。
使用下面方法避免SQL SERVER 内存被急剧抢占:
1、开启Lock page in memory功能
只有05/08企业版才有
2、使用sp_configure 设置sql 的MAX SERVER MEMORY。
3、升级新版本或者补丁。
4、升级硬件驱动。

合理配置SQL SERVER 内存

两条原则:
(1)Windows 系统和其他关键应用服务要有足够的内存,不要在运行过程中因为内存不足,而抢SQL SERVER已经申请的内存。
(2)在满足第一点的前提下,SQL SERVER使用尽可能多的内存,并保证内存使用数量的稳定性。
方法:
1、使用64位
2、专用服务器
3、设置SQL Server Max Server Memory
4、给SQL Server 启动账号赋予Lock Pages in memory权限。
5、“set working set size”不要使用。

三、SQL Server 内部独特的内存管理模式:

SQL SERVER 开放的内存调节接口:

1、Min Server Memory (sp_configure):最终由windows确定,不保证SQL Server使用最小物理内存数。
2、Max Server Memory(sp_configure):数据放在物理内存还是缓冲文件中,由windows决定。
3、Set Working Set Size (sp_configure):不要使用。
4、AWE Enalbed(sp_configure):对32位系统有意义。
5、Lock Pages in memory (企业版会自动开启):有一定机会确保sql server的物理内存数。

内存使用分类:

按用途分类:

Database Cache:存放数据页的缓冲区。
各类Consumer:
    Connection::默认4K
    General:包含语句的编译、范式化、每个锁数据结构、事务上下文、表格和索引的元数据等。默认8K。
    Query Plan:默认8k,
    Optimizer:默认8k,
    Utilities:像BCP、Log Manager、Parallel Queries、Backup的特殊操作。默认8k,
线程内存:进程内的每个线程分配0.5MB内存。存放线程的数据结构和相关信息。默认512K
第三方代码申请的内存(COM,XP...)

按申请方式分类:

有些内存申请方式是:预留Reserve一大块内存,然后使用的时候一小块一小块commit,而另外一些内存申请直接从地址空间Commit,这种叫Stolen
原文:http://blog.csdn.net/dba_huangzj/article/details/7527543