DMVs for Memory

Today we will look into DMVs related to memory

First we will look into sys.dm_os_sys_memory which will give us memory related basic informatio for the system
This will give us detail for physical memory, available memory , toatl page file and available page file and
high/low memory status. If  memory status is low then it means system is facing memory pressure.

SELECT total_physical_memory_kb / ( 1024.0 * 1024 )     total_physical_memory_gb,

       available_physical_memory_kb / ( 1024.0 * 1024 ) available_physical_memory_gb,

       total_page_file_kb / ( 1024.0 * 1024 )           total_page_file_gb,

       available_page_file_kb / ( 1024.0 * 1024 )       available_page_file_gb,

       system_high_memory_signal_state,

       system_low_memory_signal_state,

       system_memory_state_desc

FROM   sys.dm_os_sys_memory

dm_os_process_memory
This dmv is supprted on SQL 2008 and later versions
it provides some  more detailed information about memory in sql server
All details are in kb

SELECT physical_memory_in_use_kb,

       large_page_allocations_kb,

       locked_page_allocations_kb,

       total_virtual_address_space_kb,

       virtual_address_space_reserved_kb,

       virtual_address_space_committed_kb,

       virtual_address_space_available_kb,

       page_fault_count,

       memory_utilization_percentage,

       available_commit_limit_kb,

       process_physical_memory_low,

       process_virtual_memory_low

FROM   sys.dm_os_process_memory

This will give us physical memory , locked page allocation (should be 0 if AWE is not enabled) ,
total , reserved, committed and available virtual address space, page fault count , memory utilization percentage and process physical memory low if the server is facing memory pressure