Anwendungsmuster für die Partitionierung speicheroptimierter Tabellen

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed Instance

In-Memory OLTP unterstützt ein Anwendungsentwurfsmuster, das die Leistung für aktuelle Daten priorisiert. Dieses Muster gilt, wenn aktuelle Daten wesentlich häufiger gelesen oder aktualisiert werden als ältere Daten. Die aktuellen Daten werden als aktiv oder heiß betrachtet, und die älteren Daten sind kalt.

Der Hauptgedanke besteht darin, heiße Daten in einer speicheroptimierten Tabelle zu speichern. Auf wöchentlicher oder monatlicher Basis werden ältere Daten, die kalt werden, in eine partitionierte Tabelle verschoben. In der partitionierten Tabelle werden die Daten auf einem Datenträger oder auf einer anderen Festplatte und nicht im Arbeitsspeicher gespeichert.

In der Regel verwendet dieser Entwurf einen Datetime2-Schlüssel , um den Verschiebungsprozess zu ermöglichen, um zwischen heißen und kalten Daten effizient zu unterscheiden.

Erweiterte Partitionierung

Der Entwurf soll eine partitionierte Tabelle mit einer speicheroptimierten Partition imitieren. Damit dieser Entwurf funktioniert, stellen Sie sicher, dass die Tabellen ein gemeinsames Schema verwenden. Das Codebeispiel weiter unten in diesem Artikel veranschaulicht dieses Verfahren.

Neue Daten werden immer als heiß betrachtet. Heiße Daten werden in die speicheroptimierte Tabelle eingefügt und aktualisiert. Kalte Daten werden in der herkömmlichen partitionierten Tabelle beibehalten. Eine gespeicherte Prozedur fügt in regelmäßigen Abständen eine neue Partition hinzu. Die Partition enthält die neuesten Kaltdaten, die aus der speicheroptimierten Tabelle verschoben wurden.

Wenn für einen Vorgang nur heiße Daten benötigt werden, können nativ kompilierte gespeicherte Prozeduren für den Zugriff auf die Daten verwendet werden. Vorgänge, bei denen auf heiße oder kalte Daten zugegriffen wird, müssen interpretiertes Transact-SQL verwenden, um die speicheroptimierte Tabelle mit der partitionierten Tabelle zu verknüpfen.

Hinzufügen einer Partition

Daten, die kürzlich kalt wurden, müssen in die partitionierte Tabelle verschoben werden. Die Schritte für diesen regelmäßigen Partitionsaustausch lauten wie folgt:

1. Legen Sie für die Daten in der speicheroptimierten Tabelle den datetime-Wert fest, der die Grenze bzw. den Umstellungszeitpunkt für die heißen bzw. neuen kalten Daten darstellt.

2. Fügen Sie die neuen, kalten Daten aus der In-Memory-OLTP-Tabelle in eine cold_staging-Tabelle ein.

3. Löschen Sie diese kalten Daten aus der speicheroptimierten Tabelle.

4. Verschieben Sie die cold_staging Tabelle in eine Partition.

5. Fügen Sie die Partition hinzu.

Wartungsfenster

Einer der vorangegangenen Schritte besteht darin, die neuen kalten Daten aus der speicheroptimierten Tabelle zu löschen. Es gibt ein Zeitintervall zwischen diesem Löschvorgang und dem letzten Schritt, der die neue Partition hinzufügt. In diesem Intervall schlägt jede Anwendung, die versucht, die neu kalten Daten zu lesen, fehl.

Ein Beispiel dazu finden Sie unter Partitionierung auf Anwendungsebene.

Codebeispiel

Das folgende Transact-SQL-Beispiel wird nur für die vereinfachte Darstellung in mehreren kleineren Codeblöcken angezeigt. Für Ihre Tests könnten Sie alle an einen großen Codeblock anfügen.

Insgesamt zeigt das T-SQL-Beispiel, wie eine speicheroptimierte Tabelle mit einer partitionierten, datenträgerbasierten Tabelle verwendet wird.

In den ersten Phasen des T-SQL-Beispiels werden zunächst die Datenbank und anschließend Objekte wie Tabellen in der Datenbank erstellt. In den weiteren Phasen wird gezeigt, wie Daten aus einer speicheroptimierten Tabelle in eine partitionierte Tabelle verschoben werden.

Erstellen einer Datenbank

In diesem Abschnitt des T-SQL-Beispiels wird eine Testdatenbank erstellt. Die Datenbank ist so konfiguriert, dass sowohl speicheroptimierte Tabellen als auch partitionierte Tabellen unterstützt werden.

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

Erstellen einer speicheroptimierten Tabelle für heiße Daten

In diesem Abschnitt wird die speicheroptimierte Tabelle erstellt, in der die neuesten Daten enthalten sind. Bei diesen Daten handelt es sich noch immer größtenteils um heiße Daten.

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

Erstellen einer partitionierten Tabelle für kalte Daten

In diesem Abschnitt wird die partitionierte Tabelle erstellt, in der die kalten Daten enthalten sind.

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

Erstellen einer Tabelle zum Speichern kalter Daten während der Verschiebung

In diesem Abschnitt wird die cold_staging Tabelle erstellt. Es wird auch eine Ansicht erstellt, in der die aktiven und kalten Daten aus den beiden Tabellen zusammengeführt werden.

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

Erstellen der gespeicherten Prozedur

In diesem Abschnitt wird die gespeicherte Prozedur erstellt, die Sie regelmäßig ausführen. Die Prozedur verschiebt neue kalte Daten aus der speicheroptimierten Tabelle in die partitionierte Tabelle.

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

Vorbereiten von Beispieldaten und Veranschaulichung der gespeicherten Prozedur

In diesem Abschnitt werden Beispieldaten generiert und eingefügt. Anschließend wird die gespeicherte Prozedur zur Veranschaulichung ausgeführt.

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

Löschen aller für die Veranschaulichung verwendeten Objekte

Denken Sie daran, die für die Veranschaulichung verwendete Testdatenbank vom Testsystem zu entfernen.

USE master;
GO

DROP DATABASE PartitionSample;
GO

Verwandte Inhalte

• Beispieldatenbank für IN-Memory-OLTP