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

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

컬럼스토어 인덱스 대용량 데이터 로드

Column Store Index Bulk Load Data

 

  • Version : SQL Server 2012, 2014

 

클러스터 컬럼 스토어 인덱스 대용량 데이터 로드

이전 포스팅에서 컬럼스토어 인덱스에 대한 동시성 및 인서트 동시성에 대해서 살펴 보았다.

 

이번 포스트는 대용량 데이터 로드(Bulk load)를 통해 데이터가 삽입될 때 잠금 동작에 대해서 설명한다. 테스트용 기본 테이블을 생성하고 더미 데이터를 생성한다.

Create table t_bulkload (

accountkey int not null,

accountdescription nvarchar (50),

accounttype nvarchar(50),

AccountCodeAlternatekey int

)

go

 

-- Let us prepare the data

-- insert 110K rows into a regular table

 

begin tran

    declare @i int = 0

 

    while (@i < 110000)

    begin

        insert into t_bulkload values (@i, 'description', 'dummy-accounttype', @i*2)

        set @i = @i + 1

    end

 

commit

 

커맨드 창에서 BCP명령을 사용하여 데이터를 파일로 생성한다.

bcp adventureworksDW2012..t_bulkload out c:\t_bulkoad.dat -c -T

 

 

테스트를 위해 만든 테이블을 truncate 하고 클러스터 컬럼스토어 인덱스를 생성한다. 테이블에 행이 없기 때문에 ROWGROUP은 없다.

--truncate the table

Truncate table t_bulkload

 

-- convert row clustered index into clustered columnstore index

CREATE CLUSTERED COLUMNSTORE index t_bulkload_cci on t_bulkload

 

이제 배치 크기를 103000 크기로 하고 대량 데이터를 로드 한다. 그리고 컬럼스토어 RowGroup 정보를 확인한다.

세션 1

세션 2

-- now bulkload the data

begin tran

bulk insert t_bulkload

FROM 'c:\t_bulkoad.dat'

WITH

(

BATCHSIZE = 103000

)

 

 
 

select * from sys.column_store_row_groups where object_id = object_id('t_bulkload')

 

 

위 그림은 두 개의 행 그룹이 있는 것을 보여준다. 첫 번째 행 row_group_id = 0 그룹은 103000행을 압축한다. 이는 BATCHSIZE >= 102400 일때 SQL Server가 직접 행 그룹을 압축하기 때문이다. 직접 행 압축은 행 델타 그룹을 사용하지 않기 때문에 SQL Server 로깅을 최소화 할 수 있다. 그리고 튜플 데이터를 이동 할 필요가 없다.

 

나머지 7천 데이터 파일은 행 델타 그룹 삽입(데이터파일은 110000 행이다)에서 열이 부족하기 때문에 행 그룹이 계속 닫혀 있지 않음을 의미하는 OPEN 상태로 표시되고 있다. 수 백만 행이 표시 되었을 때 백그라운드의 튜플 이동기에 의해 닫히고 압축을 할 것이다.

 

잠금 상태를 살펴 보자. 델타 행 그룹 및 압축 행 그룹 모두에서 X 잠금을 확인 할 수 있다. 행 그룹 레벨에서 잠금 오버헤드를 최소화 한다.

select

request_session_id as spid,

resource_type as rt,

resource_database_id as rdb,

(case resource_type

        WHEN 'OBJECT' then object_name(resource_associated_entity_id)

        WHEN 'DATABASE' then ' '

        ELSE (select object_name(object_id)

                from sys.partitions

                where hobt_id=resource_associated_entity_id)

END) as objname,

resource_description as rd,

request_mode as rm,

request_status as rs

from sys.dm_tran_locks

 

 

 

현재 대용량 데이터 로드가 실행된 상태에서 다른 세션에서 행을 삽입할 경우 어떤 현상이 발생 하는지 살펴보자.

세션 3

세션 4

begin tran

    insert into t_bulkload values (-1, 'single row', 'single row', -1)

 
 

select

request_session_id as spid,

resource_type as rt,

resource_database_id as rdb,

(case resource_type

        WHEN 'OBJECT' then object_name(resource_associated_entity_id)

        WHEN 'DATABASE' then ' '

        ELSE (select object_name(object_id)

                from sys.partitions

                where hobt_id=resource_associated_entity_id)

END) as objname,

resource_description as rd,

request_mode as rm,

request_status as rs

from sys.dm_tran_locks

 

 

한 행을 입력하였지만 현재 행 그룹 row_group_id = 1에 X 잠금을 보유하고 있기 때문에 다음과 같은 새로운 델타 행 그룹에 삽입된 것을 확인 할 수 있다.

 

 

[참고자료]

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2014/07/28/clustered-column-store-index-bulk-loading-the-data.aspx

 

 



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

No. Subject Author Date Views
1990 클러스터된 SQL 서버 인스턴스에 대한 호스트 이름 확인 jevida(강성욱) 2017.01.11 1368
1989 sys.dm_tran_locks 를 이용한 잠금 정보 확인 jevida(강성욱) 2017.01.11 1531
1988 외래키 제약 조건 삭제 후 재작성 스크립트 생성하기 jevida(강성욱) 2017.01.11 1781
1987 페이지 ID로 테이블 이름 찾기 jevida(강성욱) 2017.01.11 1434
1986 DBCC CHECKPRIMARYFILE 사용법 jevida(강성욱) 2017.01.11 1331
» 컬럼스토어 인덱스 대용량 데이터 로드 jevida(강성욱) 2017.01.11 1744
1984 컬럼스토어 인덱스 INSERT 작업과 동시성 jevida(강성욱) 2017.01.11 1493
1983 컬럼스토어 인덱스 동시성 jevida(강성욱) 2017.01.11 1782
1982 컬럼스토어 인덱스 ROW와 ROWGROUP 영향 jevida(강성욱) 2016.11.23 2948
1981 테이블 변수와 TF 2453 jevida(강성욱) 2016.11.23 3186
1980 Sp_trace_create MaxfileSize 오류 jevida(강성욱) 2016.11.23 2397
1979 RANDBETWEEN 함수 만들기 jevida(강성욱) 2016.11.23 4631
1978 Optimize for hint 쿼리 최적화 jevida(강성욱) 2016.11.23 3512
1977 TempDB 파일 사이즈 증가 시 경고 받기 jevida(강성욱) 2016.11.23 3047
1976 블록킹 세션을 찾아 우선순위 낮은 세션 종료하기 jevida(강성욱) 2016.11.23 3250
1975 다양한 포맷의 이름 파싱 하기 jevida(강성욱) 2016.11.23 2665
1974 비결정적 사용자 정의 함수 사용으로 인한 느린 쿼리 jevida(강성욱) 2016.11.23 3151
1973 스냅숏 격리 수준(SNAPSHOT ISOLATION LEVEL) jevida(강성욱) 2016.11.23 4336
1972 SQL Server 파라메터 스니핑의 다양한 접근 jevida(강성욱) 2016.11.23 3175
1971 잘못된 개체를 참조하는 SQL Server 개체 찾기 jevida(강성욱) 2016.11.23 1948





XE Login