运维脚本 内存管理统计（5）

转自：https://blog.csdn.net/kk185800961

1. --查看设置的最大最小每次  
2. exec sp_configure ‘max server memory (MB)‘  
3. exec sp_configure ‘min server memory (MB)‘  
5. --SqlServer目标内存、当前内存 、数据库内存页数  
6. SELECT object_name,counter_name,cntr_value,cntr_value/1024/1024 AS [cntr_value(GB)]  
7. FROM sys.dm_os_performance_counters  
8. WHERE counter_name in(‘Target Server Memory (KB)‘,‘Total Server Memory (KB)‘,‘Database pages‘)  
10. --系统物理内存使用情况  
11. SELECT * FROM sys.dm_os_sys_info  
12. SELECT * FROM sys.dm_os_sys_memory  
13. SELECT * FROM sys.dm_os_process_memory  
15. --查看当前是否有等待内存授予的进程  
16. Select a.*,b.text, c.query_plan   
17. from sys.dm_exec_query_memory_grants a  
18. CROSS APPLY sys.dm_exec_sql_text(sql_handle) b   
19. CROSS APPLY sys.dm_exec_query_plan (plan_handle) c 
20.  
21. --SqlServer预留和提交内存情况  
22. DBCC MEMORYSTATUS; --旧的方法  
23. SELECT  [type]     
24. ,SUM(virtual_memory_reserved_kb) AS [vm reserved]    
25. ,SUM(virtual_memory_committed_kb) AS [vm commited]    
26. ,SUM(awe_allocated_kb) AS [awe allocated]      
27. ,SUM(shared_memory_reserved_kb) AS [sm reserved]    
28. ,SUM(shared_memory_committed_kb) AS [sm committed]    
29. ,SUM(single_pages_kb) AS [Stolen in Buffer Pool]    
30. ,SUM(multi_pages_kb) AS [MemToLeave]    
31. ,SUM(single_pages_kb) + SUM(multi_pages_kb) AS [Stolen]    
32. ,SUM(virtual_memory_committed_kb) + SUM(multi_pages_kb) AS [Buffer Pool]    
33. FROM sys.dm_os_memory_clerks   
34. GROUP BY [type]    
35. ORDER BY [type] desc  
37. --当前各数据库buffer pool的分配情况  
38. SELECT   
39. CASE database_id   
40. WHEN 32767 THEN ‘ResourceDb‘   
41. ELSE db_name(database_id) END AS Database_name  
42. ,count(*) AS cached_pages_count  
43. ,count(*)*8/1024 AS cached_space_in_mb  
44. ,sum(convert(bigint,free_space_in_bytes))/1024/1024 AS free_space_in_mb  
45. FROM sys.dm_os_buffer_descriptors(nolock)  
46. GROUP BY db_name(database_id) ,database_id  
47. ORDER BY cached_pages_count DESC;  
49. --当前数据库各表buffer pool的分配情况  
50. SELECT top(20) name ,index_id ,count(*)AS cached_pages_count   
51. ,count(*)*8/1024 AS cached_space_in_mb   
52. FROM sys.dm_os_buffer_descriptors AS bd   
53. INNER JOIN (  
54. SELECT object_name(object_id) AS name ,index_id ,allocation_unit_id  
55. FROM sys.allocation_units AS au INNER JOIN sys.partitions AS p   
56. ON au.container_id = p.hobt_id  AND (au.type = 1 OR au.type = 3 )   
57. UNION ALL  
58. SELECT object_name(object_id) AS name,index_id, allocation_unit_id  
59. FROM sys.allocation_units AS au INNER JOIN sys.partitions AS p   
60. ON au.container_id = p.hobt_id  AND au.type = 2  
61. ) AS obj ON bd.allocation_unit_id = obj.allocation_unit_id  
62. WHERE database_id = db_id() AND obj.name NOT LIKE ‘sys%‘  
63. GROUP BY name, index_id   
64. ORDER BY cached_pages_count DESC  
66. -- 当前各表或索引在缓冲池中的页数量及大小  
67. SELECT obj.name ,obj.index_id,i.name ,count(*)AS cached_pages_count ,count(*)*8/1024 AS cached_space_in_mb  
68. FROM sys.dm_os_buffer_descriptors AS bd   
69. INNER JOIN (  
70. SELECT object_name(object_id) AS name,object_id,index_id ,allocation_unit_id  
71. FROM sys.allocation_units AS au  
72. INNER JOIN sys.partitions AS p ON au.container_id = p.hobt_id AND (au.type = 1 OR au.type = 3)  
73. UNION ALL  
74. SELECT object_name(object_id) AS name,object_id,index_id, allocation_unit_id  
75. FROM sys.allocation_units AS au  
76. INNER JOIN sys.partitions AS p   ON au.container_id = p.partition_id AND au.type = 2  
77. ) AS obj ON bd.allocation_unit_id = obj.allocation_unit_id  
78. LEFT JOIN sys.indexes i(nolock) on obj.object_id = i.object_id AND obj.index_id = i.index_id    
79. WHERE database_id = db_id() AND obj.name NOT LIKE ‘sys%‘  
80. GROUP BY obj.name ,obj.index_id,i.name  
81. ORDER BY cached_pages_count DESC;  
83. -- 统计当前内存分配情况  
84. select   
85. physical_memory_in_bytes / 1024 / 1024 as physical_memory_mb,  
86. virtual_memory_in_bytes / 1024 / 1024 as virtual_memory_mb,  
87. bpool_committed * 8 / 1024 as bpool_committed_mb,  
88. bpool_commit_target * 8 / 1024 as bpool_target_mb,  
89. bpool_visible * 8 / 1024 as bpool_visible_mb  
90. from sys.dm_os_sys_info  
92. --当前内存脏页数量及大小  
93. SELECT db_name(database_id) AS ‘Database‘  
94. ,count(page_id) AS ‘Dirty Pages‘  
95. ,count(page_id)*8/1024 AS ‘Dirty Pages(MB)‘  
96. FROM sys.dm_os_buffer_descriptors(nolock)  
97. WHERE is_modified =1  
98. GROUP BY db_name(database_id)  
99. ORDER BY ‘Dirty Pages‘ DESC  
101. --缓存类型数量大小  
102. select cacheobjtype as [Cached Type]  
103. ,COUNT(*) [Number of Plans]   
104. ,SUM(CONVERT(BIGINT,size_in_bytes))/1024/1024 [Plan Cache Size(MB)]  
105. from sys.dm_exec_cached_plans   
106. group by cacheobjtype   
107. order by [Plan Cache Size(MB)] desc  
109. --缓存对象数量大小  
110. select objtype as [Cached Object Type]  
111. ,COUNT(*) as [Number of Plans]   
112. ,SUM(CONVERT(BIGINT,size_in_bytes))/1024/1024 [Plan Cache Size(MB)]  
113. ,avg(usecounts) [Avg Use Count]   
114. from sys.dm_exec_cached_plans   
115. group by objtype   
116. order by [Plan Cache Size(MB)] desc  
118. --内存对象  
119. SELECT top 10 * FROM sys.dm_os_memory_cache_counters(nolock)  
120. ORDER BY entries_count DESC;  
121. SELECT * FROM sys.dm_os_memory_clerks a  
122. WHERE name=‘TokenAndPermUserStore‘  
123. SELECT * FROM sys.dm_os_memory_cache_entries(nolock)  
124. WHERE [type]=‘USERSTORE_TOKENPERM‘ and name=‘TokenAndPermUserStore‘  
126. --缓存分析对象  
127. SELECT * FROM sys.syscacheobjects   
128. SELECT * FROM sys.dm_exec_cached_plans  
130. --即时查询优化（运行即生效）  
131. EXEC sp_configure ‘show advanced options‘,1  
132. RECONFIGURE  
133. EXEC sp_configure ‘optimize for ad hoc workloads‘,1  
134. RECONFIGURE  

运维脚本 内存管理统计（5）

原文：https://www.cnblogs.com/gered/p/9052236.html