SQL Server内存机制详解

1.前言

对于数据库引擎来说，内存是一个性能提升的重要解决手段。把数据缓存起来，可以避免在查询或更新数据时花费多余的时间，而这时间通常是从磁盘获取数据时用来等待磁盘寻址的。把执行计划缓存起来，可以避免重复分析执行计划时带来额外的CPU及各种资源的开销。通过在内存中开辟查询内存空间，可以迅速地完成排序、哈希等计算，达到快速返回运算结果的目的。若没有足够的内存空间，数据库引擎将无法快速地响应用户的请求。

2.SQL Server如何从操作系统层面分配内存

SQL Server存储引擎本身是一个Windows下的进程，所以SQL Server使用内存和其它Windows进程一样，都需要向Windows申请内存（通过VirtualAlloc之类的API向Windows申请内存）。

3.物理内存、虚拟内存、虚拟内存管理器

3.1物理内存（RAM）

内存芯片提供的物理存储空间，能被CPU直接访问，访问速度快，易丢失。内存性能指标GB/s,ns（纳秒），前者是吞吐量，后者是响应时间。磁盘性能指标MB/s,us(微秒)，从两者对比就能看出内存访问速度是远优于磁盘的。

3.2虚拟内存

物理内存容量是有限的，如果所有进程都直接使用有限的物理内存，那新的进程将无法为他们找到任何物理内存，那么物理内存将容易成为瓶颈。所以Windows会授予每个进程一个虚拟地址空间（Virtual Address Space，VAS），通过VAS建立应用程序与物理内存的桥梁。

3.2.1虚拟地址空间（Virtual Address Space，VAS）

是指一个应用程序能够申请访问的最大地址空间。VAS作为中间的抽象层的，不是所有的请求都直接映射到物理内存，它首先映射到VAS，然后映射到物理内存。
而两个进程可以共用一个VAS，而VAS的大小取决于CPU架构，具体请看下面表格：

VAS有两种内存模式，Kernel Model和User Model。Kernel Model下的VAS是供Windows系统进程使用，而User Model下的VAS是供用户进程使用。
由表格可知，32位Windows系统应用程序可以访问最大2GB的VAS，64Windows位系统可以访问最大8TB的VAS。这意味着在32位Windows系统中一个word文档进程跟一个SQL Server进程能得到最大2GB的VAS是一样的。因此，从理论上讲，这意味着任何应用程序进程在32位Windows系统上都将共享最大限度的2 G的VAS。

3.3虚拟内存管理器（Virtual Memory Manager，VMM）

VMM是负责把物理内存在系统中所有需要内存的进程之间作共享，必要时会从VAS回收物理内存，把数据存储到页面文件上面去，保证数据永不丢失。当进程需要内存时，VMM会从页面文件中查找数据，并将这数据写入一部分空闲内存当中，然后将新页面映射到需要操作的VAS当中。

4.SQL Server内存架构

SQL Server 2012对内存管理这块跟SQL Server 2008还是有比较大的区别的，参考一些资料，下面我们来看看两者具体架构。

SQL Server 2008 R2：

SQL Server 2012：

名词术语

4.1缓冲池（Buffer Pool）

为了更加清楚了解Buffer Pool，我们先来了解下 SQL Server的所需要的内存有哪些，其中包括SQL Server服务(sqlserver.exe)和其它一些组件所占用的内存，例如SQL Server代理程序（sqlagent.exe）, SQL Server复制代理程序、SQL Server报表服务(ReportingServicesService.exe)、SQL Server Analysis Services(msmdsrv.exe)、SQL Server Integration Services(MsDtsSrvr.exe)，和SQL Server 全文搜索(msftesql.exe)。
在一台运行SQL Server的服务器上，运行着sqlserver服务(sqlserver.exe)和其它一些组件。在sqlserver服务(sqlserver.exe)获取到的内存中，又分为2大块：一部分为Buffer Pool，另一部分为非Buffer Pool，旧称MemToReserve（默认sqlserver.exe给它预留了256MB）。下表为这两部分内存各自的用途：

4.2 Single-Page

这块内存是<=8kb的存储，适用于sql server 2008及以前，属于Buffer Pool缓冲池来分配。有存储数据页面，Consumer功能组件。

4.3 Multi-Page

这块内存是>8kb的存储，适用于sql server 2008及以前，不属于Buffer Pool缓冲池来分配, 有存储Consumer功能组件, 第三方代码, Threads线程。

4.4 Any Size Page

这个适用于sql server 2012及以上，整合了single-page,multi-page统称any size page。

4.5 Memory Manager

它来统一响应SQL Server 内部各种组件内存申请的请求。因为这个原因，在SQL Server 2012里面，max server memory 不再像以前的版本那样，只控制buffer pool的大小，也包括那些大于8kb 的内存请求。也就是，max server memory能够更准确地控制SQL Server 的内存使用了。

