데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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

 

  • Version : SQL Server 2014, 2016

 

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

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/

 




강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
2070 SQL Server 2016 Multiple Log Writer Workers jevida(강성욱) 2017.01.11 3805
2069 SQL Server 2016 Larger Data File Writes jevida(강성욱) 2017.01.11 3188
2068 SQL Server 2016 향상된 업데이트 스케줄링 알고리즘 jevida(강성욱) 2017.01.11 3651
2067 SQL Server 2016 Automatic Soft NUMA jevida(강성욱) 2017.01.11 3101
2066 SQL Server 2016 Tempdb 환경 설정 jevida(강성욱) 2017.01.11 4392
2065 SQL Server 2016 향상된 즉시 파일 초기화 jevida(강성욱) 2017.01.11 2756
2064 SQL Server 2016 LDF 생성 또는 증가 시 변경된 스탬프 패턴 jevida(강성욱) 2017.01.11 2635
2063 SQL Server 2016 Tempdb 성능 향상 jevida(강성욱) 2017.01.11 4716
2062 SQL Server 2016 DBCC CHECK 작업 성능 향상 jevida(강성욱) 2017.01.11 2376
2061 Microsoft Azure - SQL Server가 포함된 가상 컴퓨터 생성하기 jevida(강성욱) 2017.01.11 1840
2060 데이터베이스에서 사용자 삭제 오류 jevida(강성욱) 2017.01.11 1303
2059 Sys.dm_exec_query_plan 에서 query_plan 컬럼의 NULL 값 반환 jevida(강성욱) 2017.01.11 1875
2058 SSMS에서 유효하지 않은 소유자로 데이터베이스 정보가 보이지 않는 증상 jevida(강성욱) 2017.01.11 1845
2057 SQLCMD 유틸리티 사용하기 jevida(강성욱) 2017.01.11 7889
» In-Memory 최적화 테이블을 사용하여 임시 테이블 및 테이블 변수 성능 향상 jevida(강성욱) 2017.01.11 4531
2055 SQL Server Job Agent는 몇 개까지 실행이 가능할까? jevida(강성욱) 2017.01.11 1757
2054 확장이벤트 사용시 주의사항 jevida(강성욱) 2017.01.11 1631
2053 Unix(Linux) timestamp(bigint) 형식을 datetime 으로 변경하기 jevida(강성욱) 2017.01.11 2969
2052 Query Rule Off를 사용한 SQL Server 옵티마이저 비활성화 jevida(강성욱) 2017.01.11 1571
2051 시스템 관리자 권한이 없는 특정 프로그램에서 Trace Flag 사용하기 jevida(강성욱) 2017.01.11 2325





XE Login