云上SQL Server 内存管理

前言：众所周知，cpu，内存，磁盘是一个服务非常重要的三个核心资源，本章将介绍SQL Server 内部的内存结构和内存管理。最后给出内存在腾讯云SQL Server云数据库监控指标中的反应，帮助用户了解SQL Server云数据库的特性。

1. SQL Server数据库与NUMA

NUMA（非一致性内存架构）是为了扩展主板性能。在多个CPU服务器上，为了避免共享内存总线成为瓶颈，NUMA为每个CPU都单独设置了内存控制器。SQL Server的基础架构就是基于NUMA设计的。如果您之前了解过SQL Server内核任务调用部分，那您一定不会对node，scheduler，等名字有一定了解。当SQL Server实例启动时，将根据检测到的NUMA创建对应的node节点，然后再根据检测到的node中逻辑处理器的个数创建对应的scheduler。

SELECT * FROM sys.dm_os_nodes;

查看SQL Server 启动后node的节点个数。

SELECT * FROM sys.dm_os_scheduler;

查看SQL Server 启动后scheduler的个数，以及和node之间的关系。

2. SQL Server的内存

1. 内存节点

内存节点就如上面所说的，和NUMA向对应，一个SQL Server实例在启动的过程中至少会有一个内存节点。您可以通过下面命令查看内存节点。

select * from sys.dm_os_memory_nodes;

memory_mode_id和NUMA的node节点对应

2. 内存分配员

所有组件都需要内存分配员来访问内存节点来分配内存，比较消耗内存的组件还有期专用的内存分配员，可以通过一下语句查看内存员的情况。

select type,memory_node_id,virtual_memory_reserved_kb,virtual_memory_committed_kb from sys.dm_os_memory_clerks order by virtual_memory_reserved_kb desc;

最大的内存分配员是MEMORYCLERK_SQLBIFFERPOLL，为缓冲池分配内存的对象，归属于的内存节点

3. 缓存

在内存中会缓存很多有用的数据，比如执行计划缓存，数据缓存，存储过程，视图的缓存等等。其中占用最大的是缓冲池，也就是缓存用户数据的地方。使用下面SQL可以查询每个数据库使用缓冲池的大小。

select COUNT(*)*8/1024 AS 'Catch Size (MB)',case database_id when 32767 then 'resourceDB' else db_name(database_id) end as 'Dtatbases'  from sys.dm_os_buffer_descriptors group by DB_NAME(database_id),database_id order by 'Catch Size (MB)' desc;

不同数据库在缓冲池中的数据库缓存大小

3. 如何判断SQL Server云实例内存不够用。

通常情况下：

select counter_name, ltrim(cntr_value*1.0/1024.0) as MB from master.sys.dm_os_performance_counters where counter_name like '%total_server_memory%'

查看当前缓冲池的实际总大小。

select counter_name, ltrim(cntr_value*1.0/1024.0) as MB from master.sys.dm_os_performance_counters where counter_name like '%target_server_memory%'

查看当前缓冲池的理想大小。通常认为理想缓冲池大小与实际缓冲池大小是一致的。如果不一致存在两种情况，1. 业务的压力不够是的数据没有被缓存起来。2. 物理内存不足，不能再将用户数据放在缓冲池中。

还可以通过下面SQL查看在sqlserver期望一个页面在缓冲池中保留的时间，单位是秒

select counter_name, cntr_value as s from master.sys.dm_os_performance_counters where counter_name like '%page_life_expectancy%'

到内存紧张时，在缓冲池中的页会被平凡置换出内存，因此保留时间会变短。如果保留时间地狱300秒那么就需要关注内存情况。

SQL Server云实例提供了丰富的监控指标，例如：“缓冲区缓存命中率”，“执行计划缓存命中率”，“内存占用”，“最大内存”。从这些指标中可以判断实例的内存是否不够用。

控制台提供的“内存占用” 对应的是 Total Server Memory (KB) 指标。

控制台提供的“最大内存” 对应的是Target Server Memory (KB) 指标。

控制台提供的“缓冲区命中率”对应的是Buffer cache hit ratio 指标。

控制台提供的“执行计划缓存命中率”对应的是Cache Hit Ratio 指标。

“内存占用“这个指标，在实例启动后经过一段时间的运行，会和云上购买的实例最大内存基本一致，不用过多担心。可以主要关注“缓冲区命中率”指标，如果命中率持续低于90%，请考虑是否需要增加内存。