Dela via

Programmeringsmö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 optimerar prestandaresurser för relativt aktuella data. Det här mönstret kan gälla när aktuella data läses eller uppdateras mycket oftare än äldre data är. I det här fallet säger vi att aktuella data är aktiva eller heta och att de äldre data är kalla.

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

Vanligtvis använder den här designen en datetime-nyckel för att göra det möjligt för omflyttningsprocessen att effektivt skilja mellan het och kall data.

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 måste du se till att alla tabeller delar ett gemensamt schema. Kodexemplet senare i den här artikeln visar tekniken.

Nya data antas vara heta per definition. 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 har flyttats 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 har blivit 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 In-Memory OLTP-tabellen i en cold_staging tabell.
  3. Ta bort samma kalla data från den minnesoptimerade tabellen.
  4. Omvandla cold_staging-tabellen 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 tabellen "cold_staging". 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),
   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

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

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

Släpp alla demoobjekt

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

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

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

DROP DATABASE PartitionSample;
GO

Se även

Memory-Optimized tabeller

  • Last updated on