Supervisar tablas temporales versionadas por el sistema optimizadas para memoria

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

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

Supervisión de tablas temporales

Utiliza el siguiente código de ejemplo para monitorizar tablas temporales que usan OLTP en memoria. Estos ejemplos utilizan expresiones de tabla comunes (CTE).

Consumo de memoria detallado

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

WITH InMemoryTemporalTables
AS (SELECT SCHEMA_NAME(t1.schema_id) AS TemporalTableSchema,
           t1.object_id AS TemporalTableObjectId,
           t1.object_id AS InternalTableObjectId,
           OBJECT_NAME(t1.parent_object_id) AS TemporalTableName,
           t1.Name AS InternalHistoryStagingName
    FROM sys.internal_tables AS t1
         INNER JOIN sys.tables AS t1
             ON t1.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 AS ConsumedBy,
       c.*
FROM sys.dm_db_xtp_memory_consumers AS c
     INNER JOIN InMemoryTemporalTables AS 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 siguiente consulta muestra un resumen del consumo de memoria, incluido el total de una tabla optimizada para memoria y versionada por el sistema.

;WITH InMemoryTemporalTables
AS (
    SELECT SCHEMA_NAME(t1.schema_id) AS TemporalTableSchema,
        t1.object_id AS TemporalTableObjectId,
        t1.object_id AS InternalTableObjectId,
        OBJECT_NAME(t1.parent_object_id) AS TemporalTableName,
        t1.Name AS InternalHistoryStagingName
    FROM sys.internal_tables t1
    INNER JOIN sys.tables t1
        ON t1.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 AS 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

  • Last updated on