In-Memory：記憶體優化表 DMV

在記憶體優化表的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 usage
select 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_mb
from sys.dm_db_xtp_table_memory_stats tms
where 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_count
from sys.dm_db_xtp_memory_consumers mc
inner join sys.memory_optimized_tables_internal_attributes a
    on mc.object_id=a.object_id
        and mc.xtp_object_id=a.xtp_object_id
left join sys.indexes i 
    on mc.object_id=i.object_id
        and mc.index_id=i.index_id
where 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_desc
from sys.dm_db_xtp_transactions t

參考文檔：

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