sys.dm_db_xtp_memory_consumers (Transact-SQL)

sys.dm_db_xtp_memory_consumers (Transact-SQL)


Applies To: SQL Server 2014, SQL Server 2016 Preview

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).

Applies to: SQL Server (SQL Server 2014 through current version).


Column name

Data type




ID (internal) of the memory consumer.



The 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.)



Type of memory consumer: VARHEAP, HASH, or PGPOOL.

  • 0 – (It should not be displayed.)

  • 2 - VARHEAP

  • 3 - HASH

  • 5 - PGPOOL



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.


    For the database there is only one page pool Database 64K page pool.



The object ID to which the allocated memory is attributed. A negative value for system objects.



The object ID for the memory-optimized table.



The index ID of the consumer (if any). NULL for base tables.



Number of bytes reserved for this consumer.



Bytes used by this consumer. Applies only to varheap.



Number of allocations.



Internal use only.



Internal use only.



Internal use only.



Internal use only.



Internal address of the consumer. For internal use only.



The 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

Community Additions

© 2015 Microsoft