SqlServer 内存篇（二）—— SqlServer内存管理模式及常见问题

以下概念按sqlserver 2005及2008中描述，2012中有改变，参考系列最后一篇。

一、 SqlServer内存参数

与其他数据库相比，sqlserver可调整的内存参数很少，只有以下几个：

1. Min Server Memory（MB）

定义sqlserver最小buffer pool值。

注意事项

• Min Server Memory是一个逻辑概念，控制sqlserver total server memory大小。数据是放在物理内存还是缓冲文件由windows决定，因此这个值不能保证sqlserver最小物理内存数，只能保证虚拟内存+物理内存不小于该值。
• Min Server Memory指的是，在sqlserver地址空间增长到该大小后就不会再小于这个值。SQL Server 不会在启动时立即分配 min server memory 中指定的内存量，只逐渐会commit需要的内存，刚启动时内存量小于Min Server Memory很正常。
• 如果SQL Server 的负载很低，不需要使用到 min server memory指定的内存量，SQL Server 可能始终以低于其值的内存运行。

2. Max Server Memory（MB）

定义sqlserver最大buffer pool值。

也注意以下两点：

• Max Server Memory也是一个逻辑概念，控制sqlserver total server memory大小，最小值为128 MB
• Max Server Memory只能控制sqlserver buffer pool部分内存最大值，这不是sqlserver内存的全部，所以sqlserver使用量大于这个值很正常（但不应大太多）。

一般而言，SqlServer发生内存泄漏可能性很小。如果是刚启动的SqlServer，占用内存会逐步增大至max server memory 参数设置的最大值（修改不需重启）而后渐趋平稳。如果未设置该参数，Windows默认是2048TB，相当于无限制；Linux默认是操作系统内存的80%，留20%是为避免被OOM。

-- 将最大服务器内存选项设置为 4 GB
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO

查看当前设置

SELECT c.value, c.value_in_use
FROM sys.configurations c WHERE c.[name] = 'max server memory (MB)'

3. AWE Enabled

启用AWE以突破32位服务器2G用户寻址，sqlserver 2012开始已不支持。

4. Lock pages in memory

确定哪些帐户可以使用进程将数据保留在物理内存中，从而阻止系统将数据swap到磁盘的虚拟内存中。Standard Edition及更高版本自动开启，可以在一定程度上确保sqlserver物理内存数，当然如果windows内存压力太大，开启也救不了。

二、 sqlserver内存分类

1. 按用途分类

1）database cache：缓冲池，类似oracle buffer cache，通常是最大的区域

2）各类consumer：sqlserver功能组件统称为consumer，主要包含：

• connection：连接信息，另外还包括输入/出缓冲池
• general：锁数据结构、表和索引元数据等
• query plan：sql语句和存储过程执行计划，这块区域也比较大
• Optimizer：生成执行计划过程中消耗的内存
• utilities：bcp、log manager、并行查询、备份等特殊操作所需内存

3）线程内存：sqlserver为进程内的每个线程分配0.5M内存，存放线程数据结构及相关信息

4）第三方代码：由于它们不是sqlserver自身代码，所以sqlserver也不知道它们申请了多少内存。一般这块内存不会很大，除非第三方代码存在大量内存申请甚至内存泄漏，或者linked server需要从远端数据库取大量数据。

2. 按申请方式分类

1）先reserve再commit：database cache使用的申请方式

2）直接commit（称为stolen）：database cache之外其他部分内存使用的申请方式

之所以区分这两种申请方式，是因为sqlserver不会对stolen的内存使用AWE功能。也就是说，32位服务器AWE扩展的内存只能存放database cache数据，其他内存还是要在2G里想办法。

3. 按申请大小分类

1）single page allocation：对所有可以分成<=8KB为单位的内存申请，sqlserver分配一个page（8KB）。这些page集中在一个内存区域（buffer pool）管理。

2）multiple page allocation：对>8KB为单位的申请，sqlserver将它们存在另一个区域，这个内存区域称为multi-page（以前叫MemToLeave）。

4. 各类内存分类方法间的关系

下面按用途分，看看各种类型使用多大内存，存放在什么区域。

1）database cache：都是数据页面，均以8KB为单位，放在buffer pool。

2）各类consumer：

