In-Memory:内存优化表 DMV

2016-12-30 07:36:21来源:cnblogs.com作者:悦光阴人点击

第七城市

在内存优化表的DMV中,有两个对象ID(Object ID):

  • xtp_object_id 是内部的内存优化表(Internal Memory-Optimized Table)的ID,在对象的整个生命周期中,该ID可变;
  • object_id 是User Table的ID,唯一标识该User Table,在对象的整个生命周期中,该ID不变;

一,查看内存优化结构

xtp_object_id 是内部的内存优化表的ID(Internal Memory-Optimized Table),每一个User Table都对应一个或多个Internal Table:

  • 一个Interal Table用于存储核心数据(Core Data);
  • 其他的Internal Table 用于存储临时数据,Columnstore Index ,Off-Row(Lob);
  • 详细信息,请阅读DMV: sys.memory_optimized_tables_internal_attributes

二,查看内存优化表占用的物理内存

--memory usageselect tms.object_id    ,object_schema_name(tms.object_id)+'.'+object_name(tms.object_id) as table_name    ,(tms.memory_allocated_for_indexes_kb+tms.memory_allocated_for_table_kb)/1024 as total_allocated_mb    ,tms.memory_allocated_for_table_kb/1024 as table_allocated_mb    ,tms.memory_used_by_table_kb/1024 as table_used_mb    ,(tms.memory_allocated_for_table_kb-tms.memory_used_by_table_kb)/1024 as table_unused_mb    ,tms.memory_allocated_for_indexes_kb/1024 as index_allocated_mb    ,tms.memory_used_by_indexes_kb/1024 as index_used_mb    ,(tms.memory_allocated_for_indexes_kb-tms.memory_used_by_indexes_kb)/1024 as index_unused_mbfrom sys.dm_db_xtp_table_memory_stats tmswhere tms.object_id>0

三,查看内存消费者(Memory Consumer)

每一个MOT都有单独的Memory Heap,称作VarHeap,是一个Memory Consumer,SQL Server从VarHeap中为MOT的数据分配内存空间。varheap是可变大小的堆数据结构,能够收缩和增长。VarHeap是由固定数量的Allocation Unit组成的集合。Allocation Unit用于分配特定大小的Page,Page的大小是不固定的,最常见的Page Size是64KB。

VarHeap用于Table Row 和 Bw-Tree Index。每一个LOB列(使用max指定大小)都有自己独立的VarHeap。在创建MOT时,SQL Server决定哪些column存储在Table的VarHeap中,哪些column存储在自己独立的VarHeap中。

Hash Index 使用另外一个Memory Consumer,称作Hash。

select    object_schema_name(mc.object_id)+'.'+object_name(mc.object_id) as table_name     ,a.xtp_object_id    ,a.type_desc as xtp_object_type    ,iif(a.minor_id=0,'User Table','Off-Row Column:'+col_name(a.object_id,a.minor_id)) as xtp_object    ,mc.memory_consumer_id as consumer_id    ,mc.memory_consumer_type_desc as consumer_type    ,mc.memory_consumer_desc as consumer_desc    ,i.name as index_name    ,i.type_desc as index_type_desc    ,mc.allocated_bytes/1024/1024 as allocated_mb    ,mc.used_bytes/1024/1024 as used_mb    ,mc.allocation_countfrom sys.dm_db_xtp_memory_consumers mcinner join sys.memory_optimized_tables_internal_attributes a    on mc.object_id=a.object_id        and mc.xtp_object_id=a.xtp_object_idleft join sys.indexes i     on mc.object_id=i.object_id        and mc.index_id=i.index_idwhere mc.object_id=object_id('[influencer].[Influencers]')

引用:SQL Server In-Memory OLTP Internals for SQL Server 2016

The varheaps are used for both table rows and Bw-tree indexes. (Hash indexes are the only structure used with memory-optimized tables that uses a different memory consumer.) In addition, each LOB, column (specified with the MAX qualifier in the datatype definition) has its own varheap. As mentioned earlier, SQL Server 2016 also supports large columns similar to the row-overflow columns for disk-based tables. For memory-optimized tables, SQL Server will decide when the table is created which of your variable length columns will be stored in the table’s varheap and which will be stored as overflow data and have their own varheap. You can think of LOB and row-overflow columns as being stored in their own internal tables.

You can examine the metadata for each varheap (and the other memory consumers) in a DMV called sys.dm_db_xtp_memory_consumers. Each memory-optimized table has a row in this view for each varheap and for each hash index. (We will see one more type of memory consumer, called HkCS Allocator, in the section on columnstore indexes on memory-optimized tables.) If we join this DMV with the catalog view called sys.memory_optimized_tables_internal_attributes we can see which varheap belongs to a specific column. Each user table has a unique object_id and xtp_object_id value which is used by all the indexes. Each additional varheap, for the row_overflow and LOB columns will have its own xtp_object_id. Note that if an object is altered, its xtp_object_id will change, but its object_id will not. 

四,Hash Index的链表长度

对于Hash Index,表示链长的字段有:avg_chain_length 和 max_chain_length ,链长应保持在2左右;链长过大,表明太多的数据行被映射到相同的Bucket中,这会显著影响DML操作的性能。

导致链长过大的原因是:

  • 总的Bucket数量少,导致不同的Index Key映射到相同的Bucket上;
  • 如果空的Bucket数量大,但链长过大,这说明,Hash Index存在大量重复的Index Key;相同的Index Key被映射到相同的bucket;
  • 详细信息,请阅读:sys.dm_db_xtp_hash_index_stats (Transact-SQL);

五,事务

在访问MOT时,有两种类型事务ID,在事务中,访问MOT和访问DBT的事务是独立的:

  • xtp_transaction_id:是访问MOT的事务ID;
  • transaction_id:是访问DBT的事务ID,0表示事务没有访问MOT;
  • 详细信息,请阅读:sys.dm_db_xtp_transactions (Transact-SQL)

 查看当前数据库中活跃事务的信息:

select t.xtp_transaction_id    ,t.transaction_id    ,t.session_id    ,t.begin_tsn    ,t.end_tsn    ,t.state_desc    ,t.result_descfrom sys.dm_db_xtp_transactions t

 

参考文档:

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

第七城市

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台