sql server stolen内存问题，一次记录

【0】相关事件情况

针对目标：故障机器

时间：2021-10-20 16:20:00 - 2021-10-20 17:37:00

事件：有业务卡顿、阻塞情况分析

【1】七天的内存使用趋势图

 　　

释义如下：

Connection Memory (KB)	指定服务器正用来维护连接的动态内存的总量。
Database Cache Memory (KB)	指定服务器当前正用来缓存数据库页面的内存量。
External benefit of memory	将内存添加至特定缓存的性能优势内部评估。 该评估可被引擎用来平衡缓存之间的内存使用情况，并且在对缓存异常增长情况进行故障排除时也很有用。 该值基于内部计算显示为整数。
Free Memory (KB)	指定服务器当前未使用的已提交内存量。
Granted Workspace Memory (KB)	指定当前授予执行哈希、排序、大容量复制和索引创建操作等进程的内存总量。
Lock Blocks	指定服务器上使用的锁块的当前数目（定期进行刷新）。 一个锁块代表一个单独的锁定资源，如表、页或行。
Lock Blocks Allocated	指定所分配的锁块的当前数量。 服务器启动时，分配的锁块数加上分配的锁拥有者块数依赖于 SQL Server Locks 配置选项。 若需要更多的锁块，此值会增加。
Lock Memory (KB)	指定服务器用于锁的动态内存总量。
Lock Owner Blocks	指定当前正在服务器上使用的锁拥有者块的数目（定期进行刷新）。 一个锁拥有者块代表一个独立线程对某一对象上的一个锁的拥有权。 因此，若三个线程在一个页上各有一个共享 (S) 锁，就会有三个锁拥有者块。
Lock Owner Blocks Allocated	指定所分配的锁拥有者块的当前数量。 服务器启动时，分配的锁拥有者块数和分配的锁块数依赖于 SQL Server Locks 配置选项。 若需要更多的锁拥有者块，此数值会动态增加。
Log Pool Memory (KB)	服务器正用于日志池的动态内存的总量。
Maximum Workspace Memory (KB)	指示用于执行哈希、排序、大容量复制和索引创建操作等进程的最大可用内存数。
Memory Grants Outstanding	指定成功获得工作空间内存授权的进程总数。
Memory Grants Pending	指定等待工作空间内存授权的进程总数。
Optimizer Memory (KB)	指定服务器正用于查询优化的动态内存总数。
Reserved Server Memory (KB)	指示服务器保留供将来使用的内存量。 此计数器显示最初授予（显示在 Granted Workspace Memory (KB) 中）但当前未使用的内存量。
SQL Cache Memory (KB)	指定服务器正用于动态 SQL 缓存的动态内存总数。
Stolen Server Memory (KB)	指定服务器当前正用于除数据库页面之外的其他用途的内存量。
Target Server Memory (KB)	指示服务器能够使用的理想内存量。
Total Server Memory (KB)	指定服务器已使用内存管理器提交的内存量。

【2】分析

（2.1）当前问题的核心指标

Total Server Memory (KB)	23.4 GiB	22.2 GiB	23.4 GiB
Stolen Server Memory (KB)	20.9 GiB	18.4 GiB	18.3 GiB
Database Cache Memory (KB)	5.11 GiB	3.73 GiB	5.08 Gi

我们核心关注的是这3个大占用；

Total server memory（KB）：即当前sql server实际使用占用的内存

Database Cache Memory（KB）：指定服务器当前正用来缓存数据库页面的内存量。即脏页和查询缓存；

Stolen server memory（KB）：即非  Database Cache Memory（KB）数据库页面之外的所有内存总量（即包含其他所有的）；

核心的问题就在于 Stolen server memory（KB）的高占用，22G占了18.3GB，直接达到85%左右，其占用特性如下：

（1）描述了用于排序、散列、join、游标、CLR、建索引等（查询工作区内存）的缓冲区;语句的分析、优化、执行计划缓存、计算等也都需要用到stolen;

（2）用于分配的通用内存存储区以存储内部数据结构（例如锁、事务上下文和连接信息）的缓冲区;程序连接创建的变量、对象等数据没有释放；

（3）不允许惰性写入器进程将Stolen缓冲区 刷新出缓冲池。也就是无法自动释放，除非调用者主动关闭；

（2.2）10.20故障情况根因

如上图，本身 database cache memory（脏页、数据页、查询缓存）占用的内存就只有3G；

由于当时有2个大查询 join、order by、表扫描、及发布订阅的清除分发操作（定时自动系统作业），需要增加 stolen server memory 内存；

而我们最大内存设置只有22GB，所以只能从 database Cache memory 数据页中的内存去调取；

而后发现数据页内存被强制分去2.5GB左右内存，跌至只有1GB左右；然后又有大扫描需要从磁盘刷页到database cache，导致其他的增删查改小查询也需要对内存做等待；

至此，常规增删查改出现大量等待内存分配情况；最终导致业务卡顿，甚至连接中断；

（2.3）Stolen server memory（KB）高占用分析

1、如果声明了很多游标，用完了不关

2、prepare很多执行计划，不un-prepare,不登出SQL就会占用

