Sql Server : Memory

This SQL query retrieves information about the system memory on a SQL Server instance using the sys.dm_os_sys_memory dynamic management view.

SELECT total_physical_memory_kb/1024 AS [Physical Memory (MB)], 
       available_physical_memory_kb/1024 AS [Available Memory (MB)], 
       total_page_file_kb/1024 AS [Total Page File (MB)], 
         available_page_file_kb/1024 AS [Available Page File (MB)], 
         system_cache_kb/1024 AS [System Cache (MB)],
       system_memory_state_desc AS [System Memory State]
FROM sys.dm_os_sys_memory WITH (NOLOCK) -- OPTION (RECOMPILE);

You want to see “Available physical memory is high” for System Memory State. This indicates that you are not under memory pressure.

Possible System Memory State values;

 - Available physical memory is high
 - Physical memory usage is steady
 - Available physical memory is low
 - Available physical memory is running low
 - Physical memory state is transitioning

This SQL query retrieves information about the SQL Server process memory usage using the sys.dm_os_process_memory dynamic management view.

SELECT physical_memory_in_use_kb/1024 AS [SQL Server Memory Usage (MB)],
         locked_page_allocations_kb/1024 AS [SQL Server Locked Pages Allocation (MB)],
       large_page_allocations_kb/1024 AS [SQL Server Large Pages Allocation (MB)], 
         page_fault_count, memory_utilization_percentage, available_commit_limit_kb, 
         process_physical_memory_low…