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

2022-06-23 00:40:47

  1. 内存动态管理视图（DMV）：  
从sys.dm_os_memory_clerks开始。
 
  1. SELECT  [type] ,  
  2.         SUM(virtual_memory_reserved_kb) AS [VM Reserved] ,  
  3.         SUM(virtual_memory_committed_kb) AS [VM Committed] ,  
  4.         SUM(awe_allocated_kb) AS [AWE Allocated] ,  
  5.         SUM(shared_memory_reserved_kb) AS [SM Reserved] ,  
  6.         SUM(shared_memory_committed_kb) AS [SM Committed] ,  
  7.         SUM(multi_pages_kb) AS [Multipage Allocator] ,  
  8.         SUM(single_pages_kb) AS [SinlgePage Allocator],  
  9.         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],  
  10.         SUM(single_pages_kb)+SUM(multi_pages_kb) AS Stolen,  
  11.         SUM(virtual_memory_committed_kb)+SUM(single_pages_kb) AS [SinlgePage Allocator]          
  12. FROM    sys.dm_os_memory_clerks  
  13. GROUP BY [type]  
  14. ORDER BY [type]  


其中type为Memory Clerk的名称，可以知道内存的用途。

  1. 对于得出的数据：  
  1. emoryclerk_sqlbufferpool:正常来说这个汇总值最大。  

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

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

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

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

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

  1. CACHESTORE_CLRPROC：SQLCLR过程缓存。  

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

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

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

  1. USERSTORE_SXC：暂时存放正在执行中的语句的PRC参数，如果参数过长，这部分内存的使用量会比较大。    
 
内存中的数据页由哪些表格组成，各占多少？
sys.dm_os_buffer_descriptors

  1. DECLARE @name NVARCHAR(100)  
  2. DECLARE @cmd NVARCHAR(1000)  
  3. DECLARE dbnames CURSOR  
  4. FOR  
  5.     SELECT  NAME  
  6.     FROM    master.dbo.sysdatabases  
  7. OPEN dbnames  
  8. FETCH NEXT FROM dbnames INTO @name   
  9. WHILE @@FETCH_STATUS = 0   
  10.     BEGIN  
  11.         SET @cmd = 'select b.database_id,db=db_name(b.database_id),p.object_id,p.index_id,buffer_count=count(*) from '  
  12.             + @name + '.sys.allocation_units a, ' + @name  
  13.             + '.sys.dm_os_buffer_descriptors b, ' + @name  
  14.             + '.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('''  
  15.             + @name  
  16.             + ''')  
  17.     group by b.database_id,p.object_id,p.index_id order by b.database_id,buffer_count desc '  
  18.         EXEC (@cmd)  
  19.         FETCH NEXT FROM dbnames INTO @name   
  20.     END  
  21. CLOSE dbnames  
  22. DEALLOCATE dbnames  
  23. GO  
 
会缓存执行计划的对象：
proc：存储过程
prepared：预定义语句
Adhoc：动态查询
ReplProc：复制筛选过程
Trigger：触发器
View：视图
Default：默认值
UsrTab：用户表
SysTab：系统表
Check：Check约束
Rule：规则
 
可以查看各种对象各占多少内存：

  1. SELECT  objtype ,  
  2.         SUM(size_in_bytes) / 1024 AS sum_size_in_KB ,  
  3.         COUNT(bucketid) AS cache_counts  
  4. FROM    sys.dm_exec_cached_plans  
  5. GROUP BY objtype  
分析具体存储哪些对象：

  1. SELECT  usecounts ,  
  2.         refcounts ,  
  3.         size_in_bytes ,  
  4.         cacheobjtype ,  
  5.         objtype ,  
  6.         TEXT  
  7. FROM    sys.dm_exec_cached_plans cp  
  8.         CROSS APPLY sys.dm_exec_sql_text(plan_handle)  
  9. ORDER BY objtype DESC ;  
  10. GO  


--使用DMV分析SQL SERVER 启动以来做read最多的语句

  1. --按照物理读的页面数排序，前50名  
  2. SELECT TOP 50  
  3.         qs.total_physical_reads ,  
  4.         qs.execution_count ,  
  5.         qs.total_physical_reads / qs.execution_count AS [Avg IO] ,  
  6.         SUBSTRING(qt.text, qs.statement_start_offset / 2,  
  7.                   ( CASE WHEN qs.statement_end_offset = -1  
  8.                          THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2  
  9.                          ELSE qs.statement_end_offset  
  10.                     END - qs.statement_start_offset ) / 2) AS query_text ,  
  11.         qt.dbid ,  
  12.         dbname = DB_NAME(qt.dbid) ,  
  13.         qt.objectid ,  
  14.         qs.sql_handle ,  
  15.         qs.plan_handle  
  16. FROM    sys.dm_exec_query_stats qs  
  17.         CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt  
  18. ORDER BY qs.total_physical_reads DESC   
  19. --按照逻辑读的页面数排序，前50名  
  20. SELECT TOP 50  
  21.         qs.total_logical_reads ,  
  22.         qs.execution_count ,  
  23.         qs.total_logical_reads / qs.execution_count AS [Avg IO] ,  
  24.         SUBSTRING(qt.text, qs.statement_start_offset / 2,  
  25.                   ( CASE WHEN qs.statement_end_offset = -1  
  26.                          THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2  
  27.                          ELSE qs.statement_end_offset  
  28.                     END - qs.statement_start_offset ) / 2) AS query_text ,  
  29.         qt.dbid ,  
  30.         dbname = DB_NAME(qt.dbid) ,  
  31.         qt.objectid ,  
  32.         qs.sql_handle ,  
  33.         qs.plan_handle  
  34. FROM    sys.dm_exec_query_stats qs  
  35.         CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt  
  36. ORDER BY qs.total_logical_reads DESC  
 
  1. --用DBCC强制释放部分SQL SERVER 内存缓存：  

  1. DBCC FREESYSTEMCACHE  

  1. DBCC FREESESSIONCACHE  

  1. DBCC FREEPROCCACHE  
    1. --查看操作系统内存状况  
    2. SELECT  total_physical_memory_kb / 1024 AS [物理内存(MB)] ,  
    3.         available_physical_memory_kb / 1024 AS [可用物理内存(MB)] ,  
    4.         system_cache_kb / 1024 AS [系统缓存内存总量(MB)] ,  
    5.         ( kernel_paged_pool_kb + kernel_nonpaged_pool_kb ) / 1024 AS [内核池内存总量(MB)] ,  
    6.         total_page_file_kb / 1024 AS [操作系统报告的提交限制的大小(MB)] ,  
    7.         available_page_file_kb / 1024 AS [未使用的页文件的总量(MB)] ,  
    8.         system_memory_state_desc AS [内存状态说明]  
    9. FROM    sys.dm_os_sys_memory

原文：http://blog.csdn.net/dba_huangzj/article/details/7531384

本文转自欢醉博客园博客，原文链接http://www.cnblogs.com/zhangs1986/p/3672810.html如需转载请自行联系原作者


欢醉

上一篇：leetcode hot 100-20. 有效的括号


下一篇：891. 有效回文 II