In-Memory OLTP는 메모리 최적화 테이블에 제한된 양의 활성 데이터가 유지되는 패턴을 지원하지만 액세스 빈도가 낮은 데이터는 디스크에서 처리됩니다. 일반적으로 이 시나리오는 키를 기반으로 데이터가 저장되는 시나리오입니다 datetime .
메모리 최적화 테이블과 공통 스키마를 가진 분할된 테이블을 유지 관리함으로써 메모리 최적화 테이블로 분할된 테이블을 에뮬레이트할 수 있습니다. 현재 데이터는 메모리 최적화 테이블에 삽입되고 업데이트되는 반면, 자주 액세스하지 않은 데이터는 기존의 분할된 테이블에서 유지 관리됩니다.
활성 데이터가 메모리 최적화 테이블에 있다는 것을 알고 있는 애플리케이션은 고유하게 컴파일된 저장 프로시저를 사용하여 데이터에 액세스할 수 있습니다. 전체 데이터 범위에 액세스해야 하거나 관련 데이터를 보유하는 테이블을 모르는 작업은 해석된 Transact-SQL 사용하여 메모리 최적화 테이블을 분할된 테이블과 조인합니다.
이 파티션 스위치는 다음과 같이 설명됩니다.
마감 날짜를 사용하여 In-Memory OLTP 테이블의 데이터를 준비 테이블에 삽입합니다.
메모리 최적화 테이블에서 동일한 데이터를 삭제합니다.
준비 테이블에서 교환합니다.
활성 파티션을 추가합니다.
활성 데이터 유지 관리
ActiveOrders 삭제로 시작하는 작업은 데이터를 삭제하고 준비 테이블에서 전환하는 동안 데이터가 누락되는 쿼리를 방지하기 위해 유지 관리 기간 동안 수행해야 합니다.
관련 샘플은 Application-Level 분할을 참조하세요.
코드 샘플
다음 샘플에서는 분할된 디스크 기반 테이블과 함께 메모리 최적화 테이블을 사용하는 방법을 보여줍니다. 자주 사용되는 데이터는 메모리에 저장됩니다. 데이터를 디스크에 저장하려면 새 파티션을 만들고 분할된 테이블에 데이터를 복사합니다.
이 샘플의 첫 번째 부분에서는 데이터베이스와 필요한 개체를 만듭니다. 샘플의 두 번째 부분에서는 메모리 최적화 테이블에서 분할된 테이블로 데이터를 이동하는 방법을 보여줍니다.
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;