Alkalmazásminta a memóriára optimalizált táblák particionálásához

A következőkre vonatkozik:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Az In-Memory OLTP olyan alkalmazástervezési mintát támogat, amely kiemelt teljesítményerőforrásokat biztosít a viszonylag aktuális adatokhoz. Ez a minta akkor alkalmazható, ha az aktuális adatokat sokkal gyakrabban olvassák vagy frissítik, mint a régebbi adatok. Ebben az esetben azt mondjuk, hogy az aktuális adatok aktívak vagy gyakoriak, a régebbi adatok pedig hidegek.

A fő cél a gyakori adatok tárolása egy memóriaoptimalizált táblázatban. Heti vagy havi rendszerességgel a régebbi, kihűlt adatok egy particionált táblába kerülnek. A particionált tábla adatai lemezen vagy más merevlemezen vannak tárolva, nem a memóriában.

Ez a kialakítás általában datetime kulcsot használ az áthelyezési folyamat hatékony lehetővé tételéhez, hogy megkülönböztesse a forró és hideg adatokat.

Speciális particionálás

A terv egy olyan particionált táblát szeretne utánozni, amely egy memóriaoptimalizált partícióval is rendelkezik. Ahhoz, hogy ez a terv működjön, gondoskodnia kell arról, hogy a táblák közös sémával rendelkezzenek. A jelen cikk későbbi részében szereplő kódminta a technikát mutatja be.

Az új adatokat definíció szerint forrónak tekintik. A rendszer gyakori adatokat szúr be és frissít a memóriaoptimalizált táblában. A hideg adatokat a hagyományos particionált táblában tárolják. A tárolt eljárás időnként új partíciót ad hozzá. A partíció a memóriaoptimalizált táblából áthelyezett legújabb hideg adatokat tartalmazza.

Ha egy műveletnek csak gyorsan elérhető adatokra van szüksége, natív módon fordított tárolt eljárásokat használhat az adatok eléréséhez. A gyakori vagy ritka elérésű adatokhoz hozzáférő műveleteknek értelmezett Transact-SQL-t kell használniuk, hogy összekapcsolják a memóriaoptimalizált táblát a particionált táblával.

Partíció hozzáadása

A nemrég kihűlt adatokat át kell helyezni a particionált táblába. Az időszakos partíciós felcserélés lépései a következők:

1. A memóriaoptimalizált táblában lévő adatok esetében határozza meg azt az időbélyeget, amely elválasztja a forró és az archivált adatokat.
2. Szúrja be az újonnan hideg adatokat az In-Memory OLTP-táblából egy cold_staging táblába.
3. Törölje ugyanazokat a hideg adatokat a memóriaoptimalizált táblából.
4. Cserélje át a cold_staging táblát egy partícióba.
5. Adja hozzá a partíciót.

Karbantartási időszak

Az előző lépések egyike az újonnan kihűlt adatok törlése a memóriaoptimalizált táblából. A törlés és az új partíciót hozzáadó utolsó lépés között van egy időintervallum. Ebben az intervallumban az újonnan hideg adatokat beolvasni próbáló alkalmazások sikertelenek lesznek.

Kapcsolódó minta megtekintéséhez lásd: Application-Level Particionálás.

Kód minta

A következő Transact-SQL minta kisebb kódblokkokban jelenik meg, csak a könnyű megjelenítés érdekében. Ezeket egy nagy kódblokkba fűzheti a teszteléshez.

A T-SQL-minta összességében azt mutatja be, hogyan használható memóriaoptimalizált tábla particionált lemezalapú táblával.

A T-SQL-minta első fázisai létrehozzák az adatbázist, majd objektumokat, például táblákat hoznak létre az adatbázisban. A későbbi fázisok bemutatják, hogyan helyezhetők át adatok a memóriaoptimalizált táblából particionált táblába.

Adatbázis létrehozása

A T-SQL-minta ezen szakasza létrehoz egy tesztadatbázist. Az adatbázis úgy van konfigurálva, hogy a memóriaoptimalizált és a particionált táblákat is támogassa.

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

Memóriaoptimalizált táblázat létrehozása gyakori adatokhoz

Ez a szakasz létrehozza a legújabb adatokat tartalmazó memóriaoptimalizált táblát, amely többnyire még mindig forró adatok.

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

Particionált tábla létrehozása hideg adatokhoz

Ez a szakasz létrehozza a hideg adatokat tartalmazó particionált táblát.

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

"Táblázat létrehozása a ritkán elérhető adatok áthelyezés alatti tárolására"

Ez a szakasz létrehozza a cold_staging táblát. A két tábla forró és hideg adatainak egyesítő nézete is létrejön.

-- 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),
   INDEX ix_date_total NONCLUSTERED (so_date desc, so_total desc),
   CONSTRAINT CHK_SalesOrders_cold_staging CHECK (so_date >= '1900-01-01')
);
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

A tárolt eljárás létrehozása

Ez a szakasz létrehozza a periodikusan futtatandó tárolt eljárást. Az eljárás áthelyezi az újonnan hideg adatokat a memóriaoptimalizált táblából a particionált táblába.

-- 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 FROM 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 INT = (
        SELECT MAX(partition_number)
            FROM sys.partitions
            WHERE object_id = OBJECT_ID('dbo.SalesOrders_cold'));

      EXEC 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 nvarchar( 100) = CONVERT( nvarchar( 100) , @splitdate , 121);
      DECLARE @sql nvarchar( 1000) = N'alter table dbo.SalesOrders_cold_staging 
         add constraint CHK_SalesOrders_cold_staging check (so_date > ''' + @s + ''')';
      PRINT @sql;
      EXEC sp_executesql @sql;

      COMMIT;
END;
GO

Mintaadatok előkészítése és a tárolt eljárás bemutatása

Ez a szakasz mintaadatokat hoz létre és szúr be, majd bemutatóként futtatja a tárolt eljárást.

-- 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 datetime2 = SYSDATETIME();
EXEC 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 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 datetime2 = SYSDATETIME();
EXEC 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 ps
  WHERE object_id = OBJECT_ID( 'dbo.SalesOrders_cold')
    AND index_id = 1;

Az összes demóobjektum elvetése

Ne felejtse el megtisztítani a demóteszt-adatbázist a tesztrendszerből.

-- You must first leave the context of the PartitionSample database.

-- USE <A-Database-Name-Here>;
GO

DROP DATABASE PartitionSample;
GO

Lásd még:

Memory-Optimized táblák