如何检查SQL Server 内存瓶颈

 DBCC memorystatus
 --查询缓冲区池消耗的内存总量包括AWE
 
 SELECT
 SUM(multi_pages_kb+virtual_memory_committed_kb+shared_memory_committed_kb+
 awe_allocated_kb) AS [Used by BPool, kb]
 from sys.dm_os_memory_clerks WHERE type='MEMORYCLERK_SQLBUFFERPOOL'
 
 --确认那些内部组建窃取了缓冲区池中的大部分的页面
 
 SELECT TOP 10 type, SUM(single_pages_kb) AS stolen_mem_kb
 from sys.dm_os_memory_clerks
 GROUP BY type ORDER BY SUM(single_pages_kb) DESC
 
 --确认在缓冲区池外分配了内存的内部组建
 
 SELECT type, SUM(multi_pages_kb) AS memory_allocated_kb
 from sys.dm_os_memory_clerks
 WHERE multi_pages_kb!=0
 GROUP BY type