sys.dm_db_xtp_memory_consumers (Transact-SQL)sys.dm_db_xtp_memory_consumers (Transact-SQL)

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

报告 内存中 OLTPIn-Memory OLTP 数据库引擎中的数据库级内存消耗者。Reports the database-level memory consumers in the 内存中 OLTPIn-Memory OLTP database engine. 视图为数据库引擎使用的每个内存消耗者返回一行。The view returns a row for each memory consumer that the database engine uses. 使用此 DMV 以查看如何将内存分布在不同的内部对象。Use this DMV to see how the memory is distributed across different internal objects.

有关详细信息,请参阅内存中 OLTP(内存中优化)For more information, see In-Memory OLTP (In-Memory Optimization).

列名Column name 数据类型Data type 描述Description
memory_consumer_idmemory_consumer_id bigintbigint 内存消耗者的 ID(内部)。ID (internal) of the memory consumer.
memory_consumer_typememory_consumer_type intint 内存消耗者的类型:The type of memory consumer:

0=聚合。0=Aggregation. (聚合两个或多个消耗者的内存使用量。(Aggregates memory usage of two or more consumers. 不应显示。)It should not be displayed.)

2=VARHEAP(跟踪长度可变的堆的内存占用情况。)2=VARHEAP (Tracks memory consumption for a variable-length heap.)

3=HASH(跟踪索引的内存占用情况。)3=HASH (Tracks memory consumption for an index.)

5=DB 页池(跟踪用于运行时操作的数据库页池的内存占用情况。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_descmemory_consumer_type_desc nvarchar(64)nvarchar(64) 内存消耗者的类型:VARHEAP、 HASH 或 PGPOOL。Type of memory consumer: VARHEAP, HASH, or PGPOOL.

0-(它应不显示。)0 - (It should not be displayed.)

2 - VARHEAP2 - VARHEAP

3 - HASH3 - HASH

5 - PGPOOL5 - PGPOOL
memory_consumer_descmemory_consumer_desc nvarchar(64)nvarchar(64) 对内存消耗者实例的说明:Description of the memory consumer instance:

VARHEAP: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. 由范围索引用于分配 BW 页的专用堆。Private heap used by range index to allocate BW pages.

哈希:无说明,因为 object_id 指示表,index_id 哈希索引本身。HASH: No description since the object_id indicates the table and the index_id the hash index itself.

PGPOOL:数据库是只有一个页池数据库 64k 页池。PGPOOL: For the database there is only one page pool Database 64K page pool.
object_idobject_id bigintbigint 所分配的内存所属的对象 ID。The object ID to which the allocated memory is attributed. 负值表示系统对象。A negative value for system objects.
xtp_object_idxtp_object_id bigintbigint 内存优化表对象 ID。The object ID for the memory-optimized table.
index_idindex_id intint 消耗者的索引 ID(如果有)。The index ID of the consumer (if any). NULL 表示基表。NULL for base tables.
allocated_bytesallocated_bytes bigintbigint 为此消耗者保留的字节数。Number of bytes reserved for this consumer.
used_bytesused_bytes bigintbigint 此消耗者使用的字节数。Bytes used by this consumer. 仅适用于 varheap。Applies only to varheap.
allocation_countallocation_count intint 分配的数量。Number of allocations.
partition_countpartition_count intint 仅限内部使用。Internal use only.
sizeclass_countsizeclass_count intint 仅限内部使用。Internal use only.
min_sizeclassmin_sizeclass intint 仅限内部使用。Internal use only.
max_sizeclassmax_sizeclass intint 仅限内部使用。Internal use only.
memory_consumer_addressmemory_consumer_address varbinaryvarbinary 消耗者的内部地址。Internal address of the consumer. 仅限内部使用。For internal use only.
xtp_object_idxtp_object_id bigintbigint 对应于内存优化表的内存中 OLTP 对象 ID。The in-memory OLTP object ID that corresponds to the memory-optimized table.

备注Remarks

在输出中,数据库级分配器指用户表、索引和系统表。In the output, the allocators at database levels refer to user tables, indexes, and system tables. object_id = NULL 的 VARHEAP 指分配给具有可变长度列的表的内存。VARHEAP with object_id = NULL refers to memory allocated to tables with variable length columns.

权限Permissions

如果您对当前数据库拥有 VIEW DATABASE STATE 权限,将返回所有行。All rows are returned if you have VIEW DATABASE STATE permission on the current database. 否则,将返回一个空行集。Otherwise, an empty rowset is returned.

如果您没有 VIEW DATABASE 权限,将为表中您拥有 SELECT 权限的行返回所有列。If you do not have VIEW DATABASE permission, all columns will be returned for rows in tables that you have SELECT permission on.

只会为拥有 VIEW DATABASE STATE 权限的用户返回系统表。System tables are returned only for users with VIEW DATABASE STATE permission.

一般备注General 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. 有关这些内部表和示例查询显示其内存占用率的详细信息,请参阅sys.memory_optimized_tables_internal_attributes (TRANSACT-SQL)For details about these internal tables and sample queries showing their memory consumption see sys.memory_optimized_tables_internal_attributes (Transact-SQL).

示例Examples

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

使用方案User Scenario

-- 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. object_id = NULL 的 VARHEAP(最后一行)引用分配给各表的数据行的内存(本例中为 t1)。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). 分配的字节数(转换为 MB)为 1340MB。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)  

总内存分配和使用从此 DMV 是与中的对象级别相同sys.dm_db_xtp_table_memory_stats (TRANSACT-SQL)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  

请参阅See Also

内存优化表动态管理视图(Transact SQL)Memory-Optimized Table Dynamic Management Views (Transact-SQL)