sys.dm_db_xtp_memory_consumers (Transact-SQL)

Makale
10/06/2023

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 - `VARHEAP` 3 - `HASH` 4 - `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

Aracılığıyla paylaş

sys.dm_db_xtp_memory_consumers (Transact-SQL)

Remarks

Permissions

Examples

Query memory consumers in the current database

Geri Bildirim

Ek kaynaklar

Aracılığıyla paylaş

sys.dm_db_xtp_memory_consumers (Transact-SQL)

Remarks

Permissions

Examples

Query memory consumers in the current database

Related content

Geri Bildirim

Ek kaynaklar