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

一、问题：

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

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

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

     内存是Sqlserver的生命线。在errorlog中，出现一下情况：      

这类问题往往不是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）。这两部分会严格区分。

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

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

方法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