bulk data insert, Columnstore Index, ms sql, page latch contention, SQL Server, 대용량 데이터 로드, 래치 경합, 인덱스 경합, 컬럼스토어 인덱스

클러스터 컬럼스토어 인덱스(Clusterd Columnstore Index)에서 대량 인서트 작업시 발생하는 래치 경합 최소화 트릭

 

·         Version : SQL Server

 

Clustered Columnstore Index(CCI)에서 데이터를 로드 하는 일반적인 시나리오는 오버헤드가 적은 시간 (일반적으로 야간 시간)을 활용해서 수백만개의 행이 포함된 데이터 파일을 로드하는 것이다. 권장 되는 배치 사이즈는 batchsize >= 102400으로 데이터를 로드 하는것이다. 배치 사이즈에 대한 내용은 아래 링크를 참고 한다.

·         컬럼스토어 인덱스 대용량 데이터 로드 : http://sqlmvp.kr/220135847446

 

CCI는 동시 데이터 스트림을 동일한 델타 행 그룹으로 허용한다. 그러나 동시성을 높이면 더 많은 페이지 래치 경합이 발생한다. 각 델타 RG는 내부적으로 클러스터된 b-tree 인덱스로 구성되며 데이터로드는 단순하게 증가하는 클러스터된 인덱스 키 패턴을 따라 마지막 페이지에서 래치 경합이 발생한다.

·         컬럼스토어 인덱스 INSERT 작업과 동시성 : http://sqlmvp.kr/220132145097

·         컬럼스토어 인덱스 동시성 : http://sqlmvp.kr/220130069090

 

아래 스크립트는 CCI에 삽입된 숨겨진 키 값을 테스트하는 간단한 방법이다.

create table foo (c1 int)

create clustered columnstore index foocci on foo

go

 

insert into foo values (1)

insert into foo values (2)

 

-- check the hidden clustering key index

select %%physloc%% , * from foo

 

 

위 값은 <rowgroup-id>, <tuple-id>를 나타내는 클리스터된 컬럼스토어 인덱스 행의 숨겨진 키 열을 나타낸다. <tuple-id>는 내부적으로 단순하게 증가하는 숫자로 생성되어 동시 삽입시 페이지 래치 경합을 초래한다.

 

이러한 경합 문제를 해결하기 위한 트릭으로Memory optimized 테이블을 사용할 수 있다.  Memory optimized 테이블을 사용하면PAGE 구조 없이 메모리에 데이터를 유지하도록 구현되므로 마지막 페이지 경합이 제거된다.

 

이 트릭을 사용하여 Memory optimized 테이블은102400 행보다 큰 청크로 마이그레이션 하여  델타행 그룹을 사용하지 않을 수 있다.

 

 

[참고자료]

https://blogs.msdn.microsoft.com/sql_server_team/clustered-columnstore-index-massively-parallel-trickle-insert/

 

 

2018-06-11 / Sungwook Kang / http://sqlmvp.kr / http://sqlangeles.com

 

SQL Server, MS SQL, Columnstore Index, 컬럼스토어 인덱스, 인덱스 경합, 래치 경합, page latch contention, 대용량 데이터 로드, bulk data insert

저작자표시 비영리 변경금지

'SQL Server > SQL Server Tip' 카테고리의 다른 글

SQL Server 네이티브 컴파일된 저장 프로시저 성능 모니터링  (0) 2019.03.25
SQL Server 2017 소규모 시스템에서 향상된 리소스 사용  (0) 2019.03.25
SQL Server 2016 향상된 복제 기능 – 배포 데이터베이스 클린업 향상  (0) 2019.03.25
SQL Server 2017향상된 복제 기능 – 배포 데이터베이스의 AG 지원  (0) 2019.03.25
SQL Server 2017 향상된 복제 기능 - 복제에이전트 프로필 매개변수의 동적 새로 고침  (0) 2019.03.25
Columnstore Index, MSSQL, SQL Server, 데이터베이스, 열 저장소, 컬럼 기반 인덱스, 컬럼스토어 인덱스, 컬럼스토어 인덱스 성능

컬럼스토어 인덱스 성능 (Columnsotre Index Performance)

 

  • Version : SQL Server 2012, 2014, 2016

 

SQL Server 2012부터 도입된 컬럼스토어 인덱스는 열 기반 데이터 저장소 및 열 기반 쿼리 처리를 사용하여 데이터를 저장하고 관리한다. 인덱스는 주로 대량 로드 및 전용 쿼리를 수행하는 데이터웨어하우징 작업에 효과적이다. 전체 테이블 검색을 사용하는 쿼리에서는 뛰어난 성능을 제공하지만 특정 값을 찾아 데이터를 검색하는 쿼리에는 부적합 하다.

 

