메모리 최적화 테이블을 분할하기 위한 애플리케이션 패턴

적용 대상:SQL ServerAzure SQL 데이터베이스Azure SQL Managed Instance

In-Memory OLTP는 현재 데이터의 성능 우선 순위를 지정하는 애플리케이션 디자인 패턴을 지원합니다. 이 패턴은 현재 데이터가 이전 데이터보다 훨씬 자주 읽거나 업데이트되는 경우에 적용됩니다. 현재 데이터는 활성 또는 핫으로 간주되며 이전 데이터는 콜드입니다.

주요 개념은 메모리 최적화 테이블에 핫 데이터를 저장하는 것입니다. 매주 또는 매월 콜드 가 되는 이전 데이터는 분할된 테이블로 이동됩니다. 분할된 테이블에는 메모리가 아닌 디스크 또는 다른 하드 드라이브에 저장된 데이터가 있습니다.

일반적으로 이 디자인은 datetime2 키를 사용하여 이동 프로세스가 핫 데이터와 콜드 데이터를 효율적으로 구분할 수 있도록 합니다.

고급 분할

이 디자인은 하나의 메모리 최적화 파티션이 있는 분할된 테이블을 모방하려고 합니다. 이 디자인이 작동하려면 테이블이 공통 스키마를 공유해야 합니다. 이 문서의 뒷부분에 있는 코드 샘플은 이 기술을 보여줍니다.

새 데이터는 항상 핫으로 간주됩니다. 핫 데이터는 메모리 최적화 테이블에 삽입되고 업데이트됩니다. 콜드 데이터는 기존의 분할된 테이블에서 유지 관리됩니다. 저장 프로시저는 주기적으로 새 파티션을 추가합니다. 파티션에는 메모리 최적화 테이블에서 이동된 최신 콜드 데이터가 포함됩니다.

작업에 핫 데이터만 필요한 경우 고유하게 컴파일된 저장 프로시저를 사용하여 데이터에 액세스할 수 있습니다. 핫 또는 콜드 데이터에 액세스할 수 있는 작업은 해석된 Transact-SQL를 사용하여 메모리 최적화 테이블을 분할된 테이블과 조인해야 합니다.

파티션 추가

최근에 콜드가 된 데이터는 분할된 테이블로 이동해야 합니다. 이 정기적인 파티션 교환 단계는 다음과 같습니다.

1. 메모리 최적화 테이블의 데이터에 대해 핫 데이터와 새로 콜드 데이터 간의 경계 또는 차단인 날짜/시간을 결정합니다.

2. In-Memory 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용 샘플 데이터베이스