Printed 2017/01/19 08:09AM

A DMV a Day – Day 23

By GlennBerry, 2010/04/23

The DMV for Day 23 is sys.dm_os_process_memory, which is described by BOL as:

Most memory allocations that are attributed to the SQL Server process space are controlled through interfaces that allow for tracking and accounting of those allocations. However, memory allocations might be performed in the SQL Server address space that bypasses internal memory management routines. Values are obtained through calls to the base operating system. They are not manipulated by methods internal to SQL Server, except when it adjusts for locked or large page allocations. All returned values that indicate memory sizes are shown in kilobytes (KB). The column total_virtual_address_space_reserved_kb is a duplicate of virtual_memory_in_bytes from sys.dm_os_sys_info.

This DMV is useful for looking at how much memory the SQL Server process is using, whether “Locked Pages in Memory” is enabled, and whether SQL Server is getting low memory signals from the operating system. This DMV only works with SQL Server 2008, and 2008 R2. It requires VIEW SERVER STATE permission.

-- SQL Server Process Address space info (SQL 2008 and 2008 R2 only)
--(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, 
FROM sys.dm_os_process_memory;

This query shows how much physical memory is in use by SQL Server (which is nice, since you cannot believe Task Manager in most cases). It also shows whether you have “Locked Pages in Memory” enabled (which is true if locked_page_allocations_kb is higher than zero). It also shows whether the SQL Server process has been notified by the operating system that physical or virtual memory is low (at the OS level), meaning that SQL Server should try to trim its working set. This is explained in some more detail here.

Just for reference, DBCC MEMORYSTATUS shows a superset of similar information, but it is more difficult to work with programmatically.

Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.