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

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

컬럼스토어 인덱스 ROW와 ROWGROUP 영향

 

  • Version : SQL Server 2012, 2014

 

SQL Server 메모리 내 Columnstore 인덱스는 열 기반 데이터 저장소 및 열 기반 쿼리 처리를 사용하여 데이터를 저장하고 관리한다. Columnstore 인덱스는 주로 대량 로드 및 읽기 전용 쿼리를 수행하는 데이터웨어 하우징 작업에 효과적이다.

 

columnstore index는 columnstore라는 칼럼 데이터 형식을 사용하여 데이터를 저장, 검색 및 관리하는 기술이다. SQL Server는 클러스터형 columnstore 인덱스와 비클러스터형 columnstore 인덱스를 모두 지원한다. 둘 다 동일한 메모리 내 columnstore 기술을 사용하지만 용도와 지원 기능에 차이가 있다. columnstore 인덱스는 전체 테이블 검색을 사용하는 쿼리에는 뛰어난 성능을 제공하지만 특정 값을 찾아 데이터를 검색하는 쿼리에는 부적합하다.

 

자세한 내용은 컬럼스토어에 대한 마이크로소프트 공식 문서를 참고 한다.

  • Cloumnstore 인덱스 소개 :

http://msdn.microsoft.com/ko-kr/library/gg492088(v=sql.120).aspx

 

이번 포스트는 컬럼스토어 인덱스에서 Row와 rowgoup에 대한 영향으로 SQL Server Storage Engine Blog를 읽고 이해한 내용을 정리한 것으로 번역의 오류나 기술적 오류가 있음을 미리 알려둔다. 자세한 내용은 원문을 참고 하길 바란다.

 

컬럼스토어 인덱스가 가지고 있는 로우에 대한 그룹을 RowGroup이라고 한다. RowGroup에는 두 가지 타입의 그룹이 있다.

  • Delta RowGroup : 기존의 행 스토리지 형식으로 데이터를 저장
  • Compressed RowGroup : 컬럼 스토리지 형식의 높은 행 압축 수준으로 저장

 

컬럼스토어에 대한 압축은 RowGroup 행의 수에 의존 한다. RowGroup은 Columnsotre 형식으로 동시에 압축되는 행 그룹이다. 성능과 압축률을 높이기 위해 columnstore 인덱스는 테이블을 여러 행 그룹으로 조각화한 후 각 행 그룹을 열 방식으로 압축 한다. 행 그룹의 행수는 압축률을 높일 만큼 크고 메모리 내 작업을 활용할 만큼 작아야 한다.

우리는 실험에서 RowGroup이 102,400+ 행에 대해 더 좋은 컬럼 압축을 달성 할 수 있음을 발견했다. 이를 바탕으로 컬럼스토어 인덱스 가이드 라인은 다음과 같다.

  • 100000 + 행을 로드할 때 압축된 rowgroup을 직접 로드 한다. (대규모 대량 로드 중에 대부분 행은 deltastore를 통과하지 않고 columnstore로 곧바로 이동한다.)
  • 일반 인서트는 Delta RowGroup에 행을 로드 한다. 행번호 1,048,576에 도달하면 RowGroup은 닫히고 튜플무브(tuple move)라는 백그라운드 스레드가 컬럼스토어 형식으로 변환하여 저장한다.
  • 나머지 행은 columnstore 또는 deltastore에 추가된다. 행수가 행 그룹당 최대행보다 적으면 deltastore에 추가 된다.

 

RowGroup 크기에 영향을 미치는 요인은 다음과 같다.

  • 병럴처리 정도(DOP)
  • 사전의 크기
  • 메모리

인덱스 만들기는 메모리가 제한되지 않는한 기본적으로 병렬작업이다. 병렬 작업은 많은 메모리를 필요로 하다. 메모리가 충분하면 동일한 열에 B-tree를 작성할 때 보다 1.5배 많은 메모리가 columnstore 인덱스를 만드는데 사용된다. 충분한 메모리가 없는 경우 메모리에 맞게 자동으로 MAXDOP을 줄인다.

 

