前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >SQL SERVER 内存分配及常见内存问题 DMV查询

SQL SERVER 内存分配及常见内存问题 DMV查询

作者头像
欢醉
发布2018-01-22 15:08:47
1.9K0
发布2018-01-22 15:08:47
举报
文章被收录于专栏:james大数据架构
  1. 内存动态管理视图(DMV):  

从sys.dm_os_memory_clerks开始。

代码语言:js
复制
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的名称,可以知道内存的用途。

  1. 对于得出的数据:  
  2. emoryclerk_sqlbufferpool:正常来说这个汇总值最大。  
  3. CACHESTORE_OBJECP:触发器、存储过程、函数的执行计划缓存。  
  4. CACHESTORE_SQLCP:动态T-SQL语句、预编译TSQL语句的执行计划缓存。  
  5. CACHESTORE_PHDR:缓存视图、用户自定义函数信息,帮助SQL更快生成执行计划。  
  6. CACHESTORE_XPROC:缓存扩展存储过程,sp_executesql,sp_cursor*,sp_Trace*等。  
  7. CACHESTORE_TEMPTABLES:缓存临时对象。local temp table 、global temp table 、table variable等。  
  8. CACHESTORE_CLRPROC:SQLCLR过程缓存。  
  9. CACHESTORE_EVENTS:存储Service Broker的时间和消息。  
  10. CACHESTORE_CURSORS:存储所有的游标,包括LocalTSQLcursors、Global TSQL cursor和API cursors等。  
  11. USERSTORE_TOKENPERM:保存所有用户的安全上下文及各种跟安全相关的令牌,这些缓存条目用于检查查询累积性的权限。  
  12. USERSTORE_SXC:暂时存放正在执行中的语句的PRC参数,如果参数过长,这部分内存的使用量会比较大。  

内存中的数据页由哪些表格组成,各占多少?

代码语言:js
复制
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:规则

可以查看各种对象各占多少内存:

代码语言:js
复制
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  

  分析具体存储哪些对象:

代码语言:js
复制
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最多的语句

  1. --按照物理读的页面数排序,前50名
  2. SELECT TOP 50  
代码语言:js
复制
        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 
  1. --按照逻辑读的页面数排序,前50名
  2. SELECT TOP 50  
代码语言:js
复制
        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  
代码语言:js
复制
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
本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2014-04-18 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档