sys.dm_db_xtp_memory_consumers (Transact-SQL)


Updated: August 29, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2014)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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 (In-Memory Optimization).

Column nameData typeDescription
memory_consumer_idbigintID (internal) of the memory consumer.
memory_consumer_typeintThe type of memory consumer:

0=Aggregation. (Aggregates memory usage of two or more consumers. It should not be displayed.)

2=VARHEAP (Tracks memory consumption for a variable-length heap.)

3=HASH (Tracks memory consumption for an index.)

5=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_descnvarchar(64)Type of memory consumer: VARHEAP, HASH, or PGPOOL.

0 – (It should not be displayed.)


3 - HASH

memory_consumer_descnvarchar(64)Description of the memory consumer instance:

Database heap. Used to allocate user data for a database (rows).
Database System heap. Used to allocate database data that will be included in memory dumps and do not include user data.
Range index heap. Private heap used by range index to allocate BW pages.

HASH: No description since the object_id indicates the table and the index_id the hash index itself.

PGPOOL: For the database there is only one page pool Database 64K page pool.
object_idbigintThe object ID to which the allocated memory is attributed. A negative value for system objects.
xtp_object_idbigintThe object ID for the memory-optimized table.
index_idintThe index ID of the consumer (if any). NULL for base tables.
allocated_bytesbigintNumber of bytes reserved for this consumer.
used_bytesbigintBytes used by this consumer. Applies only to varheap.
allocation_countintNumber of allocations.
partition_countintInternal use only.
sizeclass_countintInternal use only.
min_sizeclassintInternal use only.
max_sizeclassintInternal use only.
memory_consumer_addressvarbinaryInternal address of the consumer. For internal use only.
xtp_object_idbigintThe in-memory OLTP object ID that corresponds to the memory-optimized table.

In the output, the allocators at database levels refer to user tables, indexes, and system tables. VARHEAP with object_id = NULL refers to memory allocated to tables with variable length columns.

All rows are returned if you have VIEW DATABASE STATE permission on the current database. Otherwise, an empty rowset is returned.

If you do not have VIEW DATABASE permission, all columns will be returned for rows in tables that you have SELECT permission on.

System tables are returned only for users with VIEW DATABASE STATE permission.

When a memory-optimized table has a columnstore index, the system uses some internal indexes, which consume some memory, to track internal data for the columnstore index.

  • A tail index is an in-memory nonclustered index desgined to track which rows in the in-memory table are currently not in the columnstore index. This uses one of the fixed number of index slots on the memory-optimized table, and it shows up as an index on the memory-optimized table. The index ID for the tail index is -1. Note, the columnstore index itself does not use one of the index slots.

  • An index on the internal deleted rows table (DRT) track the rows that have been deleted from the in-memory table and the corresponding in-memory columnstore index.

-- memory consumers (database level)  
SELECT OBJECT_NAME(object_id), *   
FROM sys.dm_db_xtp_memory_consumers;  

-- memory consumers (database level)  
select  convert(char(10), object_name(object_id)) as Name,   
convert(char(10),memory_consumer_type_desc ) as memory_consumer_type_desc, object_id,index_id, allocated_bytes,  used_bytes   
from sys.dm_db_xtp_memory_consumers  

Here is the output with a subset of columns. The allocators at database levels refer to user tables, indexes, and system tables. The VARHEAP with object_id = NULL (last row) refers to memory allocated to data rows of the tables (in the example here, it is t1). The allocated bytes, when converted to MB, is 1340MB.

Name       memory_consumer_type_desc object_id   index_id    allocated_bytes      used_bytes  
---------- ------------------------- ----------- ----------- -------------------- --------------------  
t3         HASH                      629577281   2           8388608              8388608  
t2         HASH                      597577167   2           8388608              8388608  
t1         HASH                      565577053   2           1048576              1048576  
NULL       HASH                      -6          1           2048                 2048  
NULL       VARHEAP                   -6          NULL        0                    0  
NULL       HASH                      -5          3           8192                 8192  
NULL       HASH                      -5          2           8192                 8192  
NULL       HASH                      -5          1           8192                 8192  
NULL       HASH                      -4          1           2048                 2048  
NULL       VARHEAP                   -4          NULL        0                    0  
NULL       HASH                      -3          1           2048                 2048  
NULL       HASH                      -2          2           8192                 8192  
NULL       HASH                      -2          1           8192                 8192  
NULL       VARHEAP                   -2          NULL        196608               26496  
NULL       HASH                      0           1           2048                 2048  
NULL       PGPOOL                    0           NULL        0                    0  
NULL       VARHEAP                   NULL        NULL        1405943808           1231220560  
(17 row(s) affected)  

The total memory allocated and used from this DMV is same as the object level in sys.dm_db_xtp_table_memory_stats (Transact-SQL).

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  
total_allocated_MB   total_used_MB  
-------------------- --------------------  
1358                 1191  

Memory-Optimized Table Dynamic Management Views (Transact-SQL)

Community Additions