자세한 내용은 MSDN을 참고한다.

 

일반 B-tree 인덱스와 컬럼스토어 인덱스의 성능에 대해서 비교해본다. 아래 스크립트는 테스트를 위한 테이블 생성 및 데이터 생성 스크립트이다. (꽤 오랜 시간동안 테스트 데이터가 생성된다.)

-- Create SampleData table

CREATE TABLE [dbo].[SampleData](

    [RowKey] [int] NOT NULL,

    [CreateDate] [int] NOT NULL,

    [OtherDate] [int] NOT NULL,

    [VarcharColumn1] [varchar](20) NULL,

    [VarcharColumn2] [varchar](20) NULL,

    [VarcharColumn3] [varchar](20) NULL,

    [VarcharColumn4] [varchar](20) NULL,

    [VarcharColumn5] [varchar](20) NULL,

    [IntColumn1] int NULL,

    [IntColumn2] int NULL,

    [IntColumn3] int NULL,

    [IntColumn4] int NULL,

    [IntColumn5] int NULL,

    [IntColumn6] int NULL,

    [IntColumn7] int NULL,

    [IntColumn8] int NULL,

    [IntColumn9] int NULL,

    [IntColumn10] int NULL,

    [FloatColumn1] float NULL,

    [FloatColumn2] float NULL,

    [FloatColumn3] float NULL,

    [FloatColumn4] float NULL,

    [FloatColumn5] float NULL    

)

GO

 

-- Load sample data into table

DECLARE @val INT

SELECT @val=1

WHILE @val < 5000000

BEGIN

INSERT INTO SampleData

VALUES (@val,

CAST(CONVERT(varchar,DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 365),

'2015-01-01'),112) as integer),

CAST(CONVERT(varchar,DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 365),

'2015-01-01'),112) as integer),

'TEST' + cast(round(rand()*100,0) AS VARCHAR),

'TEST' + cast(round(rand()*100,0) AS VARCHAR),

'TEST' + cast(round(rand()*100,0) AS VARCHAR),

'TEST' + cast(round(rand()*100,0) AS VARCHAR),

'TEST' + cast(round(rand()*100,0) AS VARCHAR),

round(rand()*100000,0),

round(rand()*100000,0),     

round(rand()*100000,0),     

round(rand()*100000,0),     

round(rand()*100000,0),

round(rand()*100000,0),

round(rand()*100000,0),

round(rand()*100000,0),

round(rand()*100000,0),

round(rand()*100000,0),

     round(rand()*10000,2),

     round(rand()*10000,2),

     round(rand()*10000,2),

     round(rand()*10000,2),

     round(rand()*10000,2))

SELECT @val=@val+1

END

GO

 

 

데이터 생성이 완료 되었으면 성능 비교를 위해 B-tree 인덱스와 컬럼스토어 인덱스를 생성한다.

-- b-tree index

CREATE NONCLUSTERED INDEX IX_SampleData_Reg

ON SampleData (VarcharColumn1,FloatColumn1);

GO

 

-- column store index

CREATE NONCLUSTERED COLUMNSTORE INDEX IX_SampleData_ColStore

ON SampleData (VarcharColumn1,FloatColumn1);

GO

 

 

각 인덱스를 사용한 쿼리를 실행하여 성능을 측정한다. 실행 측정 결과는 프로파일러를 사용하였다.

-- column store query

SELECT VarcharColumn1,avg(FloatColumn1)

FROM SampleData GROUP BY VarcharColumn1

GO

 

-- b-tree query

SELECT VarcharColumn1,avg(FloatColumn1)

FROM SampleData GROUP BY VarcharColumn1

OPTION (TABLE HINT(SampleData, INDEX (IX_SampleData_Reg)))

GO

 

 

결과를 살펴보면 컬럼스토어 인덱스 성능이 훨씬 낫다는 것을 실험에서 확인할 수 있다.

 

[참고자료]

 

 

2015-09-14 / 강성욱 / http://sqlmvp.kr

 

SQL Server, MSSQL, 컬럼스토어 인덱스, Columnstore Index, 컬럼스토어 인덱스 성능, 열 저장소, 데이터베이스, 컬럼 기반 인덱스

'SQL Server > SQL Server Tip' 카테고리의 다른 글

