Columnstore 인덱스 - 데이터 로드 지침

아티클
09/14/2023

적용 대상:SQL Server Azure SQL Database Azure SQL Managed InstanceAzure Synapse Analytics Analytics Platform System(PDW)

표준 SQL 대량 로드 및 세류 삽입 메서드를 사용하여 columnstore 인덱스에 데이터를 로드하기 위한 옵션 및 권장 사항입니다. 데이터를 columnstore 인덱스에 로드하는 것은 분석을 준비하기 위해 데이터를 인덱스로 이동하기 때문에 데이터 웨어하우징 프로세스의 필수적인 부분입니다.

columnstore 인덱스를 처음 사용하십니까? Columnstore 인덱스 - 개요 및 Columnstore 인덱스 아키텍처를 참조하세요.

대량 로드란 무엇인가요?

대량 로드 는 많은 수의 행이 데이터 저장소에 추가되는 방식을 나타냅니다. 행 일괄 처리에서 작동하기 때문에 데이터를 columnstore 인덱스로 이동하는 가장 성능이 좋은 방법입니다. 대량 로드는 최대 용량까지 행 그룹을 채우고 columnstore에 직접 압축합니다. 부하가 끝날 때 행 그룹당 최소 102,400개의 행을 충족하지 않는 행만 deltastore로 이동합니다.

대량 로드를 수행하려면 bcp 유틸리티 또는 Integration Services를 사용하거나 준비 테이블에서 행을 선택합니다.

다이어그램에 나와 있듯이 대량 로드는 다음과 같습니다.

데이터를 미리 정렬하지 않습니다. 데이터는 수신된 순서대로 행 그룹에 삽입됩니다.
일괄 처리 크기가 >= 102400이면 행이 압축된 행 그룹에 직접 로드됩니다. 행이 최종적으로 백그라운드 스레드인 TM(튜플 이동기)에 의해 압축된 행 그룹으로 이동되기 전에 데이터 행을 델타 행 그룹으로 이동하지 않도록 할 수 있으므로 효율적인 대량 가져오기를 위해 일괄 처리 크기 >=102400을 선택해야 합니다.
일괄 처리 크기 < 가 102,400이거나 다시 기본 행이 < 102,400인 경우 행이 델타 행 그룹에 로드됩니다.

참고 항목

비클러스터형 columnstore 인덱스 데이터가 있는 rowstore 테이블에서 SQL Server는 항상 데이터를 기본 테이블에 삽입합니다. 데이터는 columnstore 인덱스에 직접 삽입되지 않습니다.

대량 로드에는 다음과 같은 기본 제공 성능 최적화가 있습니다.

병렬 로드: 각각 별도의 데이터 파일을 로드하는 여러 개의 동시 대량 로드(bcp 또는 대량 삽입)를 가질 수 있습니다. SQL Server에 대한 rowstore 대량 로드와 달리, 각 대량 가져오기 스레드는 단독 잠금이 있는 별도의 행 그룹(압축 또는 델타 행 그룹)에만 데이터를 로드하기 때문에 지정할 TABLOCK 필요가 없습니다.
로깅 감소: 압축된 행 그룹에 직접 로드되는 데이터는 로그 크기를 크게 줄입니다. 예를 들어 데이터가 10배 압축된 경우 해당 트랜잭션 로그는 TABLOCK 또는 대량 로그/단순 복구 모델을 요구하지 않고 약 10배 더 작습니다. 델타 행 그룹으로 가는 모든 데이터는 완전히 기록됩니다. 여기에는 102,400개의 행보다 작은 모든 일괄 처리 크기가 포함됩니다. 일괄 처리 = 102400을 >사용하는 것이 가장 좋습니다. TABLOCK이 필요하지 않으므로 데이터를 병렬로 로드할 수 있습니다.
최소 로깅: 최소 로깅에 대한 필수 구성 요소를 따르는 경우 로깅을 추가로 감소시킬 수 있습니다. 그러나 데이터를 rowstore에 로드하는 것과 달리 TABLOCK은 BU(대량 업데이트) 잠금이 아닌 테이블의 X 잠금으로 이어지므로 병렬 데이터 로드를 수행할 수 없습니다. 잠금에 대한 자세한 내용은 잠금 및 행 버전 관리를 참조 하세요.
잠금 최적화: 압축된 행 그룹에 데이터를 로드할 때 행 그룹의 X 잠금이 자동으로 획득됩니다. 그러나 델타 행 그룹으로 대량 로드하는 경우 행 그룹에서 X 잠금을 획득하지만 X 행 그룹 잠금이 잠금 계층 구조의 일부가 아니므로 SQL Server는 여전히 PAGE/EXTENT를 잠급 수 있습니다.

