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

可以看到，USERSTORE_TOKENPERM 和 CACHESTORE_SQLCP 占用最多。

使用 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;

TokenAndPermUserStore 占用最多，TokenAndPermUserStore 是什么？
字面意思可以理解，Token and Permission ，即用户登录和权限的认证信息。
该值的大小和数量一直都在增加：

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

编译计划 （compiled plan）占用多达6GB，可以继续用 sys.dm_exec_cached_plans 查看是什么对象占用最多。

select objtype as [Cached Object Type]
,COUNT(*) as [Number of Plans]
,SUM(CONVERT(BIGINT,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

Adhoc 和 Prepared 占用6GB多的缓存，即时查询和预编译在系统中占用较多，说明有较多SQL语句不能重新使用，每次都需要编译，这部分可以进行参数化或存储过程替代。当然即时查询也可使用系统设置是否进行缓存优化，查看是否开启即时查询优化:

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://blog.csdn.net/kk185800961/article/details/46664421