SQL Server 임시 테이블 특성  (0) 2015.09.30
트리거를 사용하여 특정 컬럼 업데이트 하기 (After 트리거)  (0) 2015.09.16
In-Memory 최적화 파일 경로 변경  (0) 2015.09.08
SQL Server 2016 Query Store -실행된 쿼리에 대한 실행 계획 및 통계 정보를 확인하자  (0) 2015.09.01
SQL Server 2016 동적 데이터 마스킹을 우회하여 원본 데이터 출력하기  (0) 2015.08.27
BULK LOAD, Column Store Index, Compress Row Group, Delta Row Group, SQL, sql server 2012, 대용량 데이터베이스, 대용량데이터로드, 데이터베이스, 메모리 인덱스, 컬럼스토어 인덱스, 행 그룹

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

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

 

 

2014-09-29 / 강성욱 / http://sqlmvp.kr

 

SQL Server 2012, 컬럼스토어 인덱스, 행 그룹, Column Store Index, 메모리 인덱스, SQL, 데이터베이스, 대용량 데이터베이스, Delta Row Group, Compress Row Group, bulk load, 대용량데이터로드

'SQL Server > SQL Server Tip' 카테고리의 다른 글

페이지 ID로 테이블 이름 찾기  (0) 2015.07.23
DBCC CHECKPRIMARYFILE 사용법  (0) 2015.07.23
컬럼스토어 인덱스 INSERT 작업과 동시성  (0) 2015.07.23
컬럼스토어 인덱스 동시성  (0) 2015.07.23
컬럼스토어 인덱스 ROW와 ROWGROUP 영향  (0) 2015.07.23
Column Store Index, Compress Row Group, Delta Row Group, Read Uncommitted, SQL, sql server 2012, TRANSACTION ISOLATION LEVEL, 대용량 데이터베이스, 데이터베이스, 메모리 인덱스, 스냅샷 격리수준, 컬럼스토어 인덱스, 트랜잭션 격리수즌, 행 그룹

컬럼스토어 인덱스 INSERT 작업과 동시성

Column Store Index Concurrency with INSERT

 

  • Version : SQL Server 2012, 2014

 

클러스터 컬럼 스토어 INSERT

이전 블로그에 설명한 바와 같이 클러스터된 컬럼스토어 인덱스는 DW 시나리오에서 빠른 쿼리 성능으로 데이터로드에 최적화 되어 있다. 대량의 인서트 작업에서도 DW 쿼리는 커밋되지 않은 읽기(Read Uncommitted) 격리 수준에서 병렬로 데이터를 로드 할 수 있다.

 

데이터가 동시에 인서트 될 때 잠금 동작에 대해서 알아 본다.

Version : SQL Server 2014

CREATE TABLE [dbo].[T_ACCOUNT](

[accountkey] [int] IDENTITY(1,1) NOT NULL,

[accountdescription] [nvarchar](50) NULL

) ON [PRIMARY]

 

-- create a CCI

CREATE CLUSTERED COLUMNSTORE INDEX ACCOUNT_CCI ON T_ACCOUNT

 

 

INSERT Operations

한 행을 인서트하고 잠금을 살펴본다. 참고로 트랜잭션을 커밋하지 않았다.

세션1

세션 2

begin tran

    insert into T_ACCOUNT (accountdescription ) values ('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

 

다른 세션에서 행을 인서트한다. 그리고 잠금 상태를 확인 한다. 세션 54에서 두 번째 트랜잭션이 같은 RowGroup에 행을 삽입한 것에 유의한다. 동시 인서트로 서로를 차단하지 않고 RowGroup 데이터를 로드 할 수 있다.

세션 2

세션 3

 

begin tran

    insert into T_ACCOUNT (accountdescription ) values ('row-2');

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

  

 

 

요약하면 CCI에 삽입은 동일한 Delta RowGroup에 인서트 작업과 데이터 로드시 서로를 차단하지 않는다.

 

[참고자료]

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2014/07/27/clustered-column-store-index-concurrency-with-insert-operations.aspx

 

2014-09-25 / 강성욱 / http://sqlmvp.kr

 

SQL Server 2012, 컬럼스토어 인덱스, 행 그룹, Column Store Index, 메모리 인덱스, SQL, 데이터베이스, 대용량 데이터베이스, Delta Row Group, Compress Row Group, 트랜잭션 격리수즌, read uncommitted, transaction isolation level, 스냅샷 격리수준

'SQL Server > SQL Server Tip' 카테고리의 다른 글

DBCC CHECKPRIMARYFILE 사용법  (0) 2015.07.23
컬럼스토어 인덱스 대용량 데이터 로드  (0) 2015.07.23
컬럼스토어 인덱스 동시성  (0) 2015.07.23
컬럼스토어 인덱스 ROW와 ROWGROUP 영향  (0) 2015.07.23
테이블 변수와 TF 2453  (0) 2015.07.23

+ Recent posts

티스토리툴바