SQL SERVER 内存分配及常见内存问题（2）——DMV查询

SELECT  [type] ,
        SUM(virtual_memory_reserved_kb) AS [VM Reserved] ,
        SUM(virtual_memory_committed_kb) AS [VM Committed] ,
        SUM(awe_allocated_kb) AS [AWE Allocated] ,
        SUM(shared_memory_reserved_kb) AS [SM Reserved] ,
        SUM(shared_memory_committed_kb) AS [SM Committed] ,
        SUM(multi_pages_kb) AS [Multipage Allocator] ,
        SUM(single_pages_kb) AS [SinlgePage Allocator],
        SUM(virtual_memory_reserved_kb)/(CASE WHEN SUM(virtual_memory_committed_kb)=0 THEN 1 ELSE SUM(virtual_memory_committed_kb) END ) AS [Reserved/Commit],
        SUM(single_pages_kb)+SUM(multi_pages_kb) AS Stolen,
        SUM(virtual_memory_committed_kb)+SUM(single_pages_kb) AS [SinlgePage Allocator]        
FROM    sys.dm_os_memory_clerks
GROUP BY [type]
ORDER BY [type]

对于得出的数据：

Memoryclerk_sqlbufferpool:正常来说这个汇总值最大。

CACHESTORE_OBJECP：触发器、存储过程、函数的执行计划缓存。

CACHESTORE_SQLCP：动态T-SQL语句、预编译TSQL语句的执行计划缓存。

CACHESTORE_PHDR：缓存视图、用户自定义函数信息，帮助SQL更快生成执行计划。

CACHESTORE_XPROC：缓存扩展存储过程，sp_executesql，sp_cursor*,sp_Trace*等。

CACHESTORE_TEMPTABLES：缓存临时对象。local temp table 、global temp table 、table variable等。

CACHESTORE_CLRPROC：SQLCLR过程缓存。

CACHESTORE_EVENTS：存储Service Broker的时间和消息。

CACHESTORE_CURSORS：存储所有的游标，包括LocalTSQLcursors、Global TSQL cursor和API cursors等。

USERSTORE_TOKENPERM：保存所有用户的安全上下文及各种跟安全相关的令牌，这些缓存条目用于检查查询累积性的权限。

USERSTORE_SXC：暂时存放正在执行中的语句的PRC参数，如果参数过长，这部分内存的使用量会比较大。

DECLARE @name NVARCHAR(100)
DECLARE @cmd NVARCHAR(1000)
DECLARE dbnames CURSOR
FOR
    SELECT  NAME
    FROM    master.dbo.sysdatabases
OPEN dbnames
FETCH NEXT FROM dbnames INTO @name 
WHILE @@FETCH_STATUS = 0 
    BEGIN
        SET @cmd = 'select b.database_id,db=db_name(b.database_id),p.object_id,p.index_id,buffer_count=count(*) from '
            + @name + '.sys.allocation_units a, ' + @name
            + '.sys.dm_os_buffer_descriptors b, ' + @name
            + '.sys.partitions p where a.allocation_unit_id=b.allocation_unit_id and a.container_id=p.hobt_id and b.database_id=db_id('''
            + @name
            + ''')
	group by b.database_id,p.object_id,p.index_id order by b.database_id,buffer_count desc '
        EXEC (@cmd)
        FETCH NEXT FROM dbnames INTO @name 
    END
CLOSE dbnames
DEALLOCATE dbnames
GO

SELECT  objtype ,
        SUM(size_in_bytes) / 1024 AS sum_size_in_KB ,
        COUNT(bucketid) AS cache_counts
FROM    sys.dm_exec_cached_plans
GROUP BY objtype

SELECT  usecounts ,
        refcounts ,
        size_in_bytes ,
        cacheobjtype ,
        objtype ,
        TEXT
FROM    sys.dm_exec_cached_plans cp
        CROSS APPLY sys.dm_exec_sql_text(plan_handle)
ORDER BY objtype DESC ;
GO