메모리 액세스에 최적화된 테이블 분할을 위한 응용 프로그램 패턴Application Pattern for Partitioning Memory-Optimized Tables

이 항목 적용 대상: 예SQL Server예Azure SQL 데이터베이스없습니다Azure SQL 데이터 웨어하우스 없습니다 병렬 데이터 웨어하우스THIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

메모리 내 OLTPIn-Memory OLTP는 디스크에서 자주 액세스하지 않는 데이터를 처리하는 동안 제한된 양의 활성 데이터를 메모리 최적화 테이블에 유지하는 패턴을 지원합니다. 메모리 내 OLTPIn-Memory OLTP supports a pattern where a limited amount of active data is kept in a memory-optimized table, while less-frequently accessed data is processed on disk. 일반적으로, 이는 datetime 키를 기준으로 데이터가 저장되는 시나리오가 될 것입니다.Typically, this would be a scenario where data is stored based on a datetime key.

분할된 테이블과 메모리 최적화 테이블을 공통 스키마로 유지하여 분할된 테이블을 메모리 최적화 테이블로 에뮬레이트할 수 있습니다.You can emulate partitioned tables with memory-optimized tables by maintaining a partitioned table and a memory-optimized table with a common schema. 현재 데이터는 메모리 최적화 테이블에 삽입되고 업데이트되는 반면, 자주 액세스하지 않는 데이터는 기존의 분할된 테이블에 유지됩니다.Current data would be inserted and updated in the memory-optimized table, while less-frequently accessed data would be maintained in the traditional partitioned table.

활성 데이터가 메모리 최적화 테이블에 있음을 알고 있는 응용 프로그램은 고유하게 컴파일된 저장 프로시저를 사용하여 데이터에 액세스할 수 있습니다.An application that knows that the active data is in a memory-optimized table can use natively compiled stored procedures to access the data. 전체 데이터에 액세스해야 하거나 어떤 테이블에 관련 데이터가 유지되는지 모를 수 있는 작업은 해석된 Transact-SQLTransact-SQL을 사용하여 메모리 최적화 테이블을 분할된 테이블과 조인합니다.Operations that need to access the entire span of data, or which may not know which table holds relevant data, use interpreted Transact-SQLTransact-SQL to join the memory-optimized table with the partitioned table.

이 파티션 전환은 다음과 같이 설명됩니다.This partition switch is described as follows:

  • 메모리 내 OLTP 테이블의 데이터를 준비 테이블에 삽입합니다. 가능하면 구분 날짜를 사용합니다.Insert data from the In-Memory OLTP table into a staging table, possibly using a cutoff date.

  • 메모리 최적화 테이블에서 같은 데이터를 삭제합니다.Delete the same data from the memory-optimized table.

  • 준비 테이블을 전환합니다.Swap in the staging table.

  • 활성 파티션을 추가합니다.Add the active partition.

    파티션 전환.Partition switch.
    활성 데이터 유지 관리Active Data Maintenance

    데이터 삭제와 준비 테이블 전환 사이의 시간 중에 누락된 데이터 쿼리가 수행되지 않도록 하려면 활성 주문 삭제로 시작되는 작업을 유지 관리 시간 중에 수행해야 합니다.The actions starting with Deleting ActiveOrders need to be done during a maintenance window to avoid queries missing data during the time between deleting data and switching in the staging table.

    관련 샘플을 보려면 응용 프로그램 수준 분할을 참조하세요.For a related sample, see Application-Level Partitioning.

코드 예제Code Sample

다음 예제에서는 분할된 디스크 기반 테이블과 함께 메모리 최적화 테이블을 사용하는 방법을 보여 줍니다.The following sample shows how to use a memory-optimized table with a partitioned disk-based table. 자주 사용되는 데이터는 메모리에 저장됩니다.Frequently-used data is stored in memory. 데이터를 디스크에 저장하려면 새 파티션을 만들고 분할된 테이블에 데이터를 복사합니다.To save the data to disk, create a new partition and copy the data to the partitioned table.

이 예제의 첫 번째 부분에서는 데이터베이스와 필요한 개체를 만듭니다.The first part of this sample creates the database and necessary objects. 예제의 두 번째 부분에서는 메모리 최적화 테이블에서 분할된 테이블로 데이터를 이동하는 방법을 보여 줍니다.The second part of the sample shows how to move data from a memory-optimized table into a partitioned table.

CREATE DATABASE partitionsample;  
GO  

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

-- frequently used portion of the SalesOrders - memory-optimized  

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  

-- cold portion of the SalesOrders - partitioned disk-based table  
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  

-- table for temporary partitions  
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  

-- aggregate view of the hot and 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_hot'  
          FROM dbo.SalesOrders_cold;  
GO  

-- move all sales orders up to the split date to cold storage  
CREATE PROCEDURE dbo.usp_SalesOrdersOffloadToCold @splitdate datetime2  
   AS  
   BEGIN  
      BEGIN TRANSACTION;  
      -- create new heap based on the hot data to be moved to cold storage  
      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;  

      -- remove moved data  
      DELETE FROM dbo.SalesOrders_hot WITH( SERIALIZABLE)  
         WHERE so_date <= @splitdate;  

      -- update partition function, and switch in 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 constraint on 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 in 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 contents of the table  
SELECT *  FROM dbo.SalesOrders;  
GO  

-- offload all sales orders to date to cold storage  
DECLARE  @t datetime2 = SYSDATETIME();  
EXEC dbo.usp_SalesOrdersOffloadToCold @t;  

-- verify contents of the tables  
SELECT * FROM dbo.SalesOrders;  
GO  

-- verify partitions  
SELECT OBJECT_NAME( object_id) , * FROM sys.dm_db_partition_stats ps  
   WHERE object_id = OBJECT_ID( 'dbo.SalesOrders_cold');  

-- insert more rows in 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  

-- verify contents of the tables  
SELECT * FROM dbo.SalesOrders;  
GO  

-- offload all sales orders to date to cold storage  
DECLARE @t datetime2 = SYSDATETIME();  
EXEC dbo.usp_SalesOrdersOffloadToCold @t;  

-- verify contents of the tables  
SELECT * FROM dbo.SalesOrders;  
GO  

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

참고 항목See Also

메모리 액세스에 최적화된 테이블Memory-Optimized Tables