columnstore 인덱스에 비클러스터형 B-트리 인덱스가 있는 경우 인덱스 자체에 대한 잠금 또는 로깅 최적화는 없지만 앞에서 설명한 대로 클러스터형 columnstore 인덱스에 대한 최적화를 적용할 수 있습니다.

데이터 수정(삽입, 삭제, 업데이트)은 병렬이 아니므로 일괄 처리 모드 작업이 아닙니다.

델타 행 그룹을 최소화하는 대량 로드 크기 계획

Columnstore 인덱스는 대부분의 행이 델타 행 그룹에 있지 않고 columnstore로 압축될 때 가장 잘 수행됩니다. 행이 columnstore로 직접 이동하여 델타 저장소를 최대한 우회하도록 로드 크기를 조정하는 것이 가장 좋습니다.

이러한 시나리오에서는 로드된 행이 columnstore로 직접 이동하거나 deltastore로 이동하는 경우를 설명합니다. 이 예제에서 각 행 그룹에는 행 그룹당 102,400-1,048,576개의 행이 있을 수 있습니다. 실제로 메모리 압력이 있을 때 행 그룹의 최대 크기는 1,048,576개 행보다 작을 수 있습니다.

대량 로드할 행	압축된 행 그룹에 추가된 행	델타 행 그룹에 추가된 행
102,000	0	102,000
145,000	145,000 행 그룹 크기: 145,000	0
1,048,577	1,048,576 행 그룹 크기: 1,048,576	1
2,252,152	2,252,152 행 그룹 크기: 1,048,576, 1,048,576, 155,000.	0

다음 예제에서는 테이블에 1,048,577개의 행을 로드한 결과를 보여 줍니다. 결과는 columnstore의 COMPRESSED 행 그룹 1개(압축 열 세그먼트)와 deltastore의 행 1개를 보여 줍니다.

SELECT object_id, index_id, partition_number, row_group_id, delta_store_hobt_id,
    state, state_desc, total_rows, deleted_rows, size_in_bytes
FROM sys.dm_db_column_store_row_group_physical_stats;

스테이징 테이블을 사용하여 성능 향상

더 많은 변환을 실행하기 전에 데이터를 스테이징하기 위해 데이터를 로드하는 경우 힙 테이블에 테이블을 로드하는 것이 클러스터형 columnstore 테이블에 데이터를 로드하는 것보다 훨씬 빠릅니다. 또한 [임시 테이블][임시]에 데이터를 로드하면 테이블을 영구 스토리지에 로드하는 것보다 훨씬 빠르게 로드됩니다.

데이터 로드의 일반적인 패턴은 데이터를 준비 테이블에 로드하고, 일부 변환을 수행하고, 다음 명령을 사용하여 대상 테이블에 로드하는 것입니다.

INSERT INTO [<columnstore index>]
SELECT col1 /* include actual list of columns in place of col1*/
FROM [<Staging Table>]

이 명령은 bcp 또는 대량 삽입과 비슷한 방식으로 단일 일괄 처리로 데이터를 columnstore 인덱스에 로드합니다. 준비 테이블 < 102400의 행 수가 델타 행 그룹에 로드되는 경우 행은 압축된 행 그룹에 직접 로드됩니다. 한 가지 주요 제한 사항은 이 INSERT 작업이 단일 스레드라는 것입니다. 데이터를 병렬로 로드하려면 여러 스테이징 테이블을 만들거나 스테이징 테이블에서 겹치지 않는 행 범위와 관련된 문제를 해결할 INSERT/SELECT 수 있습니다. 이 제한 사항은 SQL Server 2016(13.x)에서 사라집니다. 다음 명령은 준비 테이블에서 병렬로 데이터를 로드하지만 지정 TABLOCK해야 합니다. 이전에 대량 로드를 사용하여 말한 것과 모순될 수 있지만 주요 차이점은 준비 테이블의 병렬 데이터 로드가 동일한 트랜잭션에서 실행됨입니다.

