Toepassingspatroon voor het partitioneren van tabellen die zijn geoptimaliseerd voor geheugen

Van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

In-Memory OLTP ondersteunt een toepassingsontwerppatroon dat prioriteit geeft aan de prestaties voor huidige gegevens. Dit patroon is van toepassing wanneer de huidige gegevens aanzienlijk vaker worden gelezen of bijgewerkt dan oudere gegevens. De huidige gegevens worden als actief of dynamisch beschouwd en de oudere gegevens zijn koud.

Het belangrijkste idee is het opslaan van dynamische gegevens in een tabel die is geoptimaliseerd voor geheugen. Op wekelijkse of maandelijkse basis worden oudere gegevens die koud worden, verplaatst naar een gepartitioneerde tabel. De gepartitioneerde tabel bevat de gegevens die zijn opgeslagen op een schijf of een andere harde schijf, niet in het geheugen.

Gewoonlijk gebruikt dit ontwerp een datetime2-sleutel om het verplaatsingsproces efficiënt onderscheid te laten maken tussen actieve en koude gegevens.

Geavanceerde partitionering

Het ontwerp wil een gepartitioneerde tabel nabootsen die ook één partitie heeft die is geoptimaliseerd voor geheugen. Dit ontwerp werkt alleen als de tabellen een gemeenschappelijk schema delen. Het codevoorbeeld verderop in dit artikel bevat de techniek.

Nieuwe gegevens worden altijd als dynamisch beschouwd. Actuele gegevens worden ingevoegd en bijgewerkt in de geheugen-geoptimaliseerde tabel. Koude gegevens worden onderhouden in de traditionele gepartitioneerde tabel. Periodiek voegt een opgeslagen procedure een nieuwe partitie toe. De partitie bevat de meest recente koude gegevens die zijn verplaatst uit de tabel die is geoptimaliseerd voor geheugen.

Als een bewerking alleen dynamische gegevens nodig heeft, kan deze systeemeigen gecompileerde opgeslagen procedures gebruiken om toegang te krijgen tot de gegevens. Bewerkingen die toegang kunnen krijgen tot dynamische of koude gegevens, moeten geïnterpreteerde Transact-SQL gebruiken om de tabel die is geoptimaliseerd voor geheugen te koppelen aan de gepartitioneerde tabel.

Een partitie toevoegen

Gegevens die onlangs koud zijn geworden, moeten worden verplaatst naar de gepartitioneerde tabel. De stappen voor deze periodieke partitiewisseling zijn als volgt:

1. Bepaal voor de gegevens in de geheugen-geoptimaliseerde tabel de datum/tijd die de scheiding vormt tussen veelvuldig gebruikte en nieuw koude gegevens.

2. Voeg de zojuist koude gegevens uit de In-Memory OLTP-tabel in een cold_staging tabel in.

3. Verwijder dezelfde koude gegevens uit de tabel die is geoptimaliseerd voor geheugen.

4. Verplaats de cold_staging tabel naar een partitie.

5. Voeg de partitie toe.

Onderhoudsvenster

Een van de voorgaande stappen is het verwijderen van de zojuist koude gegevens uit de tabel die is geoptimaliseerd voor geheugen. Er is een tijdsinterval tussen deze verwijdering en de laatste stap waarmee de nieuwe partitie wordt toegevoegd. Tijdens dit interval mislukt elke toepassing die probeert de zojuist koude gegevens te lezen.

Zie Application-Level Partitionering voor een gerelateerd voorbeeld.

Codevoorbeeld

Het volgende Transact-SQL voorbeeld wordt weergegeven in een reeks kleinere codeblokken, alleen voor het gemak van de presentatie. U kunt ze allemaal toevoegen aan één groot codeblok voor uw tests.

Als geheel laat het T-SQL-voorbeeld zien hoe u een tabel gebruikt die is geoptimaliseerd voor geheugen met een gepartitioneerde tabel op basis van een schijf.

De eerste fasen van het T-SQL-voorbeeld maken de database en maken vervolgens objecten zoals tabellen in de database. Latere fasen laten zien hoe u gegevens verplaatst van een tabel die is geoptimaliseerd voor geheugen naar een gepartitioneerde tabel.

Een database maken

In deze sectie van het T-SQL-voorbeeld wordt een testdatabase gemaakt. De database is geconfigureerd ter ondersteuning van tabellen die zijn geoptimaliseerd voor geheugen en gepartitioneerde tabellen.

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

Een geheugen-geoptimaliseerde tabel maken voor veelgebruikte gegevens.

In deze sectie maakt u de tabel die is geoptimaliseerd voor geheugen, waarin de meest recente gegevens zijn opgeslagen. Dit zijn meestal nog steeds dynamische gegevens.

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

Een gepartitioneerde tabel maken voor koude gegevens

In deze sectie wordt de gepartitioneerde tabel gemaakt die de koude gegevens bevat.

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

Een tabel maken om koude gegevens op te slaan tijdens de verplaatsing

In deze sectie wordt de cold_staging tabel gemaakt. Er wordt ook een weergave gemaakt die de dynamische en koude gegevens uit de twee tabellen samenvoegt.

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

De opgeslagen procedure maken

In deze sectie maakt u de procedure aan die u periodiek uitvoert. Met de procedure worden nieuwe koude gegevens uit de tabel die is geoptimaliseerd voor geheugen verplaatst naar de gepartitioneerde tabel.

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

Voorbeeldgegevens voorbereiden en de opgeslagen procedure demonstreren.

In deze sectie worden voorbeeldgegevens gegenereerd en ingevoegd, waarna de opgeslagen procedure wordt uitgevoerd als demonstratie.

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

Alle demo-objecten verwijderen

Vergeet niet om de demotestdatabase van uw testsysteem op te schonen.

USE master;
GO

DROP DATABASE PartitionSample;
GO

Verwante inhoud

• Voorbeelddatabase voor OLTP in het geheugen