sqlserver的内存管理

一台服务器，尤其是一台 SQL Server 服务器，内存永远是最重要的系统资源，它的多少决定着整个服务器的整体性能。如果内存不足，会引起服务器做大量的 paging 动作，从而也影响了其他系统资源，比如磁盘、CPU的使用，重要性不言而喻。

理论知识

性能计数器 与内存相关的指标

• 以下是几个与内存相关的性能计数器，仅供参考：

计数器对象	性能计数器	注释
Memory	Available MBytes	现在系统空闲的物理内存数
Memory	Pages/sec	整个系统每秒钟由于Hard Page Fault需要从磁盘上读取或写入的页面数目
SQLServer:Buffer Manager	Buffer cache hit ratio	在缓冲区高速缓存中找到而不需要从磁盘中读取的页的百分比
SQLServer:Buffer Manager	Checkpoint pages/sec	由要求刷新所有脏页的检查点或其他操作每秒刷新到磁盘的页数
SQLServer:Buffer Manager	Lazy writes/sec	每秒被缓冲区管理器的惰性编写器(Lazy writer)写入的缓冲区数
SQLServer:Buffer Manager	Page life expectancy	页若不被引用，将在缓冲池中停留的秒数
SQLServer:Buffer Manager	Page reads/sec	每秒发出的物理数据库页读取数
SQLServer:Buffer Manager	Page writes/sec	每秒执行的物理数据库页写入数
SQLServer:Memory Manager	Target Server Memory (KB)	服务器能够使用的内存总量
SQLServer:Memory Manager	Total Server Memory (KB)	从缓冲池提交的内存，即Buffer Pool的大小

• 在一台内存充足的服务器上，Buffer cache hit ratio 的值变化很小，应该在99%以上，如果小于95% 通常就有了内存不足的问题
• 当MSSQL存在内存压力，就会将最久没有被重用的数据页和执行计划清理出内存，这个动作就是由 Lazy writer 完成。若MSSQL内存压力较大，Lazy writer就会被经常触发，Page life expectancy 也会突然下降或总是高高低低，不能维持在一个稳定的水平上。

如何诊断 SQL Server 的内存存在瓶颈

• 场景1：如果 Available MBytes 持续低于100 MB、Page life expectancy 突然骤降或持续低于300秒、Buffer cache hit ratio低于95%、Pages/sec 很高（忙着paging换进换出），很大可能是内存存在瓶颈，导致磁盘IO繁忙（内存是因，磁盘是果）
• 场景2：如果 Pages/sec 很高，但 Page life expectancy 也很高，此时SQL Server没有内存压力。这只是SQL Server在主动、高效地将新数据加载进缓存，这反而是内存充足、I/O能力强的表现。

关于 Total / Target / Max Server Memory 的区别

• Total Server Memory 是 SQL Server 自己分配的 Buffer Pool 内存的总和
• Target Server Memory 是 SQL Server 在理论上能够使用的最多的内存总和
• Max Server Memory 用来限制 SQL Server 缓冲池（Buffer Pool）可以使用的最大物理内存量

注：Max Server Memory 只限制“缓冲池”(存储数据页、索引页、计划缓存等)，而 SQL Server 的部分功能（如 CLR程序集、扩展存储过程、链接服务器、内存中OLTP的部分结构等）可能使用外部分配的内存，不会严格受此限制约束，所以 SQL Server 实际使用的内存可能会超过 Max Server Memory，这也是为什么不建议 Max Server Memory 设置过高（比如设为物理内存的90%）的原因

• 在 SQL Server 启动的时候，会检查 Max Server Memory 以及当前服务器的可用物理内存数，取最小值作为 Target Server Memory
• 在 SQL Server 运行的过程中，会定期比较 Total Server Memory 和 Target Server Memory 两个值：

Total与Target对比	SQL Server的后续处理
Total < Target	SQL Server 知道系统还有足够内存，会继续分配新的内存地址空间，Total 则会不断变大
Total = Target	SQL Server 知道可用内存已用完，不会再去分配新的内存地址空间
Total > Target	感知到 Windows 内存压力，调小 Target 值，使得 Total > Target，SQL Server 就会自动释放出内存

