Blog Post

A DMV a Day – Day 4

,

The DMV for Day 4 is sys.dm_os_sys_memory, which is described by BOL as:

Returns memory information from the operating system. SQL Server is bounded by, and responds to, external memory conditions at the operating system level and the physical limits of the underlying hardware. Determining the overall system state is an important part of evaluating SQL Server memory usage.

This DMV was added in SQL Server 2008. It tells you how much physical memory you have and how much is available. It also tells you the same information about the page file. Finally, it tells you whether the operating system is signaling about a low or high memory state. This query works with SQL Server 2008, and 2008 R2. It requires VIEW SERVER STATE permission.

-- 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;

I like to use this query to gather some basic diagnostic information about an instance. It can also help confirm whether SQL Server is under external memory pressure (if the operating system signals that available physical memory is low).

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating