SQL Server Administration: SQL 2008 – SQL 2012) Use the new dynamic management view sys.dm_os_sys_memory to view memory information from the operating system. If the System Memory State = “Available physical memory is high”, this is good and indicates that the system is not experiencing external memory pressure. For more information check out
**HT to Glen Berry for the query: http://www.sqlskills.com/blogs/glenn/
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)