sys.dm_db_xtp_memory_consumers (Transact-SQL)

Applies to: 👁 Image
SQL Server 👁 Image
Azure SQL Database 👁 Image
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.

The following table describes the memory consumers specified in the memory_consumer_type column:

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