SQL SERVER 内存分配及常见内存问题 DMV查询
浏览数：40 /
时间：2015年06月12日
- 内存动态管理视图（DMV）：
从sys.dm_os_memory_clerks开始。
- 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]
其中type为Memory Clerk的名称，可以知道内存的用途。
- 对于得出的数据：
- emoryclerk_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参数，如果参数过长，这部分内存的使用量会比较大。
sys.dm_os_buffer_descriptors
- 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
会缓存执行计划的对象：
proc：存储过程
prepared：预定义语句
Adhoc：动态查询
ReplProc：复制筛选过程
Trigger：触发器
View：视图
Default：默认值
UsrTab：用户表
SysTab：系统表
Check：Check约束
Rule：规则
- 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
--使用DMV分析SQL SERVER 启动以来做read最多的语句
- --按照物理读的页面数排序，前50名
- SELECT TOP 50
- qs.total_physical_reads ,
- qs.execution_count ,
- qs.total_physical_reads / qs.execution_count AS [Avg IO] ,
- SUBSTRING(qt.text, qs.statement_start_offset / 2,
- ( CASE WHEN qs.statement_end_offset = -1
- THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
- ELSE qs.statement_end_offset
- END - qs.statement_start_offset ) / 2) AS query_text ,
- qt.dbid ,
- dbname = DB_NAME(qt.dbid) ,
- qt.objectid ,
- qs.sql_handle ,
- qs.plan_handle
- FROM sys.dm_exec_query_stats qs
- CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
- ORDER BY qs.total_physical_reads DESC
- --按照逻辑读的页面数排序，前50名
- SELECT TOP 50
- qs.total_logical_reads ,
- qs.execution_count ,
- qs.total_logical_reads / qs.execution_count AS [Avg IO] ,
- SUBSTRING(qt.text, qs.statement_start_offset / 2,
- ( CASE WHEN qs.statement_end_offset = -1
- THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
- ELSE qs.statement_end_offset
- END - qs.statement_start_offset ) / 2) AS query_text ,
- qt.dbid ,
- dbname = DB_NAME(qt.dbid) ,
- qt.objectid ,
- qs.sql_handle ,
- qs.plan_handle
- FROM sys.dm_exec_query_stats qs
- CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
- ORDER BY qs.total_logical_reads DESC
- --用DBCC强制释放部分SQL SERVER 内存缓存：
- DBCC FREESYSTEMCACHE
- DBCC FREESESSIONCACHE
- DBCC FREEPROCCACHE
- --查看操作系统内存状况
- SELECT total_physical_memory_kb / 1024 AS [物理内存(MB)] ,
- available_physical_memory_kb / 1024 AS [可用物理内存(MB)] ,
- system_cache_kb / 1024 AS [系统缓存内存总量(MB)] ,
- ( kernel_paged_pool_kb + kernel_nonpaged_pool_kb ) / 1024 AS [内核池内存总量(MB)] ,
- total_page_file_kb / 1024 AS [操作系统报告的提交限制的大小(MB)] ,
- available_page_file_kb / 1024 AS [未使用的页文件的总量(MB)] ,
- system_memory_state_desc AS [内存状态说明]
- FROM sys.dm_os_sys_memory
原文：http://blog.csdn.net/dba_huangzj/article/details/7531384
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
