메모리 최적화 테이블 변수 및 예상 행수

메모리 최적화 테이블 변수 및 예상 행수

 

·         Version : SQL Server 2014, 2016

 

이번 포스팅에서 메모리 최적화 테이블은 배치 작업이 완료될 때 까지 메모리를 소비한다고 하였다.

·         메모리 최적화 테이블변수와 701 오류 (loop 사용으로 인한 메모리 부족 오류) : http://sqlmvp.kr/220996905075

 

이번 포트스트는 메모리 최적화 테이블에서 예상 행수에 대해서 알아본다. 기본적으로 메모리 최적화 테이블 변수는 디스크 기반 테이블 변수와 동일한 방식으로 작동한다. 아래 스크립트를 실행하면 예상 행수가 1로 나타나는것을 확인할 수 있다.

 

실습용 데이터 베이스 및 기본 데이터 생성

create database IMOLTP go ALTER DATABASE imoltp ADD FILEGROUP imoltp_mod CONTAINS MEMORY_OPTIMIZED_DATA  go ALTER DATABASE imoltp ADD FILE (name='imoltp_mod1', filename='c:\_sql_data\imoltp_mod2') TO FILEGROUP imoltp_mod  go   use IMOLTP go     CREATE TYPE dbo.test_memory AS TABLE (c1 INT NOT NULL INDEX ix_c1, c2 CHAR(10)) WITH (MEMORY_OPTIMIZED=ON); go

 

예상행수 1행 반환

DECLARE @tv dbo.test_memory set nocount on   declare @i int set @i = 1 while @i < 10000 begin     insert into @tv values (@i, 'n')     set @i = @i + 1 end   set statistics xml on --this will work and the etimate will be correct select * from @tv t1 join @tv t2 on t1.c1=t2.c1 --option (recompile, querytraceon 2453) set statistics xml off go

 

예상행으로 1행이 있다. 디스크 기반 테이블 변수에서는 명령문 단위에서  recompile 옵션을 사용하여 예상치를 제어하거나 추적 플래그 2453을 사용할 수 있다.

임시 쿼리 또는 일반 TSQL 저장 프로시저에서 메모리 최적화 테이블 변수를 사용하는 경우는 두 가지 방식을 사용하여 동일하게 동작을 제어할 수 있다. 아래  예제는  recompile 옵션을 사용하여 올바르게 표시된다.

DECLARE @tv dbo.test_memory set nocount on   declare @i int set @i = 1 while @i < 10000 begin     insert into @tv values (@i, 'n')     set @i = @i + 1 end   set statistics xml on --this will work and the etimate will be correct select * from @tv t1 join @tv t2 on t1.c1=t2.c1 option (recompile, querytraceon 2453) set statistics xml off go

 

 

그러나 네이티브 컴파일된 저장프로시저 내에서 사용할 때 문제가 발생한다. 이 경우 항상 1행으로 계산된다. 네이티브 컴파일 프로시저는 명령문 레벨 recompile을 허용하지 않으므로 변경할 수 없다. 기본적으로 컴파일된 프로시저를 만들려고 하면 프로시저 생성 오류가 발생한다.

 

create procedure test with native_compilation, schemabinding  as   begin atomic with  (transaction isolation level = snapshot,  language = N'English')   DECLARE @tv dbo.test_memory declare @i int set @i = 1 while @i < 10000 begin     insert into @tv values (@i, 'n')     set @i = @i + 1 end --you can’t add TF 3453 or recompile select t1.c1, t2.c1 from @tv t1 join @tv t2 on t1.c1=t2.c1 option (recompile, querytraceon 2453) end  go

 

Msg 10794, Level 16, State 45, Procedure test, Line 17 [Batch Start Line 0] The query hint 'RECOMPILE' is not supported with natively compiled modules. Msg 10794, Level 16, State 45, Procedure test, Line 17 [Batch Start Line 0] The query hint 'QUERYTRACEON' is not supported with natively compiled modules.

 

 

이 문제를 해결하기 위한 방법은 없을까? 기본적으로 컴파일된 프로시저의 경우 아래와 같은 몇 가지 조언이 있다.

1.       메모리 최적화 테이블 변수에 입력된 행 수를 제한

2.       많은 행을 가진 메모리 최적화 테이블 변수를 조인하는 schema_only 메모리 최적화 테이블 사용을 고려

3.       데이터 특성을 잘알고 있는 경우 조인 옵션(force order)을 사용하여 범위를 다시 조정

 

[참고자료]

https://blogs.msdn.microsoft.com/psssql/2017/05/10/memory-optimized-table-variable-and-cardinality-estimate/

 

 

2017-05-17 / 강성욱 / http://sqlmvp.kr

 

SQL Server 2014, SQL Server 2016, Memory optimize table, SQL, In-Memory table, 메모리 최적화 테이블, 메모리 테이블 변수, 쿼리 튜닝, 예상 실행 계획,