sqlserver查询内存相关情况_sqlserver查看内存使用情况

 查看设置的最大与最小内存：
 exec sp_configure 'max server memory (MB)'  
 exec sp_configure 'min server memory (MB)'  

 SqlServer目标内存、当前内存 、数据库内存页数：

 SELECT object_name,counter_name,cntr_value,cntr_value/1024/1024 AS [cntr_value(GB)]  
 FROM sys.dm_os_performance_counters  
WHERE counter_name in('Target Server Memory (KB)','Total Server Memory (KB)','Database pages')
  
查看设置的最大与最小内存：

SELECT * FROM sys.dm_os_sys_info  
SELECT * FROM sys.dm_os_sys_memory  
SELECT * FROM sys.dm_os_process_memory 

  查看当前是否有等待内存授予的进程：
Select a.*,b.text, c.query_plan   
from sys.dm_exec_query_memory_grants a  
CROSS APPLY sys.dm_exec_sql_text(sql_handle) b   
CROSS APPLY sys.dm_exec_query_plan (plan_handle) c 

SqlServer预留和提交内存情况：

SELECT  [type]     
,SUM(virtual_memory_reserved_kb) AS [vm reserved]    
,SUM(virtual_memory_committed_kb) AS [vm commited]    
,SUM(awe_allocated_kb) AS [awe allocated]      
,SUM(shared_memory_reserved_kb) AS [sm reserved]    
,SUM(shared_memory_committed_kb) AS [sm committed]    
,SUM(single_pages_kb) AS [Stolen in Buffer Pool]    
,SUM(multi_pages_kb) AS [MemToLeave]    
,SUM(single_pages_kb) + SUM(multi_pages_kb) AS [Stolen]    
,SUM(virtual_memory_committed_kb) + SUM(multi_pages_kb) AS [Buffer Pool]    
FROM sys.dm_os_memory_clerks   
GROUP BY [type]    
ORDER BY [type] desc

当前各数据库buffer pool的分配情况：

SELECT   
CASE database_id   
    WHEN 32767 THEN 'ResourceDb'   
    ELSE db_name(database_id) END AS Database_name  
,count(*) AS cached_pages_count  
,count(*)*8/1024 AS cached_space_in_mb  
,sum(convert(bigint,free_space_in_bytes))/1024/1024 AS free_space_in_mb  
FROM sys.dm_os_buffer_descriptors(nolock)  
GROUP BY db_name(database_id) ,database_id  
ORDER BY cached_pages_count DESC;

当前数据库各表buffer pool的分配情况：
SELECT top(20) name ,index_id ,count(*)AS cached_pages_count   
,count(*)*8/1024 AS cached_space_in_mb   
FROM sys.dm_os_buffer_descriptors AS bd   
INNER JOIN (  
    SELECT object_name(object_id) AS name ,index_id ,allocation_unit_id  
    FROM sys.allocation_units AS au INNER JOIN sys.partitions AS p   
    ON au.container_id = p.hobt_id  AND (au.type = 1 OR