[MSSQL] 테이블을 인메모리(in-memory)로 사용하는 방법

인메모리 테이블

테이블의 데이터를 메모리로 올려, 모든 데이터 조작을 메모리상에서만 하도록 하는 방식입니다.

디스크를 거치지 않기 때문에 매우 빠른 속도로 진행되지만, 모든 데이터가 메모리상에 있어야 하기 때문에 메모리 사용률이 증가합니다.

 

메모리의 데이터는 서버가 재부팅되는 경우 소실됩니다.

이렇게 메모리에만 데이터가 있어 유실되는 부분을 방지하기 위해 File Stream 이라는 기능을 통해 디스크에 데이터를 영구보관 합니다.

( 단, 이렇게 데이터를 영구보관하기 위해서는 테이블에 Primary Key 가 무조건 있어야 합니다. )

 

 

인메모리 테이블 생성을 위한 선행조건

데이터베이스에 메모리 파일그룹 영역 생성

메모리에 최적화된 파일그룹을 생성해 줍니다.

이 파일그룹에 SCHEMA_AND_DATA 옵션을 가진 테이블의 메모리상 데이터가 저장됩니다.

 

참고로 디스크 파일그룹과는 다르게 메모리 파일그룹은 한개만 설정이 가능합니다.

(라고 알고있는데 잘 못 알고 있으면 말씀좀.. )

 

 

새로운 메모리전용 데이터베이스를 생성하는 방법

CREATE DATABASE [AdventureWorks]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'AdventureWorks_Primary', FILENAME = N'C:\DBData\AdventureWorks_Primary.mdf' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB ), 
 FILEGROUP [AdventureWorks_Memory] CONTAINS MEMORY_OPTIMIZED_DATA  DEFAULT
( NAME = N'AdventureWorks_Memory', FILENAME = N'C:\DBData\AdventureWorks_Memory.ndf' , MAXSIZE = UNLIMITED)
 LOG ON 
( NAME = N'AdventureWorks_Log', FILENAME = N'C:\DBLog\AdventureWorks_Log.ldf' , SIZE = 1024000KB , MAXSIZE = 2048GB , FILEGROWTH = 30720KB )
GO

 

기존에 존재하던 데이터베이스에 메모리영역을 추가하는 방법

ALTER DATABASE AdventureWorks ADD FILEGROUP Memory_optimized CONTAINS MEMORY_OPTIMIZED_DATA;
 
ALTER DATABASE AdventureWorks ADD FILE (
  NAME = N'Memory_optimized_file',
  FILENAME = N'C:\DBData\AdventureWorks_Memory.ndf'
 , MAXSIZE = UNLIMITED)
TO FILEGROUP Memory_optimized;

 

 

인메모리 테이블 생성 제약조건

제 기준에서 주요 제약사항만 작성하였기 때문에, 아래 URL 도 꼭 확인해주세요.

 

1. 일반적인 인덱스로는 NONCLUSTERED 만 생성할 수 있습니다.

2. COLUMNSTORE 인덱스만 CLUSTERED 인덱스를 생성할 수 있습니다.

3. PRIMARY KEY 는 업데이트가 불가능 합니다.

4. TRUNCATE 와 MERGE 구문은 사용할 수 없습니다.

5. CROSS-DATABASE 쿼리가 불가능 합니다. ( 하나의 쿼리에서 다른 데이터베이스와 함께 수행되는 방식 )

6. ALTER TABLE 로 디스크에서 메모리 테이블로 변환 불가능 합니다.

 

 

메모리 내 OLTP 에서 지원되지 않는 SQL Server 기능

docs.microsoft.com/ko-kr/sql/relational-databases/in-memory-oltp/unsupported-sql-server-features-for-in-memory-oltp?view=sql-server-ver15

 

메모리 내 OLTP 에서 지원되지 않는 T-SQL 구문

docs.microsoft.com/ko-kr/sql/relational-databases/in-memory-oltp/transact-sql-constructs-not-supported-by-in-memory-oltp?view=sql-server-ver15

 

 

인메모리 테이블 생성시 옵션

인메모리 테이블 생성시 아래와 같은 옵션을 지정합니다.

 

MEMORY_OPTIMIZED

옵션	설명
ON	ON 으로 설정한 경우 메모리 최적화 테이블로 설정됩니다.

 

DURABILITY 옵션

옵션	설명
SCHEMA_ONLY	서버 재부팅 시 테이블의 스키마만 유지됩니다. 테이블 내의 모든 데이터는 유실됩니다.
SCHEMA_AND_DATA	서버 재부팅 시 테이블의 스키마와 데이터가 유지됩니다. Primary Key 가 테이블에 존재해야 합니다.

 

인메모리 인덱스 생성시 옵션

저는 BUCKET COUNT 는 인덱스 중복행에 따라 관리를 지속적으로 해줘야 하기 때문에 거의 ASC | DESC 로 구성합니다.

인덱스 정렬 옵션

옵션	설명
ASC | DESC	일반적인 오름/내림 차순 정렬로 인덱스가 구성됩니다.
HASH BUCKET_COUNT	버킷 카운트만큼의 해시 인덱스로 구성됩니다.

 

HASH BUCKET 을 사용하고 싶은 경우는 아래 URL 을 참고해 주세요.

docs.microsoft.com/ko-kr/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver15

 

 

인메모리 테이블 생성 방법

MEMORY_OPTIMIZED 는 ON 으로 설정하여 메모리 테이블로 설정합니다.

DURABILITY 는 SCHEMA_AND_DATA 로 설정하여 데이터를 영구보관 하였습니다.

또한 SCHEMA_AND_DATA 옵션을 사용하기 위해 PRIMARY KEY 를 구성하였습니다.

COLUMNSTORE 기반의 인덱스가 아니기 때문에 CLUSTERED 인덱스를 사용할 수 없습니다.

CREATE TABLE [dbo].[Memory_Table]
(
    [C1] [varchar](30) COLLATE Korean_Wansung_CI_AS NOT NULL,
    [C2] [tinyint] NOT NULL,
    [C3] [varchar](255) COLLATE Korean_Wansung_CI_AS NOT NULL,
 CONSTRAINT [Memory_Table_PrimaryKey]  PRIMARY KEY NONCLUSTERED
(
    [C1] ASC,
    [C2] ASC
)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
GO