Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 82 of 212

70 C H A P T E R 5 | Broader data access [Shelf] [nvarchar](10) NOT NULL, [Bin] [tinyint] NOT NULL, [Quantity] [smallint] NOT NULL, [ModifiedDate] [datetime] NOT NULL, [ValidFrom] datetime2(7) NOT NULL, [ValidTo] datetime2(7) NOT NULL ); GO -- Add indexes to history table CREATE CLUSTERED COLUMNSTORE INDEX IX_ProductInventory_TemporalHistory_Disk ON [Production].[ProductInventory_TemporalHistory_Disk]; CREATE NONCLUSTERED INDEX IX_ProductInventory_TemporalHistory_Disk_ID_PERIOD_COLUMNS ON [Production].[ProductInventory_TemporalHistory_Disk] (ValidTo, ValidFrom, ProductID, LocationID); GO -- Create temporal table CREATE TABLE [Production].[ProductInventory_Temporal_InMemory]( [ProductID] [int] NOT NULL, [LocationID] [smallint] NOT NULL, [Shelf] [nvarchar](10) NOT NULL, [Bin] [tinyint] NOT NULL, [Quantity] [smallint] NOT NULL, [ModifiedDate] [datetime] NOT NULL, CONSTRAINT [PK_ProductInventory_Temporal_IM_ProductID_LocationID] PRIMARY KEY NONCLUSTERED ( [ProductID] ASC, [LocationID] ASC ), ValidFrom datetime2(7) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL, ValidTo datetime2(7) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA, SYSTEM_VERSIONING = ON (HISTORY_TABLE = [Production].[ProductInventory_TemporalHistory_Disk])); When you enable system versioning for a memory-optimized temporal table, the database engine creates an internal memory-optimized staging table to store changes and deletions as they occur in the current table. This table is not visible in Object Explorer in SSMS, but you can view metadata for this table in sys.internal_tables. The staging table name is memory_optimized_history_table_, where is the identifier of the current table. Periodically, an asynchronous data-flush task transfers the data from the staging table to the history table. This occurs when the memory required by the staging table reaches 8 percent of the memory required by the current table. The goal is to limit the staging table's memory requirements to 10 percent of the current table's memory. In addition to the periodic flush, the contents of the staging table are moved to the history table when you add, drop, or alter columns in the current table or alter the table by setting SYSTEM_VERSIONING = OFF. Note You can also force a flush of the staging table to the history table by using sp_xtp_flush_temporal_history. For more information on using this system stored procedure, see "sp_xtp_flush_temporal_history" at If you perform massive deletions from the current table to reduce its footprint in memory, memory consumption is still high because the data persists in the staging table. Instead, delete rows in batches and invoke sp_xtp_flush_temporal_history to manage the impact on memory more effectively. Similarly, bulk updates can also take a toll on memory. Therefore, perform batch updates instead in combination with sp_xtp_flush_temporal_history for optimal results. You can view the details of memory usage by querying the dynamic management view sys.dm_db_xtp_memory_consumers, as shown in Example 5-6. In this example, you identify the temporal table in the sys.tables table with a value of 1 in the is_memory_optimized column and a value

Articles in this issue

Links on this page

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016