Programmönster för partitionering av minnesoptimerade tabeller

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

In-Memory OLTP stöder ett mönster för programdesign som prioriterar prestanda för aktuella data. Det här mönstret gäller när aktuella data läse eller uppdateras betydligt oftare än äldre data. Aktuella data anses vara aktiva eller heta och de äldre data är kalla.

Huvudtanken är att lagra frekventa data i en minnesoptimerad tabell. På vecko- eller månadsbasis flyttas äldre data som blir kalla till en partitionerad tabell. Den partitionerade tabellen har sina data lagrade på en disk eller annan hårddisk, inte i minnet.

Den här designen använder vanligtvis en datetime2-nyckel för att möjliggöra en effektiv differentiering mellan aktiv och inaktiv data under flyttprocessen.

Avancerad partitionering

Designen syftar till att efterlikna att ha en partitionerad tabell som också har en minnesoptimerad partition. För att den här designen ska fungera kontrollerar du att tabellerna delar ett gemensamt schema. Kodexemplet senare i den här artikeln visar tekniken.

Nya data anses alltid vara heta. Frekventa data infogas och uppdateras i den minnesoptimerade tabellen. Kall data lagras i den traditionella partitionerade tabellen. Med jämna mellanrum lägger en lagrad procedur till en ny partition. Partitionen innehåller de senaste kalla data som flyttades från den minnesoptimerade tabellen.

Om en åtgärd bara behöver heta data kan den använda nativt kompilerade lagrade procedurer för att nå datan. Åtgärder som kan komma åt frekventa eller kalla data måste använda tolkad Transact-SQL för att ansluta den minnesoptimerade tabellen till den partitionerade tabellen.

Lägga till en partition

Data som nyligen blev kalla måste flyttas till den partitionerade tabellen. Stegen för den här periodiska partitionsväxlingen är följande:

1. För data i den minnesoptimerade tabellen fastställer du den datetime som är gränsen eller skärningspunkten mellan het och nyss kall data.

2. Infoga de nyligen kalla data från tabellen In-Memory OLTP i en cold_staging tabell.

3. Ta bort samma kalla data från den minnesoptimerade tabellen.

4. Växla tabellen cold_staging till en partition.

5. Lägg till partitionen.

Underhållsfönster

Ett av föregående steg är att ta bort de nyligen kalla data från den minnesoptimerade tabellen. Det finns ett tidsintervall mellan den här borttagningen och det sista steget som lägger till den nya partitionen. Under det här intervallet misslyckas alla program som försöker läsa de nyligen kalla data.

För ett relaterat exempel, se Application-Level partitionering.

Kodexempel

Följande Transact-SQL exempel visas i en serie mindre kodblock, bara för att underlätta presentationen. Du kan lägga till alla i ett stort kodblock för testningen.

Som helhet visar T-SQL-exemplet hur du använder en minnesoptimerad tabell med en partitionerad diskbaserad tabell.

De första faserna i T-SQL-exemplet skapar databasen och skapar sedan objekt som tabeller i databasen. Senare faser visar hur du flyttar data från en minnesoptimerad tabell till en partitionerad tabell.

Skapa en databas

Det här avsnittet i T-SQL-exemplet skapar en testdatabas. Databasen är konfigurerad för att stödja både minnesoptimerade tabeller och partitionerade tabeller.

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

Skapa en minnesoptimerad tabell för frekventa data

Det här avsnittet skapar den minnesoptimerade tabellen som innehåller de senaste data, som oftast fortfarande är heta data.

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

Skapa en partitionerad tabell för kalla data

Det här avsnittet skapar den partitionerade tabellen som innehåller kalla data.

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

Skapa en tabell för att lagra kalla data under flytt

Det här avsnittet skapar cold_staging tabellen. En vy som förenar heta och kalla data från de två tabellerna skapas också.

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

Skapa den lagrade proceduren

Det här avsnittet skapar den lagrade procedur som du kör med jämna mellanrum. Proceduren flyttar nyligen kalla data från den minnesoptimerade tabellen till den partitionerade tabellen.

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

Förbereda exempeldata och demonstrera den lagrade proceduren

Det här avsnittet genererar och infogar exempeldata och kör sedan den lagrade proceduren som en demonstration.

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

Släpp alla demoobjekt

Kom ihåg att rensa demotestdatabasen från testsystemet.

USE master;
GO

DROP DATABASE PartitionSample;
GO

Relaterat innehåll

• Exempeldatabas för minnesintern OLTP