Dynamic Management Views (DMVs) in SQL Server can be quite handy for monitoring and troubleshooting database performance. When it comes to memory management, understanding how our SQL Server instance uses memory can help us identity performance issues, as well as potential fixes.
In this article, we’ll explore four essential DMVs that provide valuable insights into memory usage in SQL Server.
The sys.dm_os_sys_memory View
The sys.dm_os_sys_memory DMV offers a snapshot of the system memory available to SQL Server. It provides information about the total physical memory and available memory on the server.
Example query:
SELECT
total_physical_memory_kb / 1024 AS total_physical_memory_mb,
available_physical_memory_kb / 1024 AS available_physical_memory_mb,
system_cache_kb / 1024 AS system_cache_mb,
system_memory_state_desc AS system_memory_state_desc
FROM sys.dm_os_sys_memory;Example output:
total_physical_memory_mb available_physical_memory_mb system_cache_mb system_memory_state_desc
------------------------ ---------------------------- --------------- ---------------------------------
3147 2014 0 Available physical memory is high
Quick explanation of the above columns:
total_physical_memory_kb: Total amount of physical memory on the server.available_physical_memory_kb: Amount of physical memory available to SQL Server.system_cache_kb: Amount of memory used for file system caching.system_memory_state_desc: Description of the memory state. The value/description in this column is dependent on the combined values of two other columns in this view;system_high_memory_signal_state, andsystem_low_memory_signal_state.
The sys.dm_os_process_memory View
The sys.dm_os_process_memory DMV provides information about the memory usage of the SQL Server process itself. This view is useful for understanding how SQL Server is managing its memory allocations.
Example query:
SELECT
physical_memory_in_use_kb / 1024 AS physical_memory_in_use_mb,
process_physical_memory_low,
process_virtual_memory_low
FROM sys.dm_os_process_memory;Example output:
physical_memory_in_use_mb process_physical_memory_low process_virtual_memory_low
------------------------- --------------------------- --------------------------
4096 false false
Explanation of these columns:
physical_memory_in_use_kb: Amount of physical memory currently used by SQL Server.process_physical_memory_low: Indicates whether the process is responding to low physical memory notification.process_virtual_memory_low: Indicates whether the low virtual memory condition has been detected.
The sys.dm_os_memory_clerks View
The sys.dm_os_memory_clerks DMV provides detailed information about all the memory clerks in SQL Server. Memory clerks are internal memory managers responsible for allocating memory for specific SQL Server components. As Microsoft says:
Every component that allocates a significant amount of memory must create its own memory clerk and allocate all its memory by using the clerk interfaces.
And about memory management in general:
The SQL Server memory manager consists of a three-layer hierarchy. At the bottom of the hierarchy are memory nodes. The middle level consists of memory clerks, memory caches, and memory pools. The top layer consists of memory objects.
Here’s an example of a query against the sys.dm_os_memory_clerks view:
SELECT TOP 10
type,
memory_node_id,
pages_kb / 1024 AS pages_mb,
virtual_memory_reserved_kb / 1024 AS virtual_memory_reserved_mb,
virtual_memory_committed_kb / 1024 AS virtual_memory_committed_mb
FROM sys.dm_os_memory_clerks
ORDER BY pages_kb DESC;
Example output:
type memory_node_id pages_mb virtual_memory_reserved_mb virtual_memory_committed_mb
------------------------- -------------- -------- -------------------------- ---------------------------
CACHESTORE_SQLCP 0 284 0 0
MEMORYCLERK_SQLBUFFERPOOL 0 227 80 10
CACHESTORE_PHDR 0 168 0 0
MEMORYCLERK_SOSNODE 0 36 0 0
CACHESTORE_OBJCP 0 31 0 0
MEMORYCLERK_SQLSTORENG 0 19 40 40
MEMORYCLERK_SQLGENERAL 0 14 0 0
USERSTORE_DBMETADATA 0 9 0 0
USERSTORE_SCHEMAMGR 0 8 0 0
MEMORYCLERK_SQLQUERYPLAN 0 6 0 0
Column info:
type: Type of memory clerk. Every clerk has a specific type (e.g., MEMORYCLERK_SQLBUFFERPOOL for buffer pool)pages_kb: Amount of memory used by the clerk in kilobytes. We can divide this by 1024 to get the amount in megabytes.virtual_memory_reserved_kb: Amount of virtual memory reserved by the clerkvirtual_memory_committed_kb: Amount of virtual memory committed by the clerk
The sys.dm_os_sys_info View
The sys.dm_os_sys_info view returns some useful information about the computer, and about the resources available to and consumed by SQL Server. Included in this view is information about memory usage.
Example:
SELECT
physical_memory_kb / 1024 AS physical_memory_mb,
virtual_memory_kb / 1024 AS virtual_memory_mb,
committed_kb / 1024 AS committed_mb,
committed_target_kb / 1024 AS committed_target_mb
FROM sys.dm_os_sys_info;Example output:
physical_memory_mb virtual_memory_mb committed_mb committed_target_mb
------------------ ----------------- ------------ -------------------
3147 134217727 950 2541
As with the other examples, I’ve divided the kilobyte amounts by 1024 to get the megabytes.
Column info:
physical_memory_kb: Specifies the total amount of physical memory on the machine.virtual_memory_kb: Specifies the total amount of virtual address space available to the process in user mode.committed_kb: Represents the committed memory in kilobytes (KB) in the memory manager. Doesn’t include reserved memory in the memory manager.committed_target_kb: Represents the amount of memory, in kilobytes (KB), that can be consumed by SQL Server memory manager.