Compartir vía

Supervisión de tablas temporales con control de versiones del sistema optimizadas para memoria

  • Artículo

Se aplica a: SQL Server 2016 (13.x) y versiones posteriores Azure SQL Database Azure SQL Managed Instance

Puede utilizar las vistas existentes para realizar un seguimiento del consumo de memoria resumido y detallado para cada tabla optimizada para memoria con control de versiones del sistema.

Supervisión de tablas temporales

Use el código de ejemplo siguiente para supervisar las tablas temporales que usan OLTP en memoria. En estos ejemplos se usan expresiones de tabla comunes (CTE).

Consumo de memoria detallado

La consulta siguiente detalla el consumo de memoria, dividido por tabla de almacenamiento provisional de historial interna y con control de versiones del sistema.

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';

Resumen del consumo de memoria

La consulta siguiente resume el consumo de memoria, con un total para una tabla optimizada para memoria con control de versiones del sistema.

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;

Contenido relacionado