Rediger

Del via

System-versioned temporal tables with memory-optimized tables

  • Artikkel

Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance

System-versioned temporal tables for memory-optimized tables provide a cost-effective solution for scenarios where data audit and point in time analysis are required on top of data collected with In-Memory OLTP workloads.

Overview

System-versioned temporal tables automatically keep a full history of data changes, and expose convenient Transact-SQL extensions for point in time analysis. In a typical scenario, data history is retained for a long period of time (multiple months, even years), even though it isn't regularly queried.

Data audit and time-based analysis can be demanded in different environments, especially in OLTP systems that process extremely large numbers of requests and where In-Memory OLTP technology is used. However, using memory-optimized tables in temporal scenarios is challenging because a huge amount of generated historical data commonly exceeds the limit of available RAM. At the same time, using RAM to store read-only historical data that is accessed less frequently as it becomes older, isn't an optimal solution.

System-versioned temporal tables for memory-optimized tables provide high transactional throughput, and lock-free concurrency. They give you the ability to store large amount of history data by using in-memory tables for storing current data (the temporal table), and disk-based tables for historical data. The effect on DML operations is reduced by using an internal, autogenerated memory-optimized staging table that stores recent history, and enables DMLs to be executed from natively compiled code.

The following diagram illustrates this architecture.

Diagram of temporal in-memory architecture.

Implementation details

When you create a system-versioned memory-optimized table, be aware of the following considerations. For syntax options and for an example, see CREATE TABLE.

  • Only durable memory-optimized tables can be system-versioned (DURABILITY = SCHEMA_AND_DATA).

  • History table for memory-optimized system-versioned table must be disk-based, whether it was created by the end user or the system.

  • Queries that affect only the current in-memory table can be used in natively compiled T-SQL modules. Temporal queries using the FOR SYSTEM TIME clause aren't supported in natively compiled modules. The FOR SYSTEM TIME clause is supported with memory-optimized tables in ad hoc queries and non-native modules.

  • With SYSTEM_VERSIONING = ON, an internal memory-optimized staging table is automatically created to accept the most recent system-versioned changes, which are results of update and delete operations on a current memory-optimized table.

  • Data from the internal memory-optimized staging table is regularly moved to the disk-based history table by an asynchronous data flush task. This data flush mechanism keeps the internal memory buffers at less than 10 percent of the memory consumption of their parent objects. You can track the total memory consumption of memory-optimized system-versioned temporal table by querying sys.dm_db_xtp_memory_consumers, and summarizing the data for the internal memory-optimized staging table and the current temporal table.

  • You can execute a data flush manually by running sp_xtp_flush_temporal_history.

  • With SYSTEM_VERSIONING = OFF, or when the schema of a system-versioned table is modified by adding, dropping, or altering columns, the entire contents of the internal staging buffer is moved into the disk-based history table.

  • Querying of historical data is effectively under the snapshot isolation level, and always returns a union between in-memory staging buffer and disk-based table without duplicates.

  • ALTER TABLE operations that change the table schema internally must perform a data flush, which might prolong the duration of the operation.

The internal memory-optimized staging table

The system creates an internal memory-optimized staging table to optimize DML operations.

  • The table name is generated in the following format: Memory_Optimized_History_Table_<object_id> where <object_id> is identifier of the current temporal table.

  • The table replicates the schema of current temporal table plus one bigint column. This extra column guarantees the uniqueness of the rows moved to internal history buffer.

  • The extra column has the following name format: Change_ID[<suffix>], where <suffix> is optionally added in the case where the table already has a Change_ID column.

  • The maximum row size for a system-versioned memory-optimized table is reduced by 8 bytes because of the extra bigint column in the staging table. The new maximum is now 8,052 bytes.

  • The internal memory-optimized staging table isn't represented in Object Explorer of SQL Server Management Studio.

  • Metadata about this table, and its connection with the current temporal table, can be found in sys.internal_tables.

The data flush task

The data flush is a regularly activated task that checks whether any memory-optimized table meets a memory size-based condition for data movement. Data movement starts when memory consumption of internal staging table reaches eight percent of memory consumption of the current temporal table.

The data flush task is activated regularly with a schedule that varies based on the existing workload. With a heavy workload, the task runs as frequently as every 5 seconds. With a light workload, the frequency rises to every minute. One thread is spawned for each internal memory-optimized staging table that needs cleanup.

Data flush deletes all records from the in-memory internal buffer that are older than the oldest currently running transaction to move these records to the disk-based history table.

You can execute a data flush by running sp_xtp_flush_temporal_history and specifying the schema and table name:

EXEC sys.sp_xtp_flush_temporal_history <schema_name>, <object_name>;

The same data movement process is invoked as when the system executes the data flush task on its internal schedule.

Related content