Columnstore 인덱스 - 데이터 로드 지침
적용 대상:
Microsoft Fabric의 SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System(PDW) SQL 데이터베이스
표준 SQL 대량 로드 및 지속적 삽입 메서드를 사용하여 columnstore 인덱스에 데이터를 로드하기 위한 옵션 및 권장 사항입니다. 데이터를 columnstore 인덱스에 로드하는 것은 분석 준비를 위해 데이터를 인덱스로 이동하기 때문에 데이터 웨어하우징 프로세스의 필수적인 부분입니다.
columnstore 인덱스를 처음 사용하십니까? Columnstore 인덱스 - 개요 및 Columnstore 인덱스 아키텍처를 참조하세요.
대량 로드란 무엇인가요?
대량 로드는 많은 수의 행이 데이터 저장소에 추가되는 방식을 나타냅니다. 행 배치(batch)에 대해 작동하기 때문에 데이터를 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개의 행보다 작은 모든 일괄 처리 크기가 포함됩니다. 모범 사례는 batchsize >= 102400을 사용하는 것입니다. TABLOCK이 필요하지 않으므로 데이터를 병렬로 로드할 수 있습니다.
최소 로깅: 최소 로깅에 대한 필수 구성 요소를 따르는 경우 로깅을 추가로 줄일 수 있습니다. 그러나 데이터를 rowstore에 로드하는 것과 달리 TABLOCK은 BU(대량 업데이트) 잠금이 아닌 테이블의 X 잠금으로 이어지므로 병렬 데이터 로드를 수행할 수 없습니다. 잠금에 대한 자세한 내용은 잠금 및 행 버전 관리를 참조하세요.
잠금 최적화: 압축된 행 그룹에 데이터를 로드할 때 행 그룹의 X 잠금이 자동으로 획득됩니다. 그러나 델타 행 그룹으로 대량 로드하는 경우 행 그룹에서 X 잠금을 획득하지만 X 행 그룹 잠금이 잠금 계층 구조의 일부가 아니므로 SQL Server는 여전히 PAGE/EXTENT를 잠글 수 있습니다.
columnstore 인덱스에 비클러스터링형 B-트리 인덱스가 있는 경우 인덱스 자체에 대한 잠금 또는 로깅 최적화는 없지만 위의 설명대로 클러스터형 columnstore 인덱스에 대한 최적화는 적용 가능합니다.
데이터 수정(삽입, 삭제, 업데이트)은 병렬이 아니므로 일괄 처리 모드 작업이 아닙니다.
델타 행 그룹을 최소화하는 대량 로드 크기 계획
Columnstore 인덱스는 대부분의 행이 델타 행 그룹에 있지 않고 columnstore로 압축될 때 가장 잘 수행됩니다. 행이 columnstore로 직접 이동하여 deltastore를 최대한 우회하도록 로드 크기를 조정하는 것이 좋습니다.
이러한 시나리오에서는 로드된 행이 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 인덱스에 대한 최적화는 여전히 남아 있습니다.
지속적 삽입이란?
지속적 삽입은 개별 행이 columnstore 인덱스로 이동하는 방식을 나타냅니다. 지속적 삽입은 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(튜플 이동기)이 있습니다. 닫힌 델타 행 그룹을 압축하기 위해 다음 명령을 명시적으로 호출할 수 있습니다.
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 작업을 수행합니다. 각 파티션에는 고유한 행 그룹과 하나 이상의 델타 행 그룹이 있습니다.