• 由于 SQL Server 是根据当前服务器的空闲物理内存数来计算 Target Server Memory，若不放心或者不想让 SQL Server 用光所有的可用物理内存，则可以调小 Max Server Memory，当 Total Server Memory 达到 Max Server Memory 的值，就不再申请新的内存
• 需要说明的是，Total Server Memory 和 Target Server Memory 都是指的是逻辑上的内存空间大小，而不是物理内存空间大小。数据是放在物理内存里还是放在 page file 里，SQL Server 把这个决定权交给了 Windows。对 SQL Server 来讲，缓存的主要是数据库里的数据。这些数据如果不是放在物理内存里，放在 Windows page file 和 SQL Server 自己的数据文件里其实没有太大的区别
• 对 SQL Server 来讲，在大部分情况下，可以认为 Total Server Memory 的大小和实际的物理内存使用量差不了太多

sp_configure 与内存相关的设置

• 更多请参考：SELECT * FROM sys.configurations

参数名称	参数说明
Min Server Memory (MB)	并不是用来限制最小物理内存
Max Server Memory (MB)	只限制 Buffer Pool（存储数据页、索引页、计划缓存等）
Set Working Set Size	没有用，请永远不要使用

• Min Server Memory 是一个逻辑概念，并不是用来限制最小物理内存，而是当 SQL Server 的地址空间增长到这个值以后，就不会再小于这个值。所以当 SQL Server 刚启动的时候，仅仅申请启动所需要的内存，此时内存使用量小于 Min Server Memory 是正常的。
• Max Server Memory 也是一个逻辑概念，并不是用来限制最大物理内存，因为数据放在物理内存还是缓存文件里，是由 Windows 决定的，它只能控制一部分 SQL Server 的内存使用量，如果出现 Virtual Bytes 或 Working Set 大于 Max Server Memory 也是一件很正常的事情，并不意味着 SQL Server 有内存泄露问题。
• Set Working Set Size 本来试图将 SQL Server 在物理内存中使用的内存数固定下来，但实际上并不能起到这个作用，有时候还会带来副作用，请永远不要使用

其他 与内存相关的设置

• 配置 SQL Server 的“锁定内存页”（Lock Pages in Memory）是一项Windows层面的权限设置。启用后，SQL Server 的进程内存将被强制保留在物理内存中，不会被操作系统交换到虚拟内存（分页文件）里。这项配置在高负载的数据库服务器上，尤其是在服务器内存资源紧张或与其他应用共用时，可以显著提升性能稳定性。
• 早期的 AWE (Address Windowing Extensions，地址窗口化扩展插件) 是一项为 32 位操作系统设计的特殊功能，目的是让 SQL Server 能够使用最高达 64GB 的物理内存。随着 64 位服务器的全面普及，这个选项已经完全失去了存在的意义，微软在 SQL Server 2016 及更高版本中，已弃用了这个选项。

关于内存使用的错误理解

• 误解1：Max Server Memory的值，就是SQL Server内存使用量的最大值，超过这个值就不正常

解读：Max Server Memory 这个值应该是Buffer Pool的上限，而不是SQL Server所有内存使用的上限。由于SQL内存的使用包括Buffer Pool和MemToLeave，所以SQL Server实际内存使用量一定会比Max Server Memory要大。

• 误解2：SQL Server的内存使用总量，就是性能监视器 SQLServer:Memory Manager – Total Server Memory 的值

解读：Total Server Memory的值，是SQL Server自己的代码申请的Buffer Pool空间大小。在SQL Server进程里跑的代码不都是SQL Server自己的代码。对第三方的代码（比如链接服务器）SQL Server是不知道它们申请了多少内存的。所以，真正的SQL进程申请的内存会比这个值大一些。

• 误解3：当系统有内存压力的时候，SQL Server 总是会自动释放内存

解读：通常，SQL Server的确会在系统有内存压力的时候自动释放内存。但是有个例外：若启用了 Lock Page In Memory，则当同一台服务器上的其他应用程序需要内存的时候，SQL Server很可能不会释放内存。在这种情况下，建议SQL Server设置Max Server Memory上限。

实战配置

配置内存的最佳实践

• 第1步，关于版本，强烈建议选择：64位操作系统 + 64位SQL Server
• 第2步，为 SQL Server 服务配置专用账号，并在“锁定内存页”中添加这个账号，避免SQL Server内存被抢占

