分享方式：

監視記憶體最佳化的系統版本設定時態表

  • 文章

適用於：SQL Server 2016 (13.x) 和更新版本 Azure SQL 資料庫 Azure SQL 受控執行個體

針對每個系統建立版本的記憶體最佳化資料表，您可以使用現有的檢視來追蹤其詳細和摘要記憶體耗用量。

監視時態表

使用下列範例程式碼來監視使用記憶體內部 OLTP 的時態表。 這些範例會使用通用資料表運算式 (CTE)。

詳細的記憶體使用量

以下查詢會詳細說明記憶體使用量，依據每個主要的系統版本設定和內部歷程記錄暫存表格分割。

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
    INNER JOIN sys.tables T1
        ON IT.parent_object_id = T1.object_id
    WHERE T1.is_memory_optimized = 1
        AND T1.temporal_type = 2
)
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
INNER JOIN InMemoryTemporalTables T
    ON C.object_id = T.TemporalTableObjectId
        OR C.object_id = T.InternalTableObjectId
WHERE T.TemporalTableSchema = 'dbo'
    AND T.TemporalTableName = 'FXCurrencyPairs';

記憶體使用量摘要

以下查詢會摘要說明記憶體使用量，以及系統版本設定的經記憶體最佳化的資料表總計。

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
    INNER 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
    INNER 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 = 'dbo' ANDTemporalTableName = 'FXCurrencyPairs'
GROUP BY TemporalTableSchema,
    TemporalTableName;

相關內容