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).

About these ads
This entry was posted in SQL Server 2008. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s