Patrón de aplicación para crear particiones de tablas optimizadas para memoria

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

In-Memory OLTP admite un patrón de diseño de aplicaciones que da prioridad al rendimiento de los datos actuales. Este patrón se aplica cuando los datos actuales se leen o actualizan significativamente más a menudo que los datos más antiguos. Los datos actuales se consideran activos o activos y los datos más antiguos son fríos.

La idea principal es almacenar los datos de acceso frecuente en una tabla optimizada para memoria. De forma semanal o mensual, los datos más antiguos que se vuelven inactivos se mueven a una tabla particionada. Los datos de las tablas con particiones se almacenan en un disco o en otra unidad de disco duro, no en memoria.

Normalmente, este diseño utiliza una clave datetime2 para que el proceso de movimiento distinga de manera eficaz entre datos calientes y fríos.

Particiones avanzadas

El diseño pretende imitar una tabla particionada que incluye una partición optimizada para memoria. Para que este diseño funcione, asegúrese de que las tablas comparten un esquema común. En el ejemplo de código que aparece más adelante en este artículo se muestra esta técnica.

Los nuevos datos siempre se consideran relevantes. Los datos de acceso frecuente se insertan y actualizan en la tabla optimizada para memoria, Los datos fríos se mantienen en la tabla particionada tradicional. Cada cierto tiempo, un procedimiento almacenado agrega una nueva partición. La partición contiene los datos fríos más recientes movidos fuera de la tabla optimizada para memoria.

Si una operación necesita únicamente datos de acceso frecuente, puede usar procedimientos almacenados compilados de forma nativa para acceder a esos datos. Las operaciones que puedan acceder a datos calientes o fríos deben usar Transact-SQL interpretado para combinar la tabla optimizada para memoria con la tabla con particiones.

Adición de una partición

Los datos que se han vuelto fríos recientemente deben moverse a la tabla particionada. Estos son pasos para realizar este cambio periódico de particiones:

1. En el caso de los datos de la tabla optimizada para memoria, determine el valor de fecha y hora que actúe como límite entre los datos de acceso frecuente y los datos recientemente enfriados.

2. Inserte los datos que se han vuelto fríos, de la tabla In-Memory OLTP, en la tabla cold_staging.

3. Borre los mismos datos inactivos de la tabla optimizada para memoria.

4. Intercambie la cold_staging tabla por una partición.

5. Agregue la partición.

Ventana de mantenimiento

Uno de los pasos anteriores es eliminar los datos que se han enfriado recientemente de la tabla optimizada para el uso eficiente de la memoria. Hay un intervalo de tiempo entre esta eliminación y el paso final que agrega la nueva partición. Durante este intervalo, se produce un error en cualquier aplicación que intente leer los datos recién inactivos.

Para obtener un ejemplo relacionado, vea Creación de particiones en el nivel de aplicación.

Ejemplo de código

El siguiente ejemplo de Transact-SQL se separa en una serie de bloques de código más pequeños para presentarlos de forma más sencilla, Podrías incluirlos todos en un gran bloque de código para tus pruebas.

En conjunto, el ejemplo de T-SQL refleja cómo usar una tabla optimizada para memoria con una tabla basada en disco con particiones.

En las primeras fases del ejemplo de T-SQL se crea la base de datos, tras lo cual se crean objetos como, por ejemplo, tablas en la base de datos. En las fases subsiguientes se muestra cómo mover datos de una tabla optimizada para memoria a una tabla con particiones.

Creación de una base de datos

En esta sección del ejemplo de T-SQL se crea una base de datos de prueba. La base de datos está configurada para admitir tanto tablas optimizadas para memoria como tablas con particiones.

CREATE DATABASE PartitionSample;
GO

-- Add a FileGroup, enabled for In-Memory OLTP.
-- Change file path as needed.
ALTER DATABASE PartitionSample
    ADD FILEGROUP PartitionSample_mod
    CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE PartitionSample
    ADD FILE (
        NAME = 'PartitionSample_mod',
        FILENAME = 'C:\data\PartitionSample_mod')
    TO FILEGROUP PartitionSample_mod;