5.SQL Server 2008内存

从内存架构我们可以看到有page reservation需预先申请的内存，有momory objects从windows api申请的内存，有clr第三方申请的内存。

内存使用分类

5.1按用途分类

（1）Database Cache（数据页面缓冲区）

存放数据页面的缓冲区。SQL Server数据库里的数据都是以8KB为一个页面存储。当有用户需要使用到这个页面上存储的数据时，SQL Server会把整个页面都调入内存，供用户使用。所以8KB是数据访问的最小单元。当用户修改了某个页面上的数据时，SQL Server会在内存中将这个页面修改，但是不会立刻将这个页面写回磁盘，而是等到后面的Checkpoint或Lazy Write的时候集中处理。

（2）各类Consumer

SQL Server的很多功能组件，都必须要申请内存来完成它们的任务。这些统称为&ldquo;Consumer&rdquo;。常见有如下：

• Connection：SQL Server为每个连接分配一个数据结构，存储关于这个连接的信息。另外，还会分配一个输入缓冲池，缓冲客户端发来的指令；一个输出缓冲池，存放SQL Server返回的结果，等待客户端取走。

• General：一组大杂烩。包括语句的编译、范式化、每个锁数据结构、事务上下文、表格和索引的元数据等。

• Query Plan：语句和存储过程的执行计划。和Database Cache类似，如果SQL Server没有内存压力，它就会保留每一个生成的执行计划，供以后的用户重用，减少Comlile的消耗。所以Query Plan也会是一块比较大的内存使用区域。

• Optimizer：SQL Server在生成执行计划的过程中需要消耗的内存。

• Utilities：像BCP、Log Manager、Parallel Queries、Backup等比较特殊的操作需要的内存。

（3）线程内存

SQL Server会为每个进程内的每个线程分配0.5MB的内存，以存放线程的数据结构和相关信息。

（4）第三方代码申请的内存（COM，XP...）

在SQL Server的进程里，会运行一些非SQL Server自身的代码。例如，用户定义的CLR或者Extended Stored Procedure代码，Linked Server需要加载的数据连结驱动，调用SQL Mail功能需要加载的MAPI动态库等。这些代码也会申请内存，会算在SQL Server自己都不知道。

5.2按申请方式分类

有些SQL Server内存的申请方式，是预先Reserve一块大的内存，然后在使用的时候一小块一小块地Commit。而另外的内存申请则直接从空间里Commit。在SQL Server里，把后一种方式叫Stolen。
在SQL Server里，对Database Cache，SQL Server会先Reserve，再Commit。其他的所有内存使用，基本都是直接Commit，都是&ldquo;Stolen&rdquo;。要重申的是，Stolen内存也是正常使用的内存，不是泄漏掉的内存。
之所以要把这两种分开，是因为SQL Server不会对Stolen的内存使用AWE功能。也就是说，AWE扩展出去的内存，只能用来存放Database Cache。其他内存还要在原来的那2GB里想办法。

5.3按申请大小分类

对于SQL Server自己申请的内存，有两种内存申请单位。
小于等于8KB一个单位内存申请，SQL Server就分配一个8KB页面。所有这些页面都集中管理，这块内存被称为Buffer Pool。一次一个页面的这种分配称为Single Page Allocation。
对于大于8KB为单位的内存申请，SQL Server把它们集中在另外一个区域，称为Multi-Page Allocation（旧称MemToLeave）。而这种分配称为Multi-Page Allocation。

5.4各个内存分类方法之间的关系：

这里的一个例外是运行在SQL Server进程里的CLR代码所申请的内存。这部分内存像第三方代码一样，也是使用MemToLeave的内存。但是，CLR可能也会用Reserve-Commit的方式申请内存。所以MemToLeave的内存也并不是都是Stolen的。

6.SQL Server 2012内存

根据SQL Server内存架构图，我们可以知道，在2012版本上，Single Page Allocation跟Multi-Page Allocation合并为Any Size Page Allocation了。而max server memory控制的不但是 Buffer Pool内存大小，而是所有大于等于小于8KB的内存请求。
如图：

比如我设置最小服务器内存为8G，重新启动下SQL Server (MSSQLSERVER)服务，再使用dmv来查看当前实例的总内存空间，以及占用内存空间：
--Target Server Memory (KB)最多能申请的内存量
--Total Server Memory (KB)目前使用了多少内存量

SELECT counter_name, ltrim(cntr_value*1.0/1024.0/1024.0)+'G' 
AS memoryGB FROM sys.dm_os_performance_counters 
WHERE counter_name like '％target％server％memory％'or counter_name like '％total％memory％'

从查询结果可以看到当我们在SQL Server设置最小服务器内存为8G的时候，给SQL Server分配了多少内存，它就占用多少多少内存，从而达到性能最佳。

来源：https://www.cnblogs.com/wzk153/p/10836996.html