3、系统内部资源占用（如订阅发布、镜像、内部调度等）

再结合（2.1）所说的这3条特性；

（1）描述了用于排序、散列、join、游标、CLR、建索引等（查询工作区内存）的缓冲区;语句的分析、优化、执行计划缓存、计算等也都需要用到stolen;

（2）用于分配的通用内存存储区以存储内部数据结构（例如锁、事务上下文和连接信息）的缓冲区;

（3）不允许惰性写入器进程将Stolen缓冲区 刷新出缓冲池。也就是无法自动释放，除非调用者主动关闭；

（2.4）结论与建议

做出尝试：在已经清理游标、查询缓存、执行计划缓存、清理连超1个月的连接信息后，50.18的该参数值依然没有减少；

且当我减少最大内存阈值，stolen server memory 依旧会维持在18G左右，反而 database Cache memory 内存被减少了；

当我增加最大内存阈值时，stolen server memory 也没有增加，依然维持在 18G左右， database Cache memory 内存得到了增加；

结论：无法有效的正常收缩Stolen server memory（KB）该模块值的内存占用大小   

也无法定位该stolen server memory中的内存的具体占用模块；可能是未知的内存泄露；

建议：重启服务，并加内存条，8G起步，建议16GB及以上；

（2.5）最终解决

重复服务可以解决，但要长期没有问题，建议还是假内存条

【附录】内存DMV排查

（1）内存相关DMV

-- SQL SERVER 2016
select * from sys.dm_os_memory_brokers  --代理
select * from sys.dm_os_memory_cache_clock_hands
select * from sys.dm_os_memory_cache_entries -- 缓存实际项目的实际体质
select * from sys.dm_os_memory_clerks  order by type,pages_kb desc
select * from sys.dm_os_memory_nodes   -- numa内存节点，一般64为dac管理员专用连接
select * from sys.dm_exec_query_memory_grants
select * from sys.dm_os_memory_pools
select * from sys.dm_os_nodes
select * from sys.dm_os_process_memory --查看操作系统内存

select * from sys.dm_resource_governor_resource_pools --【重要】资源池
select type,name,sum(pages_kb)/1024 as size_mb from sys.dm_os_memory_clerks group by type,name order by size_mb desc  -- 【重要】实际所有内存分配类别
select * from sys.dm_os_memory_cache_counters order by pages_kb desc  -- 【重要】缓存项目分配情况，查询计划、登录缓存等等计数器
select * from sys.dm_exec_cached_plans -- 【重要】执行计划缓存
select * from sys.dm_os_buffer_descriptors --【重要】查看各数据库占用的缓冲池,也可以具体到表
select * from sys.dm_os_memory_objects  -- 【重要】，实际的内存分配到表

（2）排查思路

排查思路：

Stolen Server Memory  高，你可以通过 sys.dm_os_memory_clerks 查看各个内存管理员管理的内存分配情况；除了 BUFFERPOOL 的，看看其他的内存占用是什么最多；

可以通过 sys.dm_os_memory_cache_counters 查看缓存分配情况；

可以通过 sys.dm_os_buffer_descriptors 查看各数据库占用的缓冲池；

可以通过 sys.dm_exec_cached_plans 查看计划缓存的分配情况。

确认打了最新补丁，其实  sys.dm_os_memory_clerks  看到的对象，不影响的话可以使用 DBCC FREESYSTEMCACHE('XXXXX') 清除指定内存管理员的内存分配。

4个清理：

官网：https://docs.microsoft.com/zh-cn/sql/t-sql/database-console-commands/dbcc-freeproccache-transact-sql?view=sql-server-ver15

　　（1）DBCC DROPCLEANBUFFERS ：从缓冲池删除所有清除缓冲区，并从列存储对象池中删除列存储对象。

　　（2）DBCC FREEPROCCACHE：删除计划高速缓存中的所有元素，通过指定计划句柄或 SQL 句柄从计划高速缓存中删除特定计划，或者删除与指定资源池相关联的所有高速缓存条目。

　　　　　　　　DBCC FREEPROCCACHE 不清除本机编译的存储过程的执行统计信息。 过程高速缓存不包含有关本机编译的存储过程的信息。

　　　　　　　　从过程执行中收集的任何执行统计信息都将显示在执行统计信息 DMV 中：sys.dm_exec_procedure_stats (Transact-SQL) 和 sys.dm_exec_query_plan (Transact-SQL)。

　　（3）DBCC FREESESSIONCACHE：刷新针对 Microsoft SQL Server 实例执行的分布式查询所使用的分布式查询连接缓存。

　　（4）DBCC FREESYSTEMCACHE：（最重要，所有的执行计划缓存）从所有缓存中释放所有未使用的缓存条目。

　　　　　　　　　　　　　　　　　　 SQL Server 数据库引擎会事先在后台清理未使用的缓存条目，以使内存可用于当前条目。 但是，可以使用此命令从每个缓存中或者从指定的 Resource Governor 池缓存中手动删除未使用的条目。

　　　　实践参考：DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;

杀连接

【参考文档】

https://blog.csdn.net/kk185800961/article/details/46664421