SQL Server memory usage query script

Select Type, -- clerk type

Sum (virtual_memory_reserved_kb) as vm_reserved_kb, -- reserved memory

Sum (virtual_memory_committed_kb) as vm_committed_kb, -- Memory submitted

Sum (awe_allocated_kb) as awe_allocated_kb, -- memory used after awe is enabled

Sum (shared_memory_reserved_kb) as sm_reserved_kb, -- shared reserved memory

Sum (shared_memory_committed_kb) as sm_committed_kb, -- shared commit memory

Sum (single_pages_kb) as sinlgepage_kb, -- Memory of stolen in buffer pool

Sum (multi_pages_kb) as multipage_kb -- Memory of memtoleave

From SYS. dm_ OS _memory_clerks

Group
Type order bytype

 

-- Query Buffer Pool/memory to leave

Select

Sum (virtual_memory_reserved_kb) as [reserved],

Sum (virtual_memory_committed_kb) as [Commit],

Sum (virtual_memory_reserved_kb)/sum (virtual_memory_committed_kb) as [Reserved/commit],

Sum (single_pages_kb) + sum (multi_pages_kb)
[Stolen],

Sum (virtual_memory_committed_kb) + sum (single_pages_kb)
[Buffer pool],

Sum (multi_pages_kb) as [memtoleave],

Sum (awe_allocated_kb) as [aweallocated]

From SYS. dm_ OS _memory_clerks

 

-- Query sqlserver memory usage

Select *
From SYS. dm_ OS _process_memory