• connection：与network package size（客户端和sqlserver通信的每个数据包大小）有关。若是默认的4K，输入/出缓存会放在buffer pool；若为8K或更大，输入/出缓存放在multi-page
• general：绝大部分内存以8K为单位申请，使用buffer pool；但若有语句特别长，需要使用大于8K为单位的内存，会放在multi-page
• query plan、optimizer、utilities：与general类似，绝大部分使用buffer pool；但若有语句特别长，它的query plan、Optimizer等会使用一部分multi-page

3）线程内存：每个线程分配0.5M内存，自然放在multi-page

4）第三方代码：由于不是sqlserver自身代码，sqlserver也不知道它们申请了多少内存，所以都放在multi-page

返回有关当前分配内存的信息

SELECT 
  physical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB, 
   large_page_allocations_kb/1024 AS sql_large_page_allocations_MB, 
   locked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB,
   virtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB, 
   virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB, 
   virtual_address_space_available_kb/1024 AS sql_VAS_available_MB,
   page_fault_count AS sql_page_fault_count,
   memory_utilization_percentage AS sql_memory_utilization_percentage, 
   process_physical_memory_low AS sql_process_physical_memory_low, 
   process_virtual_memory_low AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;

三、 不同服务器下sqlserver各部分内存上限

1. 无AWE

2. 32位有AWE

四、 sqlserver内存相关常见问题

1. windows还有很多物理内存未使用，是不是sqlserver就一定不缺内存？

当然不一定，因为windows内存多不代表sqlserver就能用到：

• sqlserver设置Max Server Memory参数会限制sqlserver继续申请内存
• 使用32位服务器，未开启AWE时sqlserver最多只能用2G内存；就算开了，multi-page部分还是用不到

2. sqlserver进程内存使用量持续上涨说明sqlserver有内存泄漏？

也不一定。前面提到过，sqlserver在启动时只申请需要的内存，随着用户使用，sqlserver会继续申请内存直到windows有压力或者到达Max Server Memory参数上限，这种持续上涨是正常的。

作为一个成熟的软件，sqlserver本身发生内存泄漏的可能性很低，应用程序发生内存泄漏的可能性更高一点。

3. Max Server Memory代表sqlserver内存的最大值，超过就不正常？

这个也在前面提到过，Max Server Memory只能控制sqlserver buffer pool部分内存最大值，这不是sqlserver内存的全部，所以内存使用量大于这个值很正常（但不应大太多）。

4. 当系统有内存压力时，sqlserver一定会释放内存？

• 如果未成功开启Lock page in memory，当系统有内存压力时，sqlserver会释放内存
• 如果成功开启Lock page in memory，当系统有内存压力但不大时，sqlserver不会释放内存
• 如果成功开启Lock page in memory，但是系统内存压力很大，sqlserver还是会释放内存

如果这类问题发生，对SqlServer影响会非常大。轻则SqlServer响应异常缓慢，重则大量用户无法连接SqlServer，SqlServer短暂hang死。错误日志中常常能看到如下告警：

spid1s a singnificant part of sqlserver process memory has been paged out.
this may result in a performance degradation（性能降级）
duration:0 seconds 
working set:1086400 commited:2160928 memory utilization:50%

5. 增加multi-page可以提高sqlserver性能？

如果能确定是multi-page内存不足导致的问题，适当增加multi-page当然可以提高sqlserver性能

如果不是，加大了未必会有用。尤其在32位未开启AWE的服务器，sqlserver可用内存只有2G，加大multi-page就意味着要减小buffer pool，很有可能得不偿失。

6. 增加服务器内存一定能提高sqlserver性能？

• 这跟第一个问题类似，sqlserver要用得到才有可能提高性能（用不到的情况不重复列了）
• 如果数据库很小，现有内存已足够缓存常用数据，再加内存不会有什么帮助，甚至会缓存很多无用数据，加大维护成本
• 如果sqlserver确实缺内存，也要先搞清楚是哪部分缺内存（方法下篇讨论）

7. stolen内存真的是偷来的吗？

其实从windows层面，任何内存都需要先reserve再commit。

为什么stolen部分可以直接commit？是因为在sqlserver中，buffer pool已经将所有将需要的内存提前reserve了。

如果sql要做的是用buffer pool已经reserve的地址空间去commit，而commit后的内存又不存放database cache数据，这部分内存就被称为stolen。

参考

服务器内存配置选项 - SQL Server | Microsoft Learn