Шаблон приложения для секционирования оптимизированных для памяти таблиц

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

In-Memory OLTP поддерживает шаблон проектирования приложения, который обеспечивает повышение производительности для актуальных данных. Этот шаблон применяется, если текущие данные считываются или обновляются значительно чаще, чем старые данные. Текущие данные считаются активными или горячими, а старые — холодными.

Основная идея заключается в хранении горячих данных в таблице, оптимизированной для памяти. На еженедельной или ежемесячной основе старые данные, которые становятся холодными , перемещаются в секционированную таблицу. Данные секционированной таблицы хранятся на диске, например на жестком диске, а не в памяти.

Как правило, этот дизайн использует ключ datetime2, чтобы процесс перемещения мог различать эффективно горячие и холодные данные.

Расширенное секционирование

В этой структуре предполагается имитировать секционированную таблицу, которая также содержит один раздел, оптимизированный для памяти. Чтобы этот дизайн работал, убедитесь, что таблицы имеют общую схему. В примере кода ниже в этой статье демонстрируется этот метод.

Новые данные всегда считаются актуальными. Горячие данные вставляются в таблицу, оптимизированную для памяти, и обновляются там же. Холодные данные хранятся в традиционной секционированной таблице. Периодически хранимая процедура добавляет новый раздел. Раздел содержит самые последние холодные данные, перенесённые из оптимизированной для памяти таблицы.

Если операция требует только горячих данных, она может использовать скомпилированные в собственном коде хранимые процедуры для доступа к ним. Операции, которые могут получить доступ к горячим или холодным данным, должны использовать интерпретируемый Transact-SQL для соединения таблицы, оптимизированной для памяти, с секционированной таблицей.

Добавление секции

Данные, которые недавно стали холодными, должны быть перемещены в секционированную таблицу. Ниже приведены шаги для этого периодического переключения разделов:

1. Для данных в таблице, оптимизированной для памяти, определите дату и время, которые являются границей, или разделом, между горячими и новыми холодными данными.

2. Вставьте недавно перемещенные в холодное хранилище данные из таблицы OLTP в памяти в таблицу cold_staging.

3. Удалите одни и те же холодные данные из таблицы, оптимизированной под память.

4. Переместите таблицу cold_staging в раздел.

5. Добавьте секцию.

Период обслуживания

Один из предыдущих шагов состоит в том, чтобы удалить новые холодные данные из таблицы, оптимизированной для памяти. Между этим удалением и последним шагом, добавляющим новую секцию, существует интервал времени. В течение этого интервала любое приложение, которое пытается считывать только что холодные данные, завершается сбоем.

Подходящий пример см. в разделе Секционирование уровня приложения.

Пример кода

Следующий пример Transact-SQL отображается в последовательности небольших блоков кода только для простоты представления. Их можно добавить в один большой блок кода для тестирования.

В целом пример T-SQL показывает, как использовать таблицу, оптимизированную для памяти, с секционированной таблицей на диске.

Первыми шагами выполнения примера T-SQL является создание базы данных, а затем создание объектов, в первую очередь таких, как таблицы, в базе данных. На последующих этапах показано перемещение данных из таблицы, оптимизированной для памяти, в секционированную таблицу.

Создание базы данных

В этой части примера T-SQL создается тестовая база данных. База данных настроена для поддержки оптимизированных для памяти таблиц и секционированных таблиц.

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

Создание оптимизированной для памяти таблицы для горячих данных

В этом разделе создается таблица, оптимизированная для памяти, содержащая последние данные, которые в основном остаются актуальными.

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

Создание секционированной таблицы для холодных данных

В этом разделе создается секционированная таблица, содержащая холодные данные.

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

Создание таблицы для хранения холодных данных во время перемещения

В этом разделе создается cold_staging таблица. Также создается представление, которое объединяет горячие и холодные данные из двух таблиц.

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

Создание хранимой процедуры

В этом разделе создается хранимая процедура, которая выполняется периодически. Процедура перемещает новые холодные данные из таблицы, оптимизированной для памяти, в секционированную таблицу.

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

Подготовка примера данных и демонстрация хранимой процедуры

Этот раздел создает и вставляет пример данных, а затем запускает хранимую процедуру как демонстрацию.

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

Удаление всех демонстрационных объектов

Не забудьте очистить демо-тестовую базу данных с вашей тестовой системы.

USE master;
GO

DROP DATABASE PartitionSample;
GO

Связанный контент

• Пример базы данных для OLTP в памяти