--缓冲区池消耗内存总量
SELECTSUM(multi_pages_kb + virtual_memory_reserved_kb + shared_memory_reserved_kb
+awe_allocated_kb) AS[Used by BPool,kb]
FROM sys.dm_os_memory_clerks
WHERE type='MEMORYCLERK_SQLBUFFERPOOL'
--查询确认哪些内部组件窃取了缓冲区池中大部分的页面
SELECTTOP10 type,
SUM(single_pages_kb) AS stolen_mem_kb
FROM sys.dm_os_memory_clerks
GROUPBY type
ORDERBYSUM(single_pages_kb) DESC
--缓冲区池外分配了内存的内部组件
SELECTTOP10 type,
SUM(multi_pages_kb) AS menory_allocated_kb
FROM sys.dm_os_memory_clerks
GROUPBY type
ORDERBYSUM(multi_pages_kb) DESC
SELECTSUM(multi_pages_kb + virtual_memory_reserved_kb + shared_memory_reserved_kb
+awe_allocated_kb) AS[Used by BPool,kb]
FROM sys.dm_os_memory_clerks
WHERE type='MEMORYCLERK_SQLBUFFERPOOL'
--查询确认哪些内部组件窃取了缓冲区池中大部分的页面
SELECTTOP10 type,
SUM(single_pages_kb) AS stolen_mem_kb
FROM sys.dm_os_memory_clerks
GROUPBY type
ORDERBYSUM(single_pages_kb) DESC
--缓冲区池外分配了内存的内部组件
SELECTTOP10 type,
SUM(multi_pages_kb) AS menory_allocated_kb
FROM sys.dm_os_memory_clerks
GROUPBY type
ORDERBYSUM(multi_pages_kb) DESC