sys.dm_os_memory_objects (Transact-SQL)

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Returns memory objects that are currently allocated by SQL Server. You can use sys.dm_os_memory_objects to analyze memory use and to identify possible memory leaks.

Column name Data type Description
memory_object_address varbinary(8) Address of the memory object. Is not nullable.
parent_address varbinary(8) Address of the parent memory object. Is nullable.
pages_allocated_count int Applies to: SQL Server 2008 through SQL Server 2008 R2.

Number of pages that are allocated by this object. Is not nullable.
pages_in_bytes bigint Applies to: SQL Server 2012 through SQL Server 2017.

Amount of memory in bytes that is allocated by this instance of the memory object. Is not nullable.
creation_options int Internal use only. Is nullable.
bytes_used bigint Internal use only. Is nullable.
type nvarchar(60) Type of memory object.

This indicates some component that this memory object belongs to, or the function of the memory object. Is nullable.
name varchar(128) Internal use only. Nullable.
memory_node_id smallint ID of a memory node that is being used by this memory object. Is not nullable.
creation_time datetime Internal use only. Is nullable.
max_pages_allocated_count int Applies to: SQL Server 2008 through SQL Server 2008 R2.

Maximum number of pages allocated by this memory object. Is not nullable.
page_size_in_bytes int Applies to: SQL Server 2012 through SQL Server 2017.

Size of pages in bytes allocated by this object. Is not nullable.
max_pages_in_bytes bigint Maximum amount of memory ever used by this memory object. Is not nullable.
page_allocator_address varbinary(8) Memory address of page allocator. Is not nullable. For more information, see sys.dm_os_memory_clerks (Transact-SQL).
creation_stack_address varbinary(8) Internal use only. Is nullable.
sequence_num int Internal use only. Is nullable.
partition_type int The type of partition:

0 - Non-partitionable memory object

1 - Partitionable memory object, currently not partitioned

2 - Partitionable memory object, partitioned by NUMA node. In an environment with a single NUMA node this is equivalent to 1.

3 - Partitionable memory object, partitioned by CPU.
contention_factor real A value specifying contention on this memory object, with 0 meaning no contention. The value is updated whenever a specified number of memory allocations were made reflecting contention during that period. Applies only to thread-safe memory objects.
waiting_tasks_count bigint Number of waits on this memory object. This counter is incremented whenever memory is allocated from this memory object. The increment is the number of tasks currently waiting for access to this memory object. Applies only to thread-safe memory objects. This is a best effort value without a correctness guarantee.
exclusive_access_count bigint Specifies how often this memory object was accessed exclusively. Applies only to thread-safe memory objects. This is a best effort value without a correctness guarantee.
pdw_node_id int Applies to: Azure SQL Data Warehouse, Parallel Data Warehouse

The identifier for the node that this distribution is on.

partition_type, contention_factor, waiting_tasks_count, and exclusive_access_count are not yet implemented in SQL Database.

Permissions

On SQL Server, requires VIEW SERVER STATE permission.
On SQL Database Premium Tiers, requires the VIEW DATABASE STATE permission in the database. On SQL Database Standard and Basic Tiers, requires the Server admin or an Azure Active Directory admin account.

Remarks

Memory objects are heaps. They provide allocations that have a finer granularity than those provided by memory clerks. SQL Server components use memory objects instead of memory clerks. Memory objects use the page allocator interface of the memory clerk to allocate pages. Memory objects do not use virtual or shared memory interfaces. Depending on the allocation patterns, components can create different types of memory objects to allocate regions of arbitrary size.

The typical page size for a memory object is 8 KB. However, incremental memory objects can have page sizes that range from 512 bytes to 8 KB.

Note

Page size is not a maximum allocation. Instead, page size is allocation granularity that is supported by a page allocator and that is implemented by a memory clerk. You can request allocations greater than 8 KB from memory objects.

Examples

The following example returns the amount of memory allocated by each memory object type.

SELECT SUM (pages_in_bytes) as 'Bytes Used', type   
FROM sys.dm_os_memory_objects  
GROUP BY type   
ORDER BY 'Bytes Used' DESC;  
GO  

See Also

SQL Server Operating System Related Dynamic Management Views (Transact-SQL)
sys.dm_os_memory_clerks (Transact-SQL)