-- Good basic information about memory amounts and state

SELECT total_physical_memory_kb, available_physical_memory_kb,

total_page_file_kb, available_page_file_kb,

system_memory_state_desc

FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);

-- You want to see "Available physical memory is high"

-- SQL Server Process Address space info

--(shows whether locked pages is enabled, among other things)

SELECT physical_memory_in_use_kb,locked_page_allocations_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 WITH (NOLOCK) OPTION (RECOMPILE);

-- You want to see 0 for process_physical_memory_low

-- You want to see 0 for process_virtual_memory_low