SQL SERVER 内存分配及常见内存问题 DMV查询-阿里云开发者社区

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

2017-11-08 1077

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

RDS SQL Server Serverless，2-4RCU 50GB 3个月

云数据库 RDS SQL Server，基础系列 2核4GB

简介：

 
 内存动态管理视图（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
本文转自欢醉博客园博客，原文链接http://www.cnblogs.com/zhangs1986/p/3672810.html如需转载请自行联系原作者

欢醉

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

热门文章

最新文章

相关课程

相关电子书

相关实验场景