sys.dm_db_xtp_memory_consumers (Transact-SQL)
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Reports the database-level memory consumers in the In-Memory OLTP database engine. The view returns a row for each memory consumer that the database engine uses. Use this DMV to see how the memory is distributed across different internal objects.
For more information, see In-Memory OLTP overview and usage scenarios.
Note
The output of this system dynamic management view may be different, depending on the version of SQL Server installed.
| Column name | Data type | Description |
|---|---|---|
memory_consumer_id |
bigint | ID (internal) of the memory consumer. |
memory_consumer_type |
int | The type of memory consumer: 0 = Aggregation. (Aggregates memory usage of two or more consumers. It shouldn't be displayed.) 2 = VARHEAP (Tracks memory consumption for a variable-length heap.)3 = HASH (Tracks memory consumption for an index.)4 = PGPOOL (DB page pool: Tracks memory consumption for a database page pool used for runtime operations. For example, table variables and some serializable scans. There is only one memory consumer of this type per database.) |
memory_consumer_type_desc |
nvarchar(64) | Type of memory consumer: VARHEAP, HASH, or PGPOOL.0 - (Shouldn't be displayed) 2 - VARHEAP3 - HASH4 - PGPOOL |
memory_consumer_desc |
nvarchar(64) | Description of the memory consumer instance. For more information, review the table that follows. |
object_id |
bigint | The object ID to which the allocated memory is attributed. A negative value for system objects. |
xtp_object_id |
bigint | The In-Memory OLTP object ID that corresponds to the memory-optimized table. |
index_id |
int | The index ID of the consumer (if any). NULL for base tables. |
allocated_bytes |
bigint | Number of bytes reserved for this consumer. |
used_bytes |
bigint | Bytes used by this consumer. Applies only to VARHEAP. |
allocation_count |
int | Number of allocations. |
partition_count |
int | Internal use only. |
sizeclass_count |
int | Internal use only. |
min_sizeclass |
int | Internal use only. |
max_sizeclass |
int | Internal use only. |
memory_consumer_address |
varbinary | Internal address of the consumer. For internal use only. |
The following table describes the memory consumers specified in the memory_consumer_type column:
| Memory consumer | Description | Type |
|---|---|---|
256K page pool |
Memory pool used during checkpoint activity. | PGPOOL |
4K page pool |
Memory pool used during checkpoint activity. | PGPOOL |
Checkpoint table |
Internal use only. | VARHEAP |
Ckpt file table |
Internal use only. | VARHEAP |
Ckpt file watermark table |
Internal use only. | VARHEAP |
Database internal heap |
Used to allocate database data that are included in memory dumps, and don't include user data. | VARHEAP |
Database user heap |
Used to allocate user data for a database (rows). | VARHEAP |
Encryption table |
Internal use only. | VARHEAP |
Hash index |
Tracks memory consumption for an index. The object_id indicates the table and the index_id of the hash index itself. |
HASH |
Large Rows File table |
Internal use only. | VARHEAP |
LOB Page Allocator |
Heap memory used by large rows. | VARHEAP |
Logical range index partition table |
Internal use only. | VARHEAP |
Logical root fragment table |
Internal use only. | VARHEAP |
Logical Root table |
Internal use only. | VARHEAP |
Logical Sequence Object table |
Internal use only. | VARHEAP |
Physical range index partition table |
Internal use only. | VARHEAP |
Physical root fragment table |
Internal use only. | VARHEAP |
Physical Root table |
Internal use only. | VARHEAP |
Physical Sequence object table |
Internal use only. | VARHEAP |
Range index heap |
Private heap used by range index to allocate Bw-tree pages. | VARHEAP |
Storage internal heap |
Internal use only. | VARHEAP |
Storage user heap |
Internal use only. | VARHEAP |
Table heap |
Heap memory used by In-Memory tables. | VARHEAP |
Tail cache 256K page pool |
Internal use only. | PGPOOL |
Tx Segment table |
Internal use only. | VARHEAP |
Remarks
When a memory-optimized table has a columnstore index, the system uses some internal tables, which consume some memory, to track data for the columnstore index. For details about these internal tables and sample queries showing their memory consumption, see sys.memory_optimized_tables_internal_attributes (Transact-SQL).
Permissions
All rows are returned if you have VIEW DATABASE STATE permission on the current database. Otherwise, an empty rowset is returned.
If you don't have VIEW DATABASE permission, all columns are returned for rows in tables that you have SELECT permission on.
On SQL Server 2019 (15.x) and previous versions, system tables are returned only for users with VIEW DATABASE STATE permission.
For SQL Server 2022 (16.x) and later versions, you require VIEW DATABASE PERFORMANCE STATE permission on the database.
Examples
Query memory consumers in the current database
Run the following query against the sample WideWorldImporters database, which contains memory-optimized tables:
SELECT CONVERT(CHAR(10), OBJECT_NAME(object_id)) AS Name,
memory_consumer_type_desc,
memory_consumer_desc,
object_id,
index_id,
allocated_bytes,
used_bytes
FROM sys.dm_db_xtp_memory_consumers;
Here's the result set.
Name memory_consumer_type_desc memory_consumer_desc object_id index_id allocated_bytes used_bytes
---------- ------------------------- -------------------------------------- ----------- ----------- ----------------- ------------
NULL VARHEAP Range index heap -15 1 131072 176
NULL VARHEAP Physical range index partition table -15 NULL 0 0
NULL VARHEAP Range index heap -14 2 131072 192
NULL VARHEAP Range index heap -14 1 131072 208
NULL VARHEAP Large Rows File table -14 NULL 0 0
NULL HASH Hash index -13 1 2048 2048
NULL VARHEAP Encryption table -13 NULL 0 0
NULL HASH Hash index -10 2 32768 32768
NULL HASH Hash index -10 1 32768 32768
NULL VARHEAP Tx Segment table -10 NULL 65536 544
NULL HASH Hash index -11 1 32768 32768
NULL VARHEAP Checkpoint table -11 NULL 131072 320
NULL HASH Hash index -12 1 8192 8192
NULL VARHEAP Ckpt file table -12 NULL 131072 3120
NULL HASH Hash index -9 1 2048 2048
NULL VARHEAP Ckpt file watermark table -9 NULL 131072 1280
NULL VARHEAP Range index heap -7 1 262144 976
NULL VARHEAP Physical Sequence Object table -7 NULL 65536 864
NULL HASH Hash index -3 1 2048 2048
NULL VARHEAP Physical root fragment table -3 NULL 0 0
NULL HASH Hash index 0 2 8192 8192
NULL HASH Hash index 0 1 32768 32768
NULL VARHEAP Physical Root table NULL NULL 327680 12160
NULL PGPOOL Tail cache 256K page pool 0 NULL 262144 262144
NULL PGPOOL 256K page pool 0 NULL 35389440 18874368
NULL PGPOOL 64K page pool 0 NULL 131072 65536
NULL PGPOOL 4K page pool 0 NULL 49152 40960
NULL VARHEAP Storage internal heap NULL NULL 786432 4816
NULL VARHEAP Storage user heap NULL NULL 262144 22496
ColdRoomTe VARHEAP Range index heap 1179151246 3 196608 800
ColdRoomTe VARHEAP Range index heap 1179151246 2 196608 800
memory_opt VARHEAP Range index heap 1211151360 2 131072 208
VehicleTem VARHEAP Range index heap 1243151474 2 11796480 1181824
ColdRoomTe VARHEAP Table heap 1179151246 NULL 65536 384
memory_opt VARHEAP Table heap 1211151360 NULL 0 0
VehicleTem VARHEAP Table heap 1243151474 NULL 33423360 32802112
VehicleTem VARHEAP Range index heap 1243151474 2 131072 160
VehicleTem VARHEAP LOB Page Allocator 1243151474 NULL 0 0
VehicleTem VARHEAP Table heap 1243151474 NULL 0 0
NULL VARHEAP Range index heap -15 1 327680 176
NULL VARHEAP Logical range index partition table -15 NULL 0 0
NULL HASH Hash index -7 1 32768 32768
NULL VARHEAP Logical Sequence Object table -7 NULL 65536 600
NULL HASH Hash index -3 1 2048 2048
NULL VARHEAP Logical root fragment table -3 NULL 0 0
NULL HASH Hash index 0 1 32768 32768
NULL VARHEAP Logical Root table NULL NULL 327680 11120
NULL PGPOOL Tail cache 256K page pool 0 NULL 262144 0
NULL PGPOOL 256K page pool 0 NULL 10485760 0
NULL PGPOOL 64K page pool 0 NULL 131072 0
NULL PGPOOL 4K page pool 0 NULL 32768 0
NULL VARHEAP Database internal heap NULL NULL 1048576 8016
NULL VARHEAP Database user heap NULL NULL 65536 1024
The total memory allocated and used from this DMV is same as the object level in sys.dm_db_xtp_table_memory_stats.
SELECT SUM(allocated_bytes) / (1024 * 1024) AS total_allocated_MB,
SUM(used_bytes) / (1024 * 1024) AS total_used_MB
FROM sys.dm_db_xtp_memory_consumers;
Here's the result set.
total_allocated_MB total_used_MB
------------------ --------------------
92 51
Related content
- Introduction to Memory-Optimized Tables
- Memory-Optimized Table Dynamic Management Views (Transact-SQL)
- In-Memory OLTP overview and usage scenarios
- Optimize performance by using in-memory technologies in Azure SQL Database
- Optimize performance by using in-memory technologies in Azure SQL Managed Instance