SQLServer Stolen内存优化案例

最近开始观察到 SQLServer.exe 进程占用内存过大，已达到了60GB，如图：

然而，SQLServer 限制的最大内存为 55GB：

EXEC sp_configure'max server memory (MB)'

可以看到，竟然高出近5GB。
我们知道，’max server memory (MB)’ 限制的是SQLServer buffer pool 的大小，buffer pool 主要缓存数据库中的数据页 ，而数据库中主要是数据的缓存，能控制这部分数据的缓存也相当于控制住了SQLServer 的缓存。

先说明当前数据库情况：
当前版本：
Microsoft SQL Server 2008 (SP4) - 10.0.6000.29 (X64) Sep 3 2014 04:11:34 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

数据库总大小不到50GB,最大内存限制为55GB。

其实数据内存的使用绰绰有余了，什么原因呢？现在开始跟踪。

先打开性能监视器（cmd—>perfmon）监视系统及数据库内存情况：
SQLServer内存是否充足？是SQLServer占用过多内存还是Windows其他程序占用内存？

监视可确定：
当前内存完全够用！
Database pages = 4992221 * 8KB = 37.55GB
Free pages = 1165389 * 8KB = 8.89GB
Total Server Memory (KB) = 55GB
Stolen pages = 1052195 * 8KB = 8GB

除了部分缓存计划，Stolen pages 占用最多！~而 Stolen 中的 MemToLeave 是不受’max server memory (MB)’ 限制的。可以使用系统DMV sys.dm_os_memory_clerks 查看主要哪些类型对象占用的 Stolen 或者 MemToLeave 最多。

SELECT [type],SUM(virtual_memory_reserved_kb) AS [vm reserved],SUM(virtual_memory_committed_kb) AS [vm commited],SUM(awe_allocated_kb) AS [awe allocated],SUM(shared_memory_reserved_kb) AS [sm reserved],SUM(shared_memory_committed_kb) AS [sm committed],SUM(single_pages_kb) AS [Stolen in Buffer Pool],SUM(multi_pages_kb) AS [MemToLeave],SUM(single_pages_kb)+SUM(multi_pages_kb) AS [Stolen],SUM(virtual_memory_committed_kb)+SUM(multi_pages_kb) AS [Buffer Pool] FROM sys.dm_os_memory_clerks GROUP BY [type] ORDER BY [MemToLeave] desc 

使用 DBCC MEMORYSTATUS 查看时，也是这两个Stolen 占用最多。

CACHESTORE_SQLCP(node 0)KB
---------------------------------------- -----------
VMReserved                              0
VMCommitted                             0
LockedPagesAllocated                   0
SMReserved                              0
SMCommitted                             0
SinglePageAllocator                     6398656
MultiPageAllocator                      231576

USERSTORE_TOKENPERM(node 0)KB
---------------------------------------- -----------
VMReserved                              0
VMCommitted                             0
LockedPagesAllocated                   0
SMReserved                              0
SMCommitted                             0
SinglePageAllocator                     859584
MultiPageAllocator                      4236064

现在使用sys.dm_os_memory_cache_counters 查看具体是什么对象

SELECT top 10 * FROM sys.dm_os_memory_cache_counters(nolock) ORDER BY multi_pages_kb DESC;

SELECT SUM(single_pages_kb+multi_pages_kb) AS "CurrentSizeOfTokenCache(kb)" FROM sys.dm_os_memory_clerks WHERE name='TokenAndPermUserStore' SELECT COUNT(*) FROM sys.dm_os_memory_cache_entries(nolock) WHERE [type]='USERSTORE_TOKENPERM' and name='TokenAndPermUserStore' 

具体的缓存对象信息可以查看：

SELECT TOP 10 OBJECT_NAME(est.objectid,EST.dbid) AS ObjectName,omce.name AS cacheName,omce.in_use_count,omce.is_dirty,omce.disk_ios_count,omce.context_switches_count,omce.original_cost,omce.current_cost FROM sys.dm_exec_cached_plans ecp CROSS APPLY sys.dm_exec_sql_text(ecp.plan_handle) est INNER JOIN sys.dm_os_memory_cache_entries omce ON ecp.memory_object_address = omce.memory_object_address WHERE omce.name = 'Object Plans' --WHERE omce.name = 'SQL Plans'

这个怎么释放？使用系统命令 DBCC FREESYSTEMCACHE

DBCC FREESYSTEMCACHE('TokenAndPermUserStore')
--DBCC FREESYSTEMCACHE('SQL Plans')
--DBCC FREESYSTEMCACHE('Object Plans')

执行完成后，内存瞬间降了下来：

虽然是将了下来，但是也会渐渐增加回去。系统提供了几个设置选项，由于介绍这些参数的相关文章太少不详细，本人没太了解，没有使用，如下几个参数：

exec sp_configure 'access check cache quota'
exec sp_configure 'access check cache bucket count'

DBCC TRACEON(4620,-1)

=========================================================
此外，还可以对缓存计划进行优化。现在看看缓存计划情况：

select cacheobjtype as [Cached Type],COUNT(*) as [Number of Plans],SUM(CONVERT(BIGINT,size_in_bytes))/1024/1024 as [Plan Cache Size(MB)] from sys.dm_exec_cached_plans group by cacheobjtype order by [Plan Cache Size(MB)] desc 

select objtype as [Cached Object Type],size_in_bytes))/1024/1024 as [Plan Cache Size(MB)],avg(usecounts) as [Avg Use Count] from sys.dm_exec_cached_plans group by objtype order by [Plan Cache Size(MB)] desc

EXEC sp_configure'optimize for ad hoc workloads'

下面开启优化：

EXEC sp_configure 'show advanced options',1 RECONFIGURE EXEC sp_configure 'optimize for ad hoc workloads',1 RECONFIGURE 

这不会使即时查询的缓存计划立即降下来，过几天观察，发现少了1GB+。
好了，暂时是缓解了，因为数据库的连接信息太多和一些SQL语句的直接执行，使缓存也占用挺多。还得继续观察 TokenAndPermUserStore ，因为这部分每次用户连接都会增加。

本文出自“Hello.KK （SQL Server）”的博客，转载请务必保留此出处http://www.voidcn.com/article/p-bcncvsph-vg.html

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!