• 第3步，设置 Max Server Memory 为物理内存的80%，预留足够的内存供 Windows 系统本身使用

注1：调整最大内存前后可关注 Available MBytes、Page life expectancy、Total Server Memory (KB) 这3个性能计数器的数值变化

注2：修改 Max Server Memory 在 2008 R2及以下版本，需要重启生效，而 2012及以上，则直接生效（如下图示）：

• 第4步，设置 Min Server Memory 默认即可（意义不大），而 Set Working Set Size 起不到固定 SQL Server 物理内存的作用，请永远不要使用

如何查看内存相关的信息

• 查看 Buffer Pool 中每个db的 objects and indexs 缓存的页、索引的数量（改造一下可以查看库级别的缓存数量）

--.查看Buffer内每个db的objects and indexs加载的页的数量
SELECT 
   dob.database_id 
  ,DB_NAME(dob.database_id) AS database_name 
  ,par.object_id 
  ,OBJECT_NAME(par.object_id) AS objname 
  ,par.index_id 
  ,COUNT(*) AS buffer_count 
FROM sys.allocation_units sau 
INNER JOIN sys.dm_os_buffer_descriptors dob ON sau.allocation_unit_id = dob.allocation_unit_id 
INNER JOIN sys.partitions par ON sau.container_id = par.hobt_id 
--WHERE dob.database_id = db_id() 
GROUP BY dob.database_id, par.object_id, par.index_id 
ORDER BY buffer_count DESC

• 查看不同缓存类型占用的内存大小

--.查看不同缓存类型占用的内存大小
SELECT TOP 10 
  type, 
  CAST(SUM(virtual_memory_reserved_kb + virtual_memory_committed_kb + awe_allocated_kb 
    + shared_memory_reserved_kb + shared_memory_committed_kb) / 1024.0 AS DECIMAL(18,2)) AS Total_Memory_MB,
  CAST(SUM(virtual_memory_reserved_kb) / 1024.0 AS DECIMAL(18,2)) AS Virtual_Reserved_MB,
  CAST(SUM(virtual_memory_committed_kb) / 1024.0 AS DECIMAL(18,2)) AS Virtual_Committed_MB,
  CAST(SUM(awe_allocated_kb) / 1024.0 AS DECIMAL(18,2)) AS AWE_Allocated_MB,
  CAST(SUM(shared_memory_reserved_kb) / 1024.0 AS DECIMAL(18,2)) AS Shared_Reserved_MB,
  CAST(SUM(shared_memory_committed_kb) / 1024.0 AS DECIMAL(18,2)) AS Shared_Committed_MB
FROM sys.dm_os_memory_clerks 
GROUP BY type 
ORDER BY Total_Memory_MB DESC

type	缓存描述	备注
MEMORYCLERK_SQLBUFFERPOOL	数据页面缓存，从磁盘读取并缓存的数据页，用于加速查询	最重要且占用最多
MEMORYCLERK_SQLCLR	CLR代码的缓存对象，该值过高则表示.NET程序集存在内存管理问题	-
MEMORYCLERK_SOSMEMMANAGER	SOS 内存管理器	-
MEMORYCLERK_SQLSTORENG	存储引擎内部对象	-
MEMORYCLERK_XE_BUFFER	扩展事件（XE）会话数据	-
MEMORYCLERK_FULLTEXT_SHMEM	全文索引相关数据	-
OBJECTSTORE_LOCK_MANAGER	锁管理器，该值过高则表示数据库中存在严重的阻塞或大量未释放的锁	-
CACHESTORE_OBJCP	触发器(triggers)、存储过程(stored proc)和函数(function)的执行计划缓存	-
CACHESTORE_SQLCP	动态TSQL语句(Adhoc)和预编译(prepared)TSQL语句的执行计划缓存	-
CACHESTORE_TEMPTABLES	缓存临时对象，例如local temp table, global temp table, 表变量等	-
USERSTORE_TOKENPERM	保存所有用户的安全上下文以及各种跟安全相关的令牌(Token)	-
USERSTORE_SXC	暂时存放正在执行中的语句的PRC参数，比如调用动态TSQL的传参	-

