In-Memory 최적화 테이블을 사용하여 임시 테이블 및 테이블 변수 성능 향상

In-Memory 최적화 테이블을 사용하여 임시 테이블 및 테이블 변수 성능 향상    

 

• Version : SQL Server 2014, 2016

 

SQL Server tempdb는 임시 테이블, 테이블 변수, 정렬, 중간값 저장 등에 사용되며 여러 병목 구간 중 하나 이다.

• Tempdb 경합 확인 및 해결 (Tempdb Contention) : http://sqlmvp.kr/140194260164

2014부터 도입된 In-Memory 기술은 OLTP 환경에서 메모리 기술을 사용하여 워크로드의 성능을 향상 시켰다. 임시 테이블 및 테이블 변수 또한 In-Memory 테이블을 사용하여 성능을 개선 할 수 있다.

 

기존의 전통적인 tempdb 기반의 테이블 유형은 아래와 같은 방법으로 생성한다.

CREATE TYPE dbo.test_disk AS TABLE (c1 INT NOT NULL, c2 CHAR(10));

 

메모리 최적화 테이블 유형을 만드는 방법은 매우 간단하다. 옵션에 'memory_optimized=on'을 추가하면 된다. 기존 유형에 인덱스가 존재하지 않는 경우 인덱스를 추가한다. (메모리 최적화 테이블은 하나 이상의 인덱스가 필요하다.)

CREATE TYPE dbo.test_memory AS TABLE (c1 INT NOT NULL INDEX ix_c1, c2 CHAR(10)) WITH (MEMORY_OPTIMIZED=ON);

 

인메모리 최적화 테이블 타입은 기존 테이블 형식, 프로시저 파라메터 정의, 테이블 변수 정의, T-SQL 모듈, ad-hoc 배치에서 사용할 수 있다.

만약 인라인 테이블 변수 선언이 있으면 어떻게 해야할까? 메모리 최적화 테이블 변수는 인라인으로 선언할 수 없기 때문에 선행 작업으로 테이블 타입을 생성해야 한다. 예를 들면 저장 프로시저 또는 ad-hoc에서 변수 선언을 할 수 있다.

DECLARE @tv TABLE ( c1 INT NOT NULL , c2 CHAR(10));

 

메모리 최적화 테이블 변수는 위와 같이 메모리 최적화 타입을 선언하고 테이블 변수 생성시 아래와 같이 테이블 타입변수를 선언한다.

DECLARE @tv dbo.test_memory;

 

기존의 전통적인 테이블 변수와 메모리 최적화 테이블 변수에 대한 성능 비교를 위해 아래와 같은 실험을 진행 하였다.

Test Disk	Test Memory
SET NOCOUNT ON GO DECLARE @tv dbo.test_disk INSERT @tv VALUES ( 1, 'n' ) INSERT @tv VALUES ( 2, 'm' ) DELETE FROM @tv GO 10000	DECLARE @tv dbo.test_memory INSERT @tv VALUES ( 1, 'n' ) INSERT @tv VALUES ( 2, 'm' ) DELETE FROM @tv GO 10000

 

내가 실습한 VM 환경은 Windows Hyper-v 환경의 SQL Server 2016이었으며 4Core, 8GB Memory 이다.

Test Disk의 경우 평균 15초의 실행 시간이 소모되었으며Test Memory 의 경우 평균 2초의 실행 시간이 소모되었다. (개인마다 편차가 있기 때문에 몇 배의 성능향상이 있다고 정의하지는 않겠다.) 임시 테이블 및 테이블 변수를 메모리 최적화 테이블로 우회함으로써 성능상 많은 이점을 얻을 수 있는것을 확인 할 수 있다.

 

SCHEMA_ONLY 메모리 최적화 테이블은 기존의 글로벌(##global_temp) 및 세션 레벨(#session_temp)의 임시 테이블을 대체하여 성능을 향상 시킬 수 있으며 둘 의 차이점은 아래와 같다.

• 메모리 최적화 테이블은 사용자 데이터베이스의 메모리 공간을 사용하며 기존의 글로벌 및 세션 임시테이블은 tempdb를 사용한다.
• 메모리 최적화 테이블은 사용자 테이블이며 사용자 데이터베이스의 일부로 유지된다. 마지막 세션이 끊어질때 세션 및 글로벌 임시 테이블의 스키마는 사리진다.
• 메모리 최적화 테이블은 배포시에 런타임이 없다.
• 메모리 최적화 테이블은 하나 이상의 인덱스가 필요하다.

 

[참고자료]

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/03/21/improving-temp-table-and-table-variable-performance-using-memory-optimization/

 

2016-08-24 / 강성욱 / http://sqlmvp.kr

 

 

SQL Server, SQL 2014, SQL 2016, In-Memory optimizer, 메모리 최적화 테이블, 임시 테이블, 테이블 변수, temp table