GO

Creación de una tabla optimizada para memoria para los datos de acceso frecuente

En esta sección se crea la tabla optimizada para memoria que contendrá los datos más recientes, que principalmente siguen siendo datos de acceso frecuente.

USE PartitionSample;
GO

-- Create a memory-optimized table for the HOT Sales Order data.
-- Notice the index that uses DATETIME2.
CREATE TABLE dbo.SalesOrders_hot
(
    so_id INT IDENTITY PRIMARY KEY NONCLUSTERED,
    cust_id INT NOT NULL,
    so_date DATETIME2 NOT NULL INDEX ix_date NONCLUSTERED,
    so_total MONEY NOT NULL,
    INDEX ix_date_total NONCLUSTERED (so_date DESC, so_total DESC)
)
WITH (MEMORY_OPTIMIZED = ON);
GO

Creación de una tabla con particiones para los datos inactivos

En esta sección se crea la tabla particionada que va a contener los datos fríos.

-- Create a partition and table for the COLD Sales Order data.
-- Notice the index that uses DATETIME2.
CREATE PARTITION FUNCTION [ByDatePF](DATETIME2)
    AS RANGE RIGHT
    FOR VALUES ();
GO

CREATE PARTITION SCHEME [ByDateRange]
    AS PARTITION [ByDatePF]
    ALL TO ([PRIMARY]);
GO

CREATE TABLE dbo.SalesOrders_cold
(
    so_id INT NOT NULL,
    cust_id INT NOT NULL,
    so_date DATETIME2 NOT NULL,
    so_total MONEY NOT NULL,
    CONSTRAINT PK_SalesOrders_cold PRIMARY KEY (so_id, so_date),
    INDEX ix_date_total NONCLUSTERED (so_date DESC, so_total DESC)
) ON [ByDateRange](so_date);
GO

Crear una tabla para almacenar los datos fríos durante el traslado

Esta sección crea la tabla cold_staging. También se crea una vista que une los datos calientes y fríos de las dos tablas.

-- A table used to briefly stage the newly cold data, during moves to a partition.
CREATE TABLE dbo.SalesOrders_cold_staging
(
    so_id INT NOT NULL,
    cust_id INT NOT NULL,
    so_date DATETIME2 NOT NULL,
    so_total MONEY NOT NULL,
    CONSTRAINT PK_SalesOrders_cold_staging PRIMARY KEY (so_id, so_date),
    CONSTRAINT CHK_SalesOrders_cold_staging CHECK (so_date >= '1900-01-01'),
    INDEX ix_date_total NONCLUSTERED (so_date DESC, so_total DESC)
);
GO

-- A view, for retrieving the aggregation of hot plus cold data.
CREATE VIEW dbo.SalesOrders AS
    SELECT so_id,
           cust_id,
           so_date,
           so_total,
           1 AS 'is_hot'
    FROM dbo.SalesOrders_hot
    UNION ALL
    SELECT so_id,
           cust_id,
           so_date,
           so_total,
           0 AS 'is_cold'
    FROM dbo.SalesOrders_cold;
GO

Creación del procedimiento almacenado

En esta sección se crea el procedimiento almacenado que se va a ejecutar periódicamente. El procedimiento mueve los datos recién enfriados de la tabla optimizada para memoria a la tabla particionada.

