sys.dm_os_memory_objects (Transact-SQL)
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
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.
Note
To call this from Azure Synapse Analytics or Analytics Platform System (PDW), use the name sys.dm_pdw_nodes_os_memory_objects. This syntax is not supported by serverless SQL pool in Azure Synapse Analytics.
| 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 (10.0.x) through SQL Server 2008 R2 (10.50.x). Number of pages that are allocated by this object. Is not nullable. |
| pages_in_bytes | bigint | Applies to: SQL Server 2012 (11.x) and later. 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 (10.0.x) through SQL Server 2008 R2 (10.50.x). Maximum number of pages allocated by this memory object. Is not nullable. |
| page_size_in_bytes | int | Applies to: SQL Server 2012 (11.x) and later. 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 | Applies to: SQL Server 2016 (13.x) and later. 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 | Applies to: SQL Server 2016 (13.x) and later. 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 | Applies to: SQL Server 2016 (13.x) and later. 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 | Applies to: SQL Server 2016 (13.x) and later. 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 Synapse Analytics, Analytics Platform System (PDW) 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 and SQL Managed Instance, requires VIEW SERVER STATE permission.
On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account, the Microsoft Entra admin account, or membership in the ##MS_ServerStateReader## server role is required. On all other SQL Database service objectives, either the VIEW DATABASE STATE permission on the database, or membership in the ##MS_ServerStateReader## server role is required.
Permissions for SQL Server 2022 and later
Requires VIEW SERVER PERFORMANCE STATE permission on the server.
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)
Feedback
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for