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
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 14645
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 8958
2074 SQL Server Install on Linux (Ubuntu – 16.0.4) jevida(강성욱) 2017.01.11 5105
2073 SQL Azure blob storage 백업을 위한 프록시 세팅 jevida(강성욱) 2017.01.11 4287
2072 SQL on Azure 환경에서 SQL 서버 연결 불능 문제(VNET 설정 문제) jevida(강성욱) 2017.01.11 3368
2071 SQL Server 2016 자동 통계 업데이트 임계값 변경 jevida(강성욱) 2017.01.11 4795
2070 SQL Server 2016 Multiple Log Writer Workers jevida(강성욱) 2017.01.11 3876
2069 SQL Server 2016 Larger Data File Writes jevida(강성욱) 2017.01.11 3244
2068 SQL Server 2016 향상된 업데이트 스케줄링 알고리즘 jevida(강성욱) 2017.01.11 3699
2067 SQL Server 2016 Automatic Soft NUMA jevida(강성욱) 2017.01.11 3173
2066 SQL Server 2016 Tempdb 환경 설정 jevida(강성욱) 2017.01.11 4480
2065 SQL Server 2016 향상된 즉시 파일 초기화 jevida(강성욱) 2017.01.11 2830
2064 SQL Server 2016 LDF 생성 또는 증가 시 변경된 스탬프 패턴 jevida(강성욱) 2017.01.11 2683
2063 SQL Server 2016 Tempdb 성능 향상 jevida(강성욱) 2017.01.11 4868
2062 SQL Server 2016 DBCC CHECK 작업 성능 향상 jevida(강성욱) 2017.01.11 2424
2061 Microsoft Azure - SQL Server가 포함된 가상 컴퓨터 생성하기 jevida(강성욱) 2017.01.11 1879
2060 데이터베이스에서 사용자 삭제 오류 jevida(강성욱) 2017.01.11 1348
2059 Sys.dm_exec_query_plan 에서 query_plan 컬럼의 NULL 값 반환 jevida(강성욱) 2017.01.11 1933
2058 SSMS에서 유효하지 않은 소유자로 데이터베이스 정보가 보이지 않는 증상 jevida(강성욱) 2017.01.11 1953
2057 SQLCMD 유틸리티 사용하기 jevida(강성욱) 2017.01.11 8288
» In-Memory 최적화 테이블을 사용하여 임시 테이블 및 테이블 변수 성능 향상 jevida(강성욱) 2017.01.11 5300
2055 SQL Server Job Agent는 몇 개까지 실행이 가능할까? jevida(강성욱) 2017.01.11 1809





XE Login