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