• 查看会缓存执行计划的对象

SELECT 
  objtype, 
  CAST(SUM(CAST(size_in_bytes AS BIGINT)) / 1024.0 / 1024.0 AS DECIMAL(18,2)) AS sum_size_mb, 
  COUNT(bucketid) AS cache_counts 
FROM sys.dm_exec_cached_plans 
GROUP BY objtype 
ORDER BY sum_size_mb DESC

objtype	对象类型	备注
Proc	存储过程	会缓存执行计划的常见对象
Prepared	预定义语句	会缓存执行计划的常见对象
Adhoc	动态查询	会缓存执行计划的常见对象
ReplProc	复制筛选过程	-
Trigger	触发器	-
View	视图	-
Default	默认值	-
UsrTab	用户表	-
SysTab	系统表	-
Check CHECK	约束	-
Rule	规则	-

• 与内存相关的常见查询

--.查看系统信息(CPU,RAM等)
SELECT 
  ds.hyperthread_ratio AS Hyperthread_Ratio 
 ,ds.cpu_count/hyperthread_ratio AS Physical_CPU_Count 
 ,ds.cpu_count AS Logical_CPU_Count 
 ,ds.physical_memory_kb/1024 AS Physical_Memory_MB 
 ,ds.committed_kb/1024 AS Committed_Memory_MB 
 ,ds.committed_target_kb/1024 AS Committed_Target_Memory_MB 
 ,ds.sql_memory_model_desc 
 ,ds.sqlserver_start_time 
 ,ds.sql_memory_model_desc AS Sql_Memory_Model 
 ,dp.locked_page_allocations_kb / 1024 AS Locked_Pages_MB 
FROM sys.dm_os_process_memory as dp
CROSS JOIN sys.dm_os_sys_info as ds

--.与内存相关的性能计数器
select * from sys.dm_os_performance_counters where counter_name like '%memory%'

--.与内存相关的sp_configure设置
SELECT name, is_dynamic, is_advanced, value_in_use FROM sys.configurations WHERE configuration_id IN (1543,1544,1532)

--.查看每个数据库加载的页的数量
SELECT 
  CASE database_id WHEN 32767 THEN 'ResourceDb' ELSE db_name(database_id) END AS Database_name 
 ,COUNT(*)*8 AS cached_pages_kb 
FROM sys.dm_os_buffer_descriptors 
GROUP BY db_name(database_id), database_id

如何清理缓存并释放内存

• 先介绍一下 DBCC MEMORYSTATUS，它可以输出 SQL Server 内部内存使用情况的详细快照，返回的数据非常详细，是排查内存问题的利器

命令	作用范围	主要用途	影响
dbcc flushprocindb (@dbid)	特定数据库	清除指定数据库的所有缓存执行计划	影响范围小，仅限于目标数据库
dbcc freeproccache	缓冲池	清除Buffer Pool中所有缓存的执行计划	影响范围大
dbcc dropcleanbuffers	缓冲池	清除Buffer Pool中所有缓存的数据页	影响范围大，会清空内存中的数据页
dbcc freesessioncache	分布式查询	清除分布式查询和链接服务器相关缓存	常用于解决链接服务器问题
dbcc freesystemcache('all')	整个实例	清除几乎所有类型的系统级缓存	不会清除Buffer Pool中的数据页，数据仍在内存中
动态调整“最大服务器内存”	缓冲池	先调小再改回原值，可强迫释放内存	不用重启MSSQL服务，即可生效

• 执行 dbcc dropcleanbuffers 只会清掉 clean buffer，若想把 dirty buffer 的空间也清掉，则先执行 checkpoint 把 dirty buffer 中的数据写到disk，再执行 dbcc dropcleanbuffers
• 清除计划缓存之后，将导致对所有后续执行计划进行重新编译，查询性能会暂时突然降低，请小心使用，尤其是 dbcc dropcleanbuffers
• 需要注意的是，这几个命令虽然会清除掉现有缓存，为新的缓存腾地方，但是 SQL Server 并不会因此释放掉已经占用的内存
• 无奈的是，SQL Server 并没有提供任何命令允许我们释放不用的内存。因此我们只能通过动态调整“最大服务器内存”来强迫它释放内存