메모리 최적화 테이블 변수 및 예상 행수
· 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)을 사용하여 범위를 다시 조정
[참고자료]
