[DMV] Look for overall Memory Consumption
-- find consumers with that account for more than 5% of the memory consumption thourgh SPA OR MPA
-- sys.dm_os_memory_clerks
declare @total_alloc bigint
declare @tab table (
type nvarchar(128) collate database_default
,allocated bigint
,vertual_res bigint
,virtual_com bigint
,awe bigint
,shared_res bigint
,shared_com bigint
,topFive nvarchar(128)
,grAND_total bigint
);
SELECT @total_alloc = sum(single_pages_kb + multi_pages_kb + (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL' THEN virtual_memory_committed_kb ELSE 0 END) + shared_memory_committed_kb)
FROM sys.dm_os_memory_clerks
--print 'Total allocated, except BPool: ' + CAST(@total_alloc AS varchar(10)) + ' Kb'
insert into @tab
SELECT type
, sum(single_pages_kb + multi_pages_kb) AS allocated
, sum(virtual_memory_reserved_kb) AS vertual_res
, sum(virtual_memory_committed_kb) AS virtual_com
, sum(awe_allocated_kb) AS awe
, sum(shared_memory_reserved_kb) AS shared_res
, sum(shared_memory_committed_kb) AS shared_com
, case when ((sum(single_pages_kb + multi_pages_kb + (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL' THEN virtual_memory_committed_kb ELSE 0 END) + shared_memory_committed_kb))/(@total_alloc + 0.0)) >= 0.05
then type
else 'Other'
end AS topFive
, (sum(single_pages_kb + multi_pages_kb + (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL' THEN virtual_memory_committed_kb ELSE 0 END) + shared_memory_committed_kb)) AS grAND_total
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY (sum(single_pages_kb + multi_pages_kb + (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL' THEN virtual_memory_committed_kb ELSE 0 END) + shared_memory_committed_kb)) DESC
SELECT * FROM @tab