CREATE DATABASE [TestTemporalDb1];
ALTER DATABASE [TestTemporalDb1]
ADD FILEGROUP [IMOLTP] CONTAINS MEMORY_OPTIMIZED_DATA;
ALTER DATABASE [TestTemporalDb1]
ADD FILE (name='TestTemporalDb1_Mod1', FILENAME='D:\Temp\TestTemporalDb1_Mod1') TO FILEGROUP [IMOLTP];
USE [TestTemporalDb1]
GO
CREATE TABLE [dbo].[Orders](
[OrderId] [INT] Identity NOT NULL,
[StoreID] int NOT NULL,
[CustomerID] int NOT NULL,
[OrderDate] [datetime] NOT NULL,
[DeliveryDate] datetime NULL,
[Amount] float,
[Notes] [NVARCHAR] (max) NULL,
[ValidFrom] [datetime2](7) NOT NULL,
[ValidTo] [datetime2](7) NOT NULL,
CONSTRAINT [PK_OrderID] PRIMARY KEY NONCLUSTERED (OrderId)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO
CREATE TABLE [dbo].[Orders_History](
[OrderId] [INT] NOT NULL,
[StoreID] int NOT NULL,
[CustomerID] int NOT NULL,
[OrderDate] [datetime] NOT NULL,
[DeliveryDate] datetime NULL,
[Amount] float,
[Notes] [NVARCHAR] (max) NULL,
[ValidFrom] [datetime2](7) NOT NULL,
[ValidTo] [datetime2](7) NOT NULL,
);
-- Create custom Indexing on the Temporal History table
CREATE CLUSTERED INDEX [IX_Order_History]
ON [dbo].[Orders_History] ( ValidTo, ValidFrom) WITH (DATA_COMPRESSION = PAGE)
-- Default Clustered index is on ValidFrom and ValidTo times
CREATE NONCLUSTERED INDEX [IX_OrderHistory_OrderId] ON [dbo].[Orders_History]
(
[OrderId] ASC,
ValidTo,
ValidFrom
) ;
GO
-- Make Temporal
ALTER TABLE [dbo].[Orders] ADD PERIOD FOR SYSTEM_TIME (ValidFrom,ValidTo);
GO
ALTER TABLE [dbo].[Orders] ALTER COLUMN ValidTo ADD HIDDEN;
ALTER TABLE [dbo].[Orders] ALTER COLUMN ValidFrom ADD HIDDEN;
GO
-- Enable Temporal
ALTER TABLE [dbo].[Orders]
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[Orders_History]))
GO
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
sp_helpindex 'sys.memory_optimized_history_table_869578136'
GO
EXEC [InsertOrders_Native_Batch] 4500000;
-- Effectively updating every 1/18th row (less than 8% of rows)
SET NOCOUNT ON
GO
UPDATE Orders SET DeliveryDate = getdate()+1
where OrderID % 18 = 0;
-- Though a query is on the history table, it also references in-memory internal table
SELECT * FROM [Orders_History] WHERE OrderID = 18
SQL Server Execution Times: CPU time = 578 ms, elapsed time = 837 ms.
Note that in the plan above, even though we only get one row from the memory-optimized internal table, we are still doing a full scan, because in the only index this table has, OrderID is not the leading column. The memory optimized internal table has 250,000 rows, none of which are flushed to the on-disk history table yet, since it is below the flush threshold as shown by the snapshot of memory consumption below.
The same is true if you do a query that uses any of the FOR SYSTEM_TIME constructs, and restrict it to a particular OrderID
SELECT * FROM Orders FORSYSTEM_TIME ALL
WHERE [OrderID] = 18;
DBCC TRACEON(10316)
GO
ALTER TABLE sys.memory_optimized_history_table_565577053
ADD Index IndOrderID(OrderId)
GO
DBCC TRACEOFF(10316)
Looking at the plan for the same query now, we see an index seek, given there is an index to satisfy the predicate. Execution times are substantially lower than in the prior case
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 36 ms.
CREATE DATABASE [TestTemporalDb1];
ALTER DATABASE [TestTemporalDb1] ADD FILEGROUP [IMOLTP] CONTAINS MEMORY_OPTIMIZED_DATA ;
ALTER DATABASE [TestTemporalDb1]
ADD FILE (name='TestTemporalDb1_Mod1', filename='H:\data\TestTemporalDb1_Mod1') TO FILEGROUP [IMOLTP];
USE [TestTemporalDb1]
GO
-- Create on In-memory table with a history table.
CREATE TABLE [dbo].[Orders](
[OrderId] [INT] Identity NOT NULL,
[StoreID] int NOT NULL,
[CustomerID] int NOT NULL,
[OrderDate] [datetime] NOT NULL,
[DeliveryDate] datetime NULL,
[Amount] float,
[Notes] [NVARCHAR] (max) NULL,
[ValidFrom] [datetime2](7) NOT NULL,
[ValidTo] [datetime2](7) NOT NULL,
CONSTRAINT [PK_OrderID] PRIMARY KEY NONCLUSTERED (OrderId)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO
ALTER TABLE Orders ADD INDEX IndOrders_StoreID(StoreID);
GO
-- Create the table on the partition scheme
CREATE TABLE [dbo].[Orders_History](
[OrderId] [INT] NOT NULL,
[StoreID] int NOT NULL,
[CustomerID] int NOT NULL,
[OrderDate] [datetime] NOT NULL,
[DeliveryDate] datetime NULL,
[Amount] float,
[Notes] [NVARCHAR] (max) NULL,
[ValidFrom] [datetime2](7) NOT NULL,
[ValidTo] [datetime2](7) NOT NULL,
)
GO
-- Create custom Indexing on the Temporal History table
CREATE CLUSTERED INDEX [IX_Order_History]
ON [dbo].[Orders_History] ( ValidTo, ValidFrom) WITH (DATA_COMPRESSION = PAGE);
-- Default Clustered index is on ValidFrom and ValidTo times
CREATE NONCLUSTERED INDEX [IX_OrderHistory_OrderId] ON [dbo].[Orders_History]
( [OrderId] ASC, ValidTo, ValidFrom) ;
GO
-- Make Temporal
ALTER TABLE [dbo].[Orders] ADD PERIOD FOR SYSTEM_TIME (ValidFrom,ValidTo);
ALTER TABLE [dbo].[Orders] ALTER COLUMN ValidTo ADD HIDDEN;
ALTER TABLE [dbo].[Orders] ALTER COLUMN ValidFrom ADD HIDDEN;
GO
-- Enable Temporal
ALTER TABLE [dbo].[Orders] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[Orders_History]));
GO
-- 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 ( used_bytes ) AS used_bytes
FROM DetailedConsumption
GROUP BY TemporalTableSchema, ParentTemporalTableName, InternalHistoryStagingName,object_id ;
-- Check indexes
-- Replace the internal table with the table name you get from the prior query
sp_helpindex 'Orders'
GO
sp_helpindex 'sys.memory_optimized_history_table_565577053'
GO
-- Insert some data?
CREATE PROCEDURE [dbo].[InsertOrders_Native_Batch]
@OrderNum INT=100
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english')
DECLARE @counter AS INT = 1;
WHILE @counter <= @OrderNum
BEGIN
INSERT INTO dbo.Orders
VALUES (@counter % 8 , @counter %10000 , getdate(), NULL , rand() * 1000,
'Notes for a fake order temporal testing, will update these later' );
SET @counter = @counter + 1;
END
END;
GO
-- Insert some rows
EXEC [InsertOrders_Native_Batch] 500000
Go 9
-- Now update them.
-- Effectively updating every 1/18th row ( less than 10% of rows)
SET NOCOUNT ON
GO
UPDATE Orders SET DeliveryDate = getdate()+1
WHERE OrderID % 18 = 0
-- This also references In-memory temp table
select count(*) from [Orders_History]
-- Enable actual plan and also look at Statistics
set statistics time on
go
-- Though a query on the history table, it also references In-memory temp table
select * from [Orders_History] where OrderID = 18
--*******************************************************************************
-- You need SQL 2016 CU3 or SQL 2016 SP1 in order for the steps below to work
--**********************************************************************************
DBCC TRACEON(10316)
GO
ALTER TABLE sys.memory_optimized_history_table_565577053
ADD Index IndOrderID(OrderId)
GO
DBCC TRACEOFF(10316)
-- Now execute the same statement and you should see a seek
select * from [Orders_History] where OrderID = 18
-- Can we manually ensure that the in-memory history table is flushed?
exec sys.sp_xtp_flush_temporal_history 'dbo', 'Inventory'
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.