예제와 함께 각각 요인을 살펴보자. 다음 스크립트는 데이터를 생성하고 컬럼스토어 인덱스를 생성한다. (매우 오랜시간 동안 스크립트가 실행 된다.)

  • 클러스터형 컬럼스토어 인덱스 : SQL Server 2014 지원
  • 비클러스터형 컬럼스토어 인덱스 : SQL Server 2012 ~ 2014 지원

CREATE TABLE dbo.t_colstore (

c1 int NOT NULL,

c2 INT NOT NULL,

c3 char(40) NOT NULL,

c4 char(1000) NOT NULL

)

go

 

set nocount on

go

 

-- load 2000000 rows

declare @outerloop int = 0

declare @i int = 0

 

while (@outerloop < 2000000)

begin

    Select @i = 0

    

    while (@i < 2000)

    begin

        insert t_colstore values (@i + @outerloop, @i + @outerloop, 'a',

        concat (CONVERT(varchar, @i + @outerloop), (replicate ('b', 950))))

        set @i += 1;

    end

 

set @outerloop = @outerloop + @i

 

set @i = 0

end

go

 

CREATE CLUSTERED COLUMNSTORE INDEX t_colstore_cci ON t_colstore with (maxdop = 1)

 

생성된 컬럼스토어의 RowGroup과 각 그룹에 포함된 행을 살펴보자. 필자가 테스트한 결과로는 39개의 그룹과 각 그룹당 45468의 행이 포함되어 있다.

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

 

 

위에 설명에서는 행번호 1,048,576에 도달해야만 세그먼트 그룹이 닫히고 새로운 세그먼트 그룹으로 저장된다고 하였는데 왜 이런 현상이 발생한 걸까? 이는 사전의 크기(16MB)가 가득차게 되면 자동으로 RowGroup의 크기를 줄인다. 즉 메모리가 부족한 경우 로우 카운트가 1048576에 도달하지 않더라도 현재의 세그먼트를 닫는다.

 

 

[참고자료]

http://msdn.microsoft.com/ko-kr/library/gg492088(v=sql.120).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 1366
1989 sys.dm_tran_locks 를 이용한 잠금 정보 확인 jevida(강성욱) 2017.01.11 1525
1988 외래키 제약 조건 삭제 후 재작성 스크립트 생성하기 jevida(강성욱) 2017.01.11 1780
1987 페이지 ID로 테이블 이름 찾기 jevida(강성욱) 2017.01.11 1434
1986 DBCC CHECKPRIMARYFILE 사용법 jevida(강성욱) 2017.01.11 1326
1985 컬럼스토어 인덱스 대용량 데이터 로드 jevida(강성욱) 2017.01.11 1743
1984 컬럼스토어 인덱스 INSERT 작업과 동시성 jevida(강성욱) 2017.01.11 1491
1983 컬럼스토어 인덱스 동시성 jevida(강성욱) 2017.01.11 1780
» 컬럼스토어 인덱스 ROW와 ROWGROUP 영향 jevida(강성욱) 2016.11.23 2943
1981 테이블 변수와 TF 2453 jevida(강성욱) 2016.11.23 3184
1980 Sp_trace_create MaxfileSize 오류 jevida(강성욱) 2016.11.23 2393
1979 RANDBETWEEN 함수 만들기 jevida(강성욱) 2016.11.23 4589
1978 Optimize for hint 쿼리 최적화 jevida(강성욱) 2016.11.23 3509
1977 TempDB 파일 사이즈 증가 시 경고 받기 jevida(강성욱) 2016.11.23 3044
1976 블록킹 세션을 찾아 우선순위 낮은 세션 종료하기 jevida(강성욱) 2016.11.23 3245
1975 다양한 포맷의 이름 파싱 하기 jevida(강성욱) 2016.11.23 2662
1974 비결정적 사용자 정의 함수 사용으로 인한 느린 쿼리 jevida(강성욱) 2016.11.23 3148
1973 스냅숏 격리 수준(SNAPSHOT ISOLATION LEVEL) jevida(강성욱) 2016.11.23 4265
1972 SQL Server 파라메터 스니핑의 다양한 접근 jevida(강성욱) 2016.11.23 3125
1971 잘못된 개체를 참조하는 SQL Server 개체 찾기 jevida(강성욱) 2016.11.23 1940





XE Login