-- A stored procedure to move all newly cold sales orders data
-- to its staging location.
CREATE PROCEDURE dbo.usp_SalesOrdersOffloadToCold
@splitdate DATETIME2
AS
BEGIN
    BEGIN TRANSACTION;

    -- Insert the cold data as a temporary heap.
    INSERT INTO dbo.SalesOrders_cold_staging WITH (TABLOCKX)
    SELECT so_id,
           cust_id,
           so_date,
           so_total
    FROM dbo.SalesOrders_hot WITH (SERIALIZABLE)
    WHERE so_date < @splitdate;

    -- Delete the moved data from the hot table.
    DELETE dbo.SalesOrders_hot WITH (SERIALIZABLE)
    WHERE so_date < @splitdate;

    -- Update the partition function, and switch in the new partition.
    ALTER PARTITION SCHEME [ByDateRange] NEXT USED [PRIMARY];

    DECLARE @p AS INT = (SELECT MAX(partition_number)
                         FROM sys.partitions
                         WHERE object_id = OBJECT_ID('dbo.SalesOrders_cold'));

    EXECUTE sp_executesql N'ALTER TABLE dbo.SalesOrders_cold_staging
            SWITCH TO dbo.SalesOrders_cold partition @i', N'@i int', @i = @p;

    ALTER PARTITION FUNCTION [ByDatePF]()
        SPLIT RANGE (@splitdate);

    -- Modify a constraint on the cold_staging table, to align with new partition.
    ALTER TABLE dbo.SalesOrders_cold_staging
    DROP CONSTRAINT CHK_SalesOrders_cold_staging;

    DECLARE @s AS NVARCHAR (100) = CONVERT (NVARCHAR (100), @splitdate, 121);

    DECLARE @sql AS NVARCHAR (1000) = N'ALTER TABLE dbo.SalesOrders_cold_staging
        ADD CONSTRAINT CHK_SalesOrders_cold_staging CHECK (so_date >= ''' + @s + ''')';

    PRINT @sql;

    EXECUTE sp_executesql @sql;

    COMMIT TRANSACTION;

END
GO

Preparación de los datos de ejemplo y ejecución del procedimiento almacenado como demostración

En esta sección se generan e insertan datos de ejemplo y, tras ello, se ejecuta el procedimiento almacenado a modo de demostración.

-- Insert sample values into the hot table.
INSERT INTO dbo.SalesOrders_hot
VALUES (1, SYSDATETIME(), 1);
GO

INSERT INTO dbo.SalesOrders_hot
VALUES (1, SYSDATETIME(), 1);
GO

INSERT INTO dbo.SalesOrders_hot
VALUES (1, SYSDATETIME(), 1);
GO

-- Verify that the hot data is in the table, by selecting from the view.
SELECT *
FROM dbo.SalesOrders;
GO

-- Treat all data in the hot table as cold data:
-- Run the stored procedure, to move (offload) all sales orders to date to cold storage.
DECLARE @t AS DATETIME2 = SYSDATETIME();
EXECUTE dbo.usp_SalesOrdersOffloadToCold @t;

-- Again, read hot plus cold data from the view.
SELECT *
FROM dbo.SalesOrders;
GO

-- Retrieve the name of every partition.
SELECT OBJECT_NAME(object_id),
       *
FROM sys.dm_db_partition_stats AS ps
WHERE object_id = OBJECT_ID('dbo.SalesOrders_cold');

-- Insert more data into the hot table.
INSERT INTO dbo.SalesOrders_hot
VALUES (2, SYSDATETIME(), 1);
GO

INSERT INTO dbo.SalesOrders_hot
VALUES (2, SYSDATETIME(), 1);
GO

INSERT INTO dbo.SalesOrders_hot
VALUES (2, SYSDATETIME(), 1);
GO

-- Read hot plus cold data from the view.
SELECT *
FROM dbo.SalesOrders;
GO

-- Again, run the stored procedure, to move all sales orders to date to cold storage.
DECLARE @t AS DATETIME2 = SYSDATETIME();
EXECUTE dbo.usp_SalesOrdersOffloadToCold @t;

-- Read hot plus cold data from the view.
SELECT *
FROM dbo.SalesOrders;
GO

-- Again, retrieve the name of every partition.
-- The stored procedure can modify the partitions.
SELECT OBJECT_NAME(object_id),
       partition_number,
       row_count
FROM sys.dm_db_partition_stats AS ps
WHERE object_id = OBJECT_ID('dbo.SalesOrders_cold')
      AND index_id = 1;

Eliminación de todos los objetos de demostración

No olvide limpiar la base de datos de prueba de demostración del sistema de prueba.

USE master;
GO

DROP DATABASE PartitionSample;
GO

Contenido relacionado

• Base de datos de ejemplo para OLTP en memoria