INSERT INTO [<columnstore index>] WITH (TABLOCK)
SELECT col1 /* include actual list of columns in place of col1*/
FROM [<Staging Table>]

스테이징 테이블에서 클러스터형 columnstore 인덱스로 로드할 때 사용할 수 있는 최적화는 다음과 같습니다.

로그 최적화: 데이터가 압축된 행 그룹에 로드될 때 로깅이 줄어듭니다.
잠금 최적화: 압축된 행 그룹에 데이터를 로드할 때 행 그룹에 대한 X 잠금을 획득합니다. 그러나 델타 행 그룹을 사용하면 행 그룹에서 X 잠금이 획득되지만, X 행 그룹 잠금이 잠금 계층 구조의 일부가 아니므로 SQL Server는 여전히 PAGE/EXTENT 잠금을 잠급 수 있습니다.

하나 이상의 비클러스터형 인덱스가 있는 경우 인덱스 자체에 대한 잠금 또는 로깅 최적화는 없지만 앞에서 설명한 대로 클러스터형 columnstore 인덱스에 대한 최적화는 여전히 존재합니다.

세류 삽입이란?

Trickle 삽입 은 개별 행이 columnstore 인덱스로 이동하는 방식을 나타냅니다. Trickle 삽입은 INSERT INTO 문을 사용합니다. 트리클 삽입을 사용하면 모든 행이 deltastore로 이동합니다. 이는 적은 수의 행에 유용하지만 큰 로드에는 실용적이지 않습니다.

INSERT INTO [<table-name>] VALUES ('some value' /*replace with actual set of values*/)

참고 항목

클러스터형 columnstore 인덱스로 값을 삽입하기 위해 INSERT INTO를 사용하는 동시 스레드는 행을 동일한 deltastore 행 그룹에 삽입할 수 있습니다.

행 그룹에 1,048,576개의 행이 포함되면 델타 행 그룹은 닫힌 것으로 표시되었지만 쿼리 및 업데이트/삭제 작업에는 계속 사용할 수 있지만 새로 삽입된 행은 기존 또는 새로 만든 deltastore 행 그룹으로 이동합니다. 닫힌 델타 행 그룹을 5분 정도 주기적으로 압축하는 TM(백그라운드 스레드 튜플 Mover) 이 있습니다. 다음 명령을 명시적으로 호출하여 닫힌 델타 행 그룹을 압축할 수 있습니다.

ALTER INDEX [<index-name>] on [<table-name>] REORGANIZE

델타 행 그룹을 강제로 닫고 압축하려면 다음 명령을 실행할 수 있습니다. 행 로드가 완료되고 새 행이 필요하지 않은 경우 이 명령을 실행할 수 있습니다. 델타 행 그룹을 명시적으로 닫고 압축하면 스토리지를 추가로 저장하고 분석 쿼리 성능을 향상시킬 수 있습니다. 새 행이 삽입될 것으로 예상하지 않는 경우 이 명령을 호출하는 것이 가장 좋습니다.

ALTER INDEX [<index-name>] on [<table-name>] REORGANIZE with (COMPRESS_ALL_ROW_GROUPS = ON)

분할된 테이블로 로드의 작동 방식

분할된 데이터의 경우 SQL Server는 먼저 파티션에 각 행을 할당한 다음 파티션 내의 데이터에 대해 columnstore 작업을 수행합니다. 각 파티션에는 고유한 행 그룹과 하나 이상의 델타 행 그룹이 있습니다.

다음 단계

클러스터형 Columnstore 인덱스를 사용하여 데이터 로드 성능 고려 사항