--查看设置的最大最小每次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_countersWHERE counter_name in('Target Server Memory (KB)','Total Server Memory (KB)','Database pages')--系统物理内存使用情况SELECT * FROM sys.dm_os_sys_infoSELECT * FROM sys.dm_os_sys_memorySELECT * FROM sys.dm_os_process_memory--查看当前是否有等待内存授予的进程Select a.*,b.text, c.query_plan from sys.dm_exec_query_memory_grants aCROSS APPLY sys.dm_exec_sql_text(sql_handle) b CROSS APPLY sys.dm_exec_query_plan (plan_handle) c--SqlServer预留和提交内存情况DBCC MEMORYSTATUS; --旧的方法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_mbFROM sys.dm_os_buffer_descriptors(nolock)GROUP BY db_name(database_id) ,database_idORDER 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 au.type = 3 )     UNION ALL    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 = 2) AS obj ON bd.allocation_unit_id = obj.allocation_unit_idWHERE database_id = db_id() AND obj.name NOT LIKE 'sys%'GROUP BY name, index_id ORDER BY cached_pages_count DESC-- 当前各表或索引在缓冲池中的页数量及大小SELECT obj.name ,obj.index_id,i.name ,count(*)AS cached_pages_count ,count(*)*8/1024 AS cached_space_in_mbFROM sys.dm_os_buffer_descriptors AS bd INNER JOIN (    SELECT object_name(object_id) AS name,object_id,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 au.type = 3)    UNION ALL    SELECT object_name(object_id) AS name,object_id,index_id, allocation_unit_id    FROM sys.allocation_units AS au    INNER JOIN sys.partitions AS p   ON au.container_id = p.partition_id AND au.type = 2) AS obj ON bd.allocation_unit_id = obj.allocation_unit_idLEFT JOIN sys.indexes i(nolock) on obj.object_id = i.object_id AND obj.index_id = i.index_id  WHERE database_id = db_id() AND obj.name NOT LIKE 'sys%'GROUP BY obj.name ,obj.index_id,i.nameORDER BY cached_pages_count DESC;-- 统计当前内存分配情况select physical_memory_in_bytes / 1024 / 1024 as physical_memory_mb,virtual_memory_in_bytes / 1024 / 1024 as virtual_memory_mb,bpool_committed * 8 / 1024 as bpool_committed_mb,bpool_commit_target * 8 / 1024 as bpool_target_mb,bpool_visible * 8 / 1024 as bpool_visible_mbfrom sys.dm_os_sys_info--当前内存脏页数量及大小SELECT db_name(database_id) AS 'Database',count(page_id) AS 'Dirty Pages',count(page_id)*8/1024 AS 'Dirty Pages(MB)'FROM sys.dm_os_buffer_descriptors(nolock)WHERE is_modified =1GROUP BY db_name(database_id)ORDER BY 'Dirty Pages' DESC--缓存类型数量大小select cacheobjtype as [Cached Type],COUNT(*) [Number of Plans] ,SUM(CONVERT(BIGINT,size_in_bytes))/1024/1024 [Plan Cache Size(MB)]from sys.dm_exec_cached_plans group by cacheobjtype order by [Plan Cache Size(MB)] desc--缓存对象数量大小select objtype as [Cached Object Type],COUNT(*) as [Number of Plans] ,SUM(CONVERT(BIGINT,size_in_bytes))/1024/1024 [Plan Cache Size(MB)],avg(usecounts) [Avg Use Count] from sys.dm_exec_cached_plans group by objtype order by [Plan Cache Size(MB)] desc--内存对象SELECT top 10 * FROM sys.dm_os_memory_cache_counters(nolock)ORDER BY entries_count DESC;SELECT * FROM sys.dm_os_memory_clerks aWHERE name='TokenAndPermUserStore'SELECT * FROM sys.dm_os_memory_cache_entries(nolock)WHERE [type]='USERSTORE_TOKENPERM' and name='TokenAndPermUserStore'--缓存分析对象SELECT * FROM sys.syscacheobjects SELECT * FROM sys.dm_exec_cached_plans--即时查询优化（运行即生效）EXEC sp_configure 'show advanced options',1RECONFIGUREEXEC sp_configure 'optimize for ad hoc workloads',1RECONFIGURE