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

아티클
08/21/2023

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

메모리 내 OLTP는 상대적으로 현재 데이터에 성능 리소스를 제공하는 애플리케이션 디자인 패턴을 지원합니다. 이 패턴은 현재 데이터를 이전 데이터보다 훨씬 더 자주 읽거나 업데이트할 때 적용할 수 있습니다. 이 경우 최신 데이터가 활성 또는 핫하다고 말하며 오래된 데이터는 콜드하다고 합니다.

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

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

고급 분할

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

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

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

파티션 추가

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

메모리 최적화 테이블의 데이터에 대해 핫 데이터와 새로 콜드 데이터 간의 경계 또는 차단인 날짜/시간을 결정합니다.
메모리 내 OLTP 테이블에서 새로 콜드 데이터를 cold_staging 테이블에 삽입합니다.
메모리 최적화 테이블에서 동일한 콜드 데이터를 삭제합니다.
cold_staging 테이블을 파티션으로 교환합니다.
파티션을 추가합니다.

유지 관리 기간

이전 단계 중 하나는 메모리 최적화 테이블에서 새로운 콜드 데이터를 삭제하는 것입니다. 이 삭제와 새 파티션을 추가하는 마지막 단계 사이에는 시간 간격이 있습니다. 이 간격 동안 새로 콜드 데이터를 읽으려는 모든 애플리케이션이 실패합니다.

관련 샘플은 애플리케이션 수준 분할을 참조 하세요.

코드 예시

다음 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),
   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 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

샘플 데이터 준비 및 저장 프로시저 데모

이 섹션에서는 샘플 데이터를 생성하고 삽입한 다음 저장 프로시저를 데모용으로 실행합니다.

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

모든 데모 개체 삭제

테스트 시스템에서 데모 테스트 데이터베이스를 제거해야 합니다.

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

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

DROP DATABASE PartitionSample;
GO

참고 항목

메모리 최적화 테이블

다음을 통해 공유