内存瓶颈——MEMORYCLERK_SQLOPTIMIZER

问题

用户应用报错：

There is insufficient system memory in resource pool 'internal' to run this query

排查

报错是由于内存不足，SQLServer启动后内存开销会很快达到max server memory，所以通过OS看SQLServer内存开销没有意义，我们排查的方向应该是这时候内存被什么占用了/是否有异常/如何优化

我们需要了解SQLOS，它是SQLServer中抽象出的OS包含任务调度、内存管理、异常处理、第三方代码控制等功能，会根据每个过程的特性做内存分类提更详细的信息

SQLOS内存管理记录了很多信息在内部视图中(sys.dm_os_memory_clerks,sys.dm_os_sys_info,sys.dm_os_sys_info,sys.dm_os_ring_buffers,sys.dm_os_memory_nodes...)，并且在异常情况会打印DBCC MEMORYSTATUS的结果到日志中，一般而言如果有现场我们先看现场（利用视图），如果事后分析701会在内存中有memorystatus提供给我们

内存701

SinglePage占了90%

MEMORYCLERK_SQLOPTIMIZER是主要开销

MEMORYCLERK_SQLOPTIMIZER过高一般情况是由于存在大量的AD-HOC

问题再复现时现场可以先查MEMORYCLERK_SQLOPTIMIZER的占用

select SUM(single_pages_kb)*1.0/1024 as total_single_pages_MB,
SUM(multi_pages_kb)*1.0/1024 as total_multi_pages_MB
from sys.dm_os_memory_clerks where type='MEMORYCLERK_SQLOPTIMIZER'

解决建议

方案一：实例级别参数调整，第一次查询不存完整的plan

sp_configure N'show advanced options',1
GO
reconfigure
GO
sp_configure N'optimize for ad hoc workloads',1
GO
sp_configure N'show advanced options',1
GO
reconfigure
GO

方案二：应用做些参数化调整