Modèle d’application pour le partitionnement des tables mémoire optimisées

S’applique à :SQL Serverbase de données Azure SQLAzure SQL Managed Instance

In-Memory OLTP prend en charge un modèle de conception d’application qui hiérarchise les performances des données actuelles. Ce modèle s’applique lorsque les données actuelles sont lues ou mises à jour beaucoup plus souvent que les données plus anciennes. Les données actuelles sont considérées comme actives ou chaudes, et les données plus anciennes sont froides.

L’idée principale est de stocker les données chaudes dans une table à mémoire optimisée. Sur une base hebdomadaire ou mensuelle, les données plus anciennes qui deviennent froides sont déplacées vers une table partitionnée. Les données de la table partitionnée sont stockées sur un lecteur ou autre disque dur, et non en mémoire.

En règle générale, cette conception utilise une clé datetime2 pour permettre au processus de déplacement de distinguer efficacement les données chaudes et froides.

Partitionnement avancé

La conception fait comme s’il existait une table partitionnée dotée également d’une partition à mémoire optimisée. Pour que cette conception fonctionne, assurez-vous que les tables partagent un schéma commun. L’exemple de code présenté plus loin dans cet article en illustre la technique.

Les nouvelles données sont toujours considérées comme actuelles. Les données chaudes sont insérées et mises à jour dans la table à mémoire optimisée. Les données froides sont conservées dans la table partitionnée classique. À intervalles réguliers, une procédure stockée ajoute une nouvelle partition. La partition contient les données froides les plus récentes déplacées hors de la table optimisée pour la mémoire.

Si une opération a besoin uniquement de données chaudes, elle peut utiliser des procédures stockées compilées en mode natif pour accéder aux données. Les opérations susceptibles d’accéder à des données chaudes ou froides doivent utiliser du Transact-SQL interprété pour joindre la table à mémoire optimisée à la table partitionnée.

Ajouter une partition

Les données devenues froides récemment doivent être déplacées dans la table partitionnée. Cet échange de partition périodique se déroule comme suit :

1. Pour les données contenues dans la table à mémoire optimisée, déterminez la date et l'heure qui constituent la délimitation entre les données chaudes et celles qui deviennent froides.

2. Insérez les données nouvellement froides, à partir de la table OLTP In-Memory, dans une cold_staging table.

3. Supprimez ces mêmes données froides de la table à mémoire optimisée.

4. Remplacez la cold_staging table en une partition.

5. Ajoutez la partition.

Fenêtre de maintenance

L’une des étapes précédentes consiste à supprimer les données nouvellement froides de la table à mémoire optimisée. Il existe un intervalle de temps entre cette suppression et la dernière étape qui ajoute la nouvelle partition. Pendant cet intervalle, toute application qui tente de lire les données nouvellement froides échoue.

Pour obtenir un exemple, consultez Partitionnement au niveau de l’application.

Exemple de code

L’exemple Transact-SQL ci-dessous a été divisé en plusieurs blocs de code plus petits dans le seul but d’en faciliter la présentation. Vous pouvez les ajouter à un bloc de code plus grand à des fins de test.

Globalement, l’exemple T-SQL montre comment utiliser une table à mémoire optimisée avec une table sur disque partitionnée.

Les premières phases de l’exemple T-SQL créent la base de données avant de créer des objets comme les tables de la base de données. Les phases suivantes montrent comment déplacer les données d’une table à mémoire optimisée vers une table partitionnée.

Création d'une base de données

Cette section de l’exemple T-SQL crée une base de données de test. La base de données est configurée pour prendre en charge à la fois les tables à mémoire optimisée et les tables partitionnées.

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

Créer une table à mémoire optimisée pour les données chaudes

Cette section crée la table à mémoire optimisée qui contient les données les plus récentes, qui sont principalement des données toujours actives.

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

Créer une table partitionnée pour les données froides

Cette section crée la table partitionnée qui contient les données froides.

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

Créer une table pour stocker les données froides pendant le déplacement

Cette section crée la cold_staging table. Une vue réunissant les données chaudes et froides des deux tables est également créée.

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

Créer la procédure stockée

Cette section crée la procédure stockée que vous exécutez à intervalles réguliers. La procédure déplace les données nouvellement froides de la table à mémoire optimisée vers la table partitionnée.

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

Préparer les exemples de données et exécuter la procédure stockée à titre de démonstration

Cette section génère des exemples de données, les insère, puis exécute la procédure stockée à titre de démonstration.

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

Supprimer tous les objets de démonstration

N’oubliez pas de nettoyer la base de données de test de démonstration de votre système de test.

USE master;
GO

DROP DATABASE PartitionSample;
GO

Contenu connexe

• Exemple de base de données pour OLTP en mémoire