Issue link: http://hub-nl.insight.com/i/692679
71 C H A P T E R 5 | Broader data access
of 2 in the temporal_type column and locate its corresponding staging table in the sys.internal_tables
table. Aggregate the values in the allocated_bytes and used_bytes columns of
sys.dm_db_xtp_memory_consumers to view memory consumption for the specified memory-optimized
table.
Example 5-6: Viewing memory usage for temporal and internal staging tables
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 TemporalTableName
, 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.TemporalTableName
, 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 TemporalTableSchema
, TemporalTableName
, sum ( allocated_bytes ) AS allocated_bytes
, sum ( used_bytes ) AS used_bytes
FROM DetailedConsumption
WHERE TemporalTableSchema = 'Production'
AND TemporalTableName = 'ProductInventory_Temporal_InMemory'
GROUP BY TemporalTableSchema, TemporalTableName;
Querying temporal tables
A standard query against a temporal table returns rows from the current table only. When you need
to retrieve historical data, you can add the FOR SYSTEM_TIME clause to your SELECT statement with
one of the following subclauses to define the boundaries of the data to return, as shown in Example
5-7:
AS OF