Wzorzec aplikacji do partycjonowania tabel zoptymalizowanych pod kątem pamięci

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

In-Memory OLTP obsługuje wzorzec projektu aplikacji, który określa priorytet wydajności dla bieżących danych. Ten wzorzec ma zastosowanie, gdy bieżące dane są odczytywane lub aktualizowane znacznie częściej niż starsze dane. Bieżące dane są uznawane za aktywne lub gorące, a starsze dane są zimne.

Głównym pomysłem jest przechowywanie gorących danych w tabeli zoptymalizowanej pod kątem pamięci. Co tydzień lub co miesiąc starsze dane, które stają się zimne , są przenoszone do tabeli podzielonej na partycje. Tabela partycjonowana zawiera dane przechowywane na dysku lub innym dysku twardym, a nie w pamięci.

Zazwyczaj ten projekt używa klucza datetime2 , aby umożliwić procesowi przenoszenia efektywne rozróżnienie między gorącymi a zimnymi danymi.

Partycjonowanie zaawansowane

Projekt ma na celu naśladowanie partycjonowanej tabeli, która ma również jedną pamięciowo zoptymalizowaną partycję. Aby ten projekt działał, upewnij się, że tabele mają wspólny schemat. Przykładowy kod w dalszej części tego artykułu przedstawia technikę.

Nowe dane są zawsze traktowane jako gorące. Gorące dane są wstawiane i aktualizowane w tabeli zoptymalizowanej pod kątem pamięci. Zimne dane są przechowywane w tradycyjnej tabeli partycjonowanej. Okresowo procedura składowana dodaje nową partycję. Partycja zawiera najnowsze zimne dane, które zostały przeniesione z tabeli zoptymalizowanej pod kątem pamięci.

Jeśli operacja wymaga tylko gorących danych, może używać natywnie skompilowanych procedur składowanych w celu uzyskania dostępu do danych. Operacje, które mogą uzyskiwać dostęp do gorących lub zimnych danych, muszą używać interpretowanego języka Transact-SQL, aby połączyć tabelę zoptymalizowaną pod kątem pamięci z tabelą partycjonowaną.

Dodawanie partycji

Dane, które ostatnio stały się zimne, muszą zostać przeniesione do tabeli partycjonowanej. Kroki dotyczące tej okresowej zamiany partycji są następujące:

1. W przypadku danych w tabeli zoptymalizowanej pod kątem pamięci określ datę/godzinę, która jest granicą lub odcięciem między gorącymi a nowo zimnymi danymi.

2. Wstaw świeżo schłodzone dane z tabeli In-Memory OLTP do tabeli cold_staging.

3. Usuń te same zimne dane z tabeli zoptymalizowanej pod kątem pamięci.

4. Zamień tabelę cold_staging na partycję.

5. Dodaj partycję.

Okno obsługi

Jednym z powyższych kroków jest usunięcie nowo zimnych danych z tabeli zoptymalizowanej pod kątem pamięci. Istnieje przedział czasu między tym usunięciem a ostatnim krokiem, który dodaje nową partycję. W tym interwale każda aplikacja, która próbuje odczytać nowo zimne dane, kończy się niepowodzeniem.

Aby zapoznać się z powiązanym przykładem, zobacz Partycjonowanie na poziomie aplikacji.

Przykład kodu

Poniższy przykład Transact-SQL jest wyświetlany w serii mniejszych bloków kodu, tylko w celu ułatwienia prezentacji. Możesz dołączyć je wszystkie do jednego dużego bloku kodu na potrzeby testowania.

W całości przykład T-SQL pokazuje, jak używać tabeli zoptymalizowanej pod kątem pamięci z partycjonowaną tabelą opartą na dyskach.

Pierwsze fazy przykładu języka T-SQL tworzą bazę danych, a następnie tworzą obiekty, takie jak tabele w bazie danych. W kolejnych fazach pokazano, jak przenieść dane z tabeli zoptymalizowanej pod kątem pamięci do tabeli partycjonowanej.

Tworzenie bazy danych

Ta sekcja przykładu języka T-SQL tworzy testową bazę danych. Baza danych jest skonfigurowana do obsługi tabel zoptymalizowanych pod kątem pamięci i tabel partycjonowanych.

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

Tworzenie tabeli zoptymalizowanej pod kątem pamięci dla gorących danych

Ta sekcja tworzy tabelę zoptymalizowaną pod kątem pamięci, która zawiera najnowsze dane, które są w większości gorącymi danymi.

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

Tworzenie tabeli partycjonowanej dla zimnych danych

Ta sekcja tworzy partycjonowaną tabelę, która przechowuje zimne dane.

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

Tworzenie tabeli do przechowywania zimnych danych podczas przenoszenia

Ta sekcja tworzy tabelę cold_staging . Tworzony jest również widok, który łączy gorące i zimne dane z obu tabel.

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

Utwórz procedurę składowaną

Ta sekcja tworzy procedurę składowaną uruchamianą okresowo. Procedura przenosi nowo zimne dane z tabeli zoptymalizowanej pod kątem pamięci do tabeli partycjonowanej.

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

Przygotuj przykładowe dane i zademonstruj procedurę składowaną

Ta sekcja generuje i wstawia przykładowe dane, a następnie uruchamia procedurę składowaną jako przykład.

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

Upuść wszystkie obiekty przykładowe

Pamiętaj, aby wyczyścić demonstracyjną bazę danych testową z twojego systemu testowego.

USE master;
GO

DROP DATABASE PartitionSample;
GO

Treści powiązane

• Przykładowa baza danych dla olTP w pamięci