Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
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), SQL Database V12.

   

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 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_desc

nvarchar(64)

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

  • 0 – (It should not be displayed.)

  • 2 - VARHEAP

  • 3 - HASH

  • 5 - PGPOOL

memory_consumer_desc

nvarchar(64)

Description of the memory consumer instance:

  • VARHEAP

    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_id

bigint

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

xtp_object_id

bigint

The object ID for 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.

xtp_object_id

bigint

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

ADD
Show:
© 2015 Microsoft