Modello di applicazione per il partizionamento di tabelle ottimizzate per la memoria

Si applica a:SQL Server, Database SQL di Azure e Istanza gestita di SQL di Azure

In-Memory OLTP supporta un modello di progettazione dell'applicazione che assegna priorità alle prestazioni per i dati correnti. Questo modello si applica quando i dati correnti vengono letti o aggiornati in modo significativo più spesso rispetto ai dati meno recenti. I dati correnti sono considerati attivi o caldi e i dati meno recenti sono freddi.

Il concetto è archiviare i dati ad accesso frequente in una tabella ottimizzata per la memoria. Su base settimanale o mensile, i dati meno recenti che diventano freddi vengono spostati in una tabella partizionata. I dati nella tabella partizionata vengono archiviati su disco o in un'altra unità disco rigido, non in memoria.

In genere, questa progettazione usa una chiave datetime2 per consentire al processo di spostamento di distinguere efficacemente tra dati caldi e dati freddi.

Partizionamento avanzato

In questa progettazione è previsto l'uso di una tabella partizionata che include anche una partizione ottimizzata per la memoria. Per il funzionamento di questa progettazione, assicurarsi che le tabelle condividono uno schema comune. Nell'esempio di codice riportato più avanti in questo articolo viene illustrata la tecnica.

I nuovi dati sono sempre considerati di importanza immediata. I dati ad accesso frequente vengono inseriti e aggiornati nella tabella ottimizzata per la memoria. I dati a freddo vengono mantenuti in una tabella partizionata tradizionale. Periodicamente, una stored procedure aggiunge una nuova partizione. La partizione contiene i dati freddi più recenti spostati dalla tabella ottimizzata per la memoria.

Se un'operazione richiede solo dati caldi, può utilizzare stored procedure compilate nativamente per accedervi. Per unire la tabella ottimizzata per la memoria con la tabella partizionata, le operazioni che potrebbero accedere a dati caldi o freddi devono usare istruzioni Transact-SQL interpretate.

Aggiungere una partizione

I dati che di recente sono divenuti freddi devono essere spostati nella tabella partizionata. I passaggi per questo scambio di partizione periodico sono i seguenti:

1. Per i dati nella tabella ottimizzata per la memoria, determinare la data e l'ora che rappresenta il limite tra i dati frequentemente accessibili e quelli che sono diventati recentemente meno accessibili.

2. Inserire i nuovi dati non frequentemente accessibili, dalla tabella OLTP In-Memory, in una cold_staging tabella.

3. Eliminare i dati freddi dalla tabella ottimizzata per la memoria.

4. Spostare la cold_staging tabella in una partizione.

5. Aggiungere la partizione.

Finestra di manutenzione

Uno dei passaggi precedenti consiste nell'eliminare i dati diventati di recente poco utilizzati dalla tabella ottimizzata per l'uso della memoria. È previsto un intervallo di tempo tra questa eliminazione e il passaggio finale che aggiunge la nuova partizione. Durante questo intervallo, qualsiasi applicazione che tenta di leggere i dati freddi fallisce.

Per un esempio correlato, vedere Partizionamento a livello di applicazione.

Esempio di codice

L'esempio Transact-SQL seguente viene presentato in una serie di blocchi di codice più piccoli, solo per la facilità di presentazione. È possibile aggiungerli tutti in un unico grande blocco di codice per i test.

Nel suo complesso, l'esempio T-SQL illustra come usare una tabella ottimizzata per la memoria con una tabella basata su disco partizionata.

Nelle prime fasi dell'esempio T-SQL viene creato il database, quindi vengono creati gli oggetti, come le tabelle nel database. Nelle fasi successive viene illustrato come spostare i dati da una tabella ottimizzata per la memoria in una tabella partizionata.

Creare un database

Questa sezione dell'esempio T-SQL crea un database di prova. Il database è configurato in modo da supportare sia tabelle ottimizzate per la memoria che tabelle partizionate.

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

Creare una tabella ottimizzata per la memoria per i dati ad accesso frequente

Questa sezione crea la tabella ottimizzata per la memoria che contiene i dati più recenti, in gran parte dati ancora ad accesso frequente.

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

Creare una tabella partizionata per dati freddi

In questa sezione viene creata la tabella partizionata che contiene i dati freddi.

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

Creare una tabella per archiviare i dati freddi durante il trasferimento

In questa sezione viene creata la tabella cold_staging. È creata anche una vista che unisce i dati caldi e freddi dalle due tabelle.

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

Crea la procedura memorizzata

In questa sezione viene creata la procedura memorizzata da eseguire periodicamente. La procedura sposta i dati recentemente diventati freddi dalla tabella ottimizzata per la memoria alla tabella partizionata.

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

Preparare i dati di esempio ed eseguire la stored procedure

Questa sezione genera e inserisce dati di esempio, quindi esegue la stored procedure a titolo dimostrativo.

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

Eliminare tutti gli oggetti dimostrativi

Ricordarsi di rimuovere il database di test dimostrativo dal sistema di test.

USE master;
GO

DROP DATABASE PartitionSample;
GO

Contenuti correlati

• Database di esempio per OLTP in memoria