SQL2016新功能時態表我以前寫過兩篇，這功能去年我也正式使用在一個線上OLTP系統，
我得老實說這功能確實省下我不少時間，但往往方便就會忽略一些細節，
這篇紀錄一下和Memory table使用的情況。
去年我大量使用In-Memory OLTP功能，將一個系統從disk table帶入memory table世界，
而In-Memory table with temporal table也是我其中一項規劃，
由於該系統記憶體有限(256GB)，所以針對大資料表自動Archive一定少不了，
而我當時忽略temporal table也會消耗記憶體，所以導致GC無法回收內部資料表浪費記憶體並影響其他查詢效能，
下面我簡單示範記憶體消耗有多嚇人。
Ps:GC平常一分鐘執行一次，遇到記憶體壓力約5秒一次，但我觀察這時間其實相當不固定。
CREATE TABLE dbo.MyTemporal
(ID int primary key NONCLUSTERED --必須定義PK
,c1 int
,c2 varchar(20)
,StartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL --定義開始時間(clustered index key)
,EndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL--定義結束時間(clustered index key)
,PERIOD FOR SYSTEM_TIME (StartTime,EndTime))
WITH(MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA,
SYSTEM_VERSIONING = ON( HISTORY_TABLE = [dbo].[MyTemporal_Archive] , DATA_CONSISTENCY_CHECK = ON )
--啟用Temporal並命名歷史紀錄表為MyTemporal_Archive(必須指定結構描述)，預設page壓縮
);
SELECT SCHEMA_NAME ( T1.schema_id ) AS TemporalTableSchema
, T1.object_id AS TemporalTableObjectId
, OBJECT_NAME ( IT.parent_object_id ) AS ParentTemporalTableName
, IT.[Name] AS InternalHistoryStagingName
FROM sys.internal_tables IT
JOIN sys.tables T1 ON IT.parent_object_id = T1.object_id
WHERE T1.is_memory_optimized = 1 AND T1.temporal_type = 2
內部資料表預設會建立唯一非叢集索引，這可以幫助lookup查詢效能
sp_helpindex 'sys.memory_optimized_history_table_110623437'
In-memory table with temporal table架構如下，由於In-Memory需要提供很高的交易處理效能，
所以不能被temporal table拖累(同步處理資料)，這時會透過內部Memory table來暫存row version，
內部資料表達到原本資料表8%門檻後，會啟動一條非同步執行緒進行Flush data to disk，
每一次的update、delete都會產生前一筆row version並存在內部資料表，
針對大型In-Memory table，你可以想像這表如果有5千萬，
那麼我需要達到4百萬才能自動寫入disk並釋放記憶體，
當然你也可以手動透過sp_xtp_flush_temporal_history進行釋放。
From Microsoft
下面我新增200萬筆資料，並實際操作讓我來瞧瞧消耗記憶體的隱形者
CREATE PROCEDURE usp_InsertMyTemporal_Native (@counts int)
WITH NATIVE_COMPILATION, SCHEMABINDING,execute as owner
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'english')
DECLARE @RowCount INT=0;
DECLARE @Random INT;
WHILE @RowCount < @counts
begin
set @Random = ROUND(((1000000- 8 -1) * RAND() + 8), 0)
INSERT INTO dbo.MyTemporal
(ID,c1,c2)
VALUES
(@RowCount,@Random,
cast(@Random as varchar(20)) + 'ricoisme'
)
SET @RowCount = @RowCount + 1
end
END
exec dbo.usp_InsertMyTemporal_Native 2000000 –elapsed time 9 sec
-- Internal Tables and memory consumption of each?
WITH InMemoryTemporalTables
AS
(
SELECT SCHEMA_NAME ( T1.schema_id ) AS TemporalTableSchema
, T1.object_id AS TemporalTableObjectId
, IT.object_id AS InternalTableObjectId
, OBJECT_NAME ( IT.parent_object_id ) AS ParentTemporalTableName
, IT.Name AS InternalHistoryStagingName
FROM sys.internal_tables IT
JOIN sys.tables T1 ON IT.parent_object_id = T1.object_id
WHERE T1.is_memory_optimized = 1 AND T1.temporal_type = 2
)
, DetailedConsumption
AS
(
SELECT TemporalTableSchema
, T.ParentTemporalTableName
, T.InternalHistoryStagingName
, CASE
WHEN C.object_id = T.TemporalTableObjectId
THEN 'Temporal Table Consumption'
ELSE 'Internal Table Consumption'
END ConsumedBy
, C.*
FROM sys.dm_db_xtp_memory_consumers C
JOIN InMemoryTemporalTables T
ON C.object_id = T.TemporalTableObjectId OR C.object_id = T.InternalTableObjectId
)
--select * from DetailedConsumption
SELECT TemporalTableSchema,
ParentTemporalTableName, object_id, object_name(object_id) as MemoryUsedByTable
, sum ( allocated_bytes ) AS allocated_bytes
, sum ( allocated_bytes/1024.0/1024.0 ) AS allocated_MB
, sum ( used_bytes ) AS used_bytes
, sum ( used_bytes/1024.0/1024.0) AS used_MB
FROM DetailedConsumption
GROUP BY TemporalTableSchema, ParentTemporalTableName, InternalHistoryStagingName,object_id ;
--更新資料且小於 total*8%
UPDATE MyTemporal SET c1 = ID+8
where ID % 13 = 0;
這時記憶體消耗成長到60272bytes(約300倍)。
SELECT * FROM MyTemporal_Archive
WHERE ID % 13=0
可以看到確實幫我自動Archive資料153847筆。
更新更多資料
UPDATE MyTemporal SET c1 = ID+7
where ID % 3 = 0;
這時記憶體消耗成長到94105872bytes(成長約1561倍)。
由於超過8%，但GC不會一次性回收，
而是拆成多個小單位(約16 row version)分批回收，主要是分散GC工作量。
大約過了3分鐘後，所有中繼資料表內記憶體幾乎都被GC回收了，
而GC回收時間無法讓我自行控制真的很讓我討厭，如果你等不及GC自動處理的話，
建議可以自行處理。
3分鐘後，記憶體只剩下10720 bytes。
--手動flush
sys.sp_xtp_flush_temporal_history dbo,MyTemporal
結論:
當時在規劃memory table時，忘記考慮temporal table這隱形記憶體消耗者，
而那些資料異動量少的資料表，導致內部中繼資料表占用過多記憶體並影響其他查詢效能，
後來我寫一隻SP針對所有內部中繼資料表進行記憶體消耗判斷並自動觸發flush。
參考
How bwin is using SQL Server 2016 In-Memory OLTP to achieve unprecedented performance and scale
System-Versioned Temporal Tables with Memory-Optimized Tables
sys.dm_db_xtp_memory_consumers (Transact-SQL)
Stopping System-Versioning on a System-Versioned Temporal Table
