SQL Server, In-Memory OLTP

In-Memory OLTP가 빠른 이유
기본 사용 예제 - 1
기본 사용 예제 - 2
인덱스
여전히 지원 안 하는 구문(2016)
Creating Natively Compiled Stored Procedures
Reference

빅데이터, IoT, 인공지능과 같은 용어를 요즘은 주변에서 쉽게 볼 수 있다. 데이터베이스 분야도 전통적인 RDBMS뿐만 아니라 NoSQL, 메모리 데이터베이스, 시계열 데이터베이스 또한 관심 분야로 떠오르고 있다. 이제는 단순한 개념적 학습이 아닌 실무에서 방대한 데이터 처리를 실시간으로 처리해야 하는 상황에 직면한 것이다. 참고로 국산 시계열 데이터베이스인 마크베이스(MACHBASE)를 살펴볼 필요가 있는데 초당 104만 건 정도의 데이터를 처리할 수 있다고 한다.

In-Memory OLTP는 SQL Server에서 트랜잭션 처리, 데이터 로드 및 일시적인 데이터 시나리오의 성능 최적화하기 위해 사용할 수 있는 뛰어난 기술이다(In-Memory OLTP is the premier technology available in SQL Server and SQL Database for optimizing performance of transaction processing, data ingestion, data load, and transient data scenarios). 전통 방식인 디스크 테이블보다 실제로 5~30배의 성능 향상이 있다고 한다.

High-throughput and low-latency transaction processing
Data ingestion, including IoT (Internet-of-Things)
Caching and session state
Tempdb object replacement
ETL (Extract Transform Load)

아래의 글은 Channel9에서 동영상 강좌로 소개한 SQL Server 2016 In-Memory OLTP를 정리한 것이다. 여기에 필요한 몇 가지 관련 내용을 추가하였다.

In-Memory OLTP가 빠른 이유

Lock-Free, Latch-Free 구조
Native compilation
메모리 테이블 : 행이 인덱스로 연결된 구조(Hash index on Name)

기본 사용 예제 - 1

-- 1. 테스트 DB 생성 : 기존 형태로 생성
create database InMemoryDB ...

-- 2. 파일그룹 만들기
alter database InMemoryDB
add filegroup [InMemoryDB_fg] contains memory_optimized_data

-- 3. 파일(폴더형태) 생성
alter database InMemoryDB
add file(name = InMemoryDB_dir, filename='C:\Data\InMemoryDB_dir')
to filegroup InMemoryDB_fg

-- 4. 테스트 테이블 생성(with 구문 추가)
use InMemoryDB
go
create table t1 (
col1 int not null primary key nonclustered hash with(bucket_count = 1000000) identity,
col2 nvarchar(100) collate korean_wansung_bin2 not null,
col3 nvarchar(100) null,
col4 int null)
with (memory_optimized = on, durability = schema_and_data)
-- 참고: schema_only :  재시작 후 데이터는 사라짐, ldf에 로깅을 하지 않으므로 성능 up

-- 5. 데이터 입력
insert into t1(col2, col3, col4) value('aaa', 'bbb', 1)

기본 사용 예제 - 2

-- Create demo database
CREATE DATABASE SQL2016_Demo
 ON  PRIMARY
( 
    NAME = N'SQL2016_Demo', 
    FILENAME = N'C:\Dump\SQL2016_Demo.mdf', 
    SIZE = 5120KB, 
    FILEGROWTH = 1024KB 
 )
 LOG ON 
 ( 
    NAME = N'SQL2016_Demo_log', 
    FILENAME = N'C:\Dump\SQL2016_Demo_log.ldf', 
    SIZE = 1024KB, 
    FILEGROWTH = 10%
 )
GO

use SQL2016_Demo
go

-- Add Filegroup by MEMORY_OPTIMIZED_DATA type 
ALTER DATABASE SQL2016_Demo 
    ADD FILEGROUP MemFG CONTAINS MEMORY_OPTIMIZED_DATA 
GO

--Add a file to defined filegroup
ALTER DATABASE SQL2016_Demo ADD FILE
    ( 
        NAME = MemFG_File1,
        FILENAME = N'C:\Dump\MemFG_File1' -- your file path, check directory exist before executing this code
    ) 
TO FILEGROUP MemFG
GO

--Object Explorer -- check database created
GO

-- create memory optimized table 1
CREATE TABLE dbo.MemOptTable1  
(  
    Column1     INT         NOT NULL,  
    Column2     NVARCHAR(4000)  NULL,  
    SpidFilter  SMALLINT    NOT NULL   DEFAULT (@@spid),  

    INDEX ix_SpidFiler NONCLUSTERED (SpidFilter),  
    INDEX ix_SpidFilter HASH (SpidFilter) WITH (BUCKET_COUNT = 64),  
      
    CONSTRAINT CHK_soSessionC_SpidFilter  
        CHECK ( SpidFilter = @@spid ),  
)  
    WITH  
        (MEMORY_OPTIMIZED = ON,  
         DURABILITY = SCHEMA_AND_DATA);  --or DURABILITY = SCHEMA_ONLY
go  

-- create memory optimized table 2
CREATE TABLE MemOptTable2
(
    ID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 10000),
    FullName NVARCHAR(200) NOT NULL, 
    DateAdded DATETIME NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

인덱스

hash, range, columnstore index
hash : point 쿼리에 효과적, bucket_count 이슈(range nonclustered사용)
range : 범위 검색에 효과적
columnstore index 지원 : Overview 문서

여전히 지원 안 하는 구문(2016)

select * : 컬럼명을 명시해야 함
case : 고유하게 컴파일된 저장 프로시저에서 CASE 식 구현
next value for : 시퀀스 미지원, 다른 채번 방법으로 사용

Creating Natively Compiled Stored Procedures

CREATE TABLE [dbo].[T2] (  
  [c1] [int] NOT NULL, 
  [c2] [datetime] NOT NULL,
  [c3] nvarchar(5) NOT NULL, 
  CONSTRAINT [PK_T1] PRIMARY KEY NONCLUSTERED ([c1])  
  ) WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )  
GO  
  
CREATE PROCEDURE [dbo].[usp_2] (@c1 int, @c3 nvarchar(5)) 
WITH NATIVE_COMPILATION, SCHEMABINDING  
AS BEGIN ATOMIC WITH  
(  
 TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english'  
)  
  DECLARE @c2 datetime = GETDATE();  
  INSERT INTO [dbo].[T2] (c1, c2, c3) values (@c1, @c2, @c3);  
END  
GO