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

No comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts