SqlServer 内存篇（四）—— 各部分内存不足特征及解决方法

最新推荐文章于 2024-09-13 11:27:34 发布

Hehuyi_In

最新推荐文章于 2024-09-13 11:27:34 发布

阅读量4.7k

点赞数

分类专栏：性能 SQLServer

本文链接：https://blog.csdn.net/Hehuyi_In/article/details/91050303

版权

性能同时被 2 个专栏收录

149 篇文章 21 订阅

订阅专栏

SQLServer

142 篇文章 26 订阅

订阅专栏

第二篇中学习了sqlserver内存的主要部分，第三篇中学习了如何分析sqlserver各部分内存使用，下面就来看看各部分缺少内存时的特征及相应解决方法。

一、 database cache

1. 表现特征

这是最常出现问题的部分，如果此部分内存不足，通常会有以下现象：

sqlserver需要经常触发lazy writes，将旧数据、执行计划写回磁盘 -> lazy writes/sec计数器数值高、频率高

经常要从数据文件中读数据，会有很多硬盘读 -> page read/sec计数器经常较高（正常应该接近0）

用户可能经常遇到硬盘读写等待（注意分清是磁盘问题还是内存问题）

由于数据页经常被清除，page life expectancy应该不高 -> page life expectancy计数器值始终较低

由于执行计划经常被清除，stolen部分内存应该不高 -> stolen page大幅降低或者持续在较低值

2. 外部压力

当windows内存不足时，sqlserver可能会压缩自己的内存空间，database cache首当其冲，容易出现内存瓶颈。

判断方法如下：

看Total Server Memory计数器的值有没有较大幅下降
看Available Mbytes有没有降到一个较低的值
若未使用AWE及Lock page in memory技术，可以看Process:Private Bytes - sqlservr和Process:Working Set - sqlservr是否大幅下降（如果用了，这两个值是不准的）

解决方法及建议：

扩服务器内存
建议开启Lock page in memory，32位系统建议开AWE（最好能迁到64位）
建议sqlserver独占服务器，不要与其他应用混用

3. 来自database cache自身使用需求的压力

判断方法如下：

Total Server Memory计数器维持在较高的值（与外部压力最大的区别）
其他特征参考“表现特征”部分，这些是与遇到外部压力共有的特征

解决方法及建议：

1）给sqlserver更多的内存

扩服务器内存
建议开启Lock page in memory，32位系统建议开AWE（最好能迁到64位）
建议sqlserver独占服务器，不要与其他应用混用
如果一个实例上DB过多，可以考虑迁一些到其他实例

2）让sqlserver少用些内存

找到读取数据页最多的sql进行优化，看能否增加过滤条件或添加索引

4. 来自buffer pool中stolen内存的压力

正常情况下，stolen内存不应该给database cache造成太大压力。但有时用户可能开启了大量sqlserver对象没有关闭（例如游标），这些对象都放在buffer pool中，当涨到一定程度时，就会反过来压缩database cache的使用。

分析及解决方法参考后文。

5. 来自multi-page的压力

multi-page与buffer pool共享sqlserver的虚拟地址空间，如果multi-page使用太多，buffer pool地址空间自然就小了。这种一般较少发生，但在以下情况还是可能出现：

32位未开启AWE的sqlserver，可用空间一共只有2G，又使用了-g参数扩展multi-page上限，压缩了buffer pool
64位的sqlserver对multi-page已经没有上限，但如果有内存泄漏非常严重的第三方代码，还是可能出现该问题

解决方法及建议：

去查sys.dm_os_memory_clerks视图找到是哪个clerk用掉了这么多内存，详细分析及解决方法参考后文。

6. 如何发现内存使用较多的语句

1）使用DMV找启动以来read最多的语句

sys.dm_exec_query_stats返回缓存查询计划的聚合性能统计信息，缓存计划中的每个语句对应使用中一行。sqlserver会统计使用这个执行计划的语句自sqlserver启动以来的：运行次数、逻辑读、物理读、cpu、运行时间等。

--按物理读排序
SELECT TOP 50
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text, *
FROM sys.dm_exec_query_stats
ORDER BY total_physical_reads DESC;

--按逻辑读排序
SELECT TOP 50
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text, *
FROM sys.dm_exec_query_stats
ORDER BY total_logical_reads DESC;

但是这个方法有它的缺点：

查询得到的结果不完全可靠。这个视图中的记录生存期与语句的执行计划本身相关，如果sqlserver将执行计划从缓存删除，这些记录也会从该视图删除。可能故障期间有一个消耗页面读非常高的语句，当在DBA查询时记录已被清出内存，导致无法查到异常语句。
视图存储的是sqlserver启动以来的历史信息，其中的值可能不够有针对性。如果问题只发生的特定时间段，其他时间都正常，平均页面读可能会很低。

如果想准确知道某时间段内哪些语句最耗内存资源、每次消耗多少，sql profile可能是更好的选择。

2）使用sqlprofile

用法这里不介绍了。对于记录的结果，sqlserver有一个fn_trace_gettable函数，可以将trace文件中的记录像表格一样查询出来（当然也可以选择直接保存到表里）

select * into sample
from fn_trace_gettable('c:\sample\a.trc',default)
where eventclass in(10,12)
--10代表RPC:Completed，一般是存储过程调用
--12代表SQL:BatchCompleted，一般是T-SQL调用

三、buffer pool中stolen内存压力分析

1. 外部压力

当windows内存不足时，sqlserver可能会压缩自己的内存空间，整个buffer pool的数据都面临着被清理，stolen也不能幸免，容易出现内存瓶颈。

2. 内部压力

1）来自database cache的挤压

如果sqlserver要执行非常大的查询，buffer pool没有足够空余空间，会触发lazy writer清理数据页，stolen用量也会被挤压。

2）stolen对象泄漏

如果客户端有泄漏数据库对象的行为，例如打开游标不关、prepare了语句不unprepare，只要这个连接不logout，sqlserver就无法清理和释放这些对象。对象使用的内存越积越多，最终将导致stolen内存压力。

3. 表现特征

用户提交的请求因缺少内存而不能完成，sqlserver返回错误信息（通常错误号是701，会记在错误日志中）。这类问题对sqlserver影响严重，但症状明显，具体处理方法参见下篇文章。
sqlserver内存空间申请出现瓶颈，遇到内存等待，性能会降低但还能执行。

4. stolen内存相关等待及解决方法

1）CMEMTHREAD

在一个时间点，只有一个连接能往一块缓存区申请/释放内存，当多个用户想要同时往同一块缓存区申请/释放内存时，只有一个连接能成功，其他的必须等待，等待的事件就是CMEMTHREAD。

这种等待通常只发生在并发特别高的sqlserver里，这些并发的连接通常大量使用每次执行都需编译的动态t-sql语句。

解决方法

看能否降低并发量
修改客户端行为，使用存储过程或参数化sql以减少编译量，增加执行计划重用度

2）SOS_RESERVEDMEMBLOCKLIST

如果sql语句包含大量参数、或者in子句包含大量值，它的执行计划可能超过8KB，需要申请multi-page区域来存储，当申请暂时不能得到满足时，等待的就是这个事件。这个问题更常见于32位机器，因为32位机器multi-page区域很小而且远小于buffer pool，如果multi-page有内存压力而buffer pool没有，并不会触发lazy writer刷数据清理内存。

解决方法