Columnstore 인덱스 - 데이터 로드 지침Columnstore indexes - data loading guidance

이 항목 적용 대상: 예SQL Server예Azure SQL 데이터베이스예Azure SQL 데이터 웨어하우스 예 병렬 데이터 웨어하우스THIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

표준 SQL 대량 로드와 trickle insert 메서드를 사용하여 columnstore 인덱스에 데이터를 로드하는 작업에 대한 옵션 및 권장 사항입니다.Options and recommendations for loading data into a columnstore index by using the standard SQL bulk loading and trickle insert methods. 분석을 위한 준비 과정으로 데이터를 인덱스로 이동하기 때문에 columnstore 인덱스에 데이터를 로드하는 작업은 데이터 웨어하우징 프로세스의 필수 요소입니다.Loading data into a columnstore index is an essential part of any data warehousing process because it moves data into the index in preparation for analytics.

columnstore 인덱스를 처음 사용하십니까?New to columnstore indexes? Columnstore 인덱스 - 개요Columnstore 인덱스 - 아키텍처를 참조하세요.See Columnstore indexes - overview and Columnstore indexes - architecture.

대량 로드란 무엇인가요?What is bulk loading?

대량 로드는 많은 행이 데이터 저장소에 추가되는 방식을 가리킵니다.Bulk loading refers to the way large numbers of rows are added to a data store. 행 일괄 처리로 작동하기 때문에 데이터를 columnstore 인덱스로 이동하는 가장 성능 효과적인 방법입니다.It is the most performant way to move data into a columnstore index because it operates on batches of rows. 대량 로드는 최대 용량까지 행 그룹을 채우고 columnstore에 직접 압축합니다.Bulk loading fills rowgroups to maximum capacity and compresses them directly into the columnstore. 행 그룹당 102,400개의 최소 행 수를 충족하지 않는, 로드의 끝에 있는 행만 deltastore로 이동합니다.Only rows at the end of a load that don't meet the minimum of 102,400 rows per rowgroup go to the deltastore.
대량 로드를 수행하려면 bcp 유틸리티 또는 Integration Services를 사용하거나 준비 테이블에서 행을 선택합니다.s To perform a bulk load, you can use bcp Utility, Integration Services, or select rows from a staging table.

클러스터형 columnstore 인덱스로 로드Loading into a clustered columnstore index

다이어그램에서 알 수 있듯이 대량 로드::As the diagram suggests, a bulk load::

  • 데이터를 미리 정렬하지 않습니다.Does not pre-sort the data. 수신하는 순서대로 데이터가 행 그룹으로 삽입됩니다.Data is inserted into rowgroups in the order it is received.
  • 일괄 처리 크기가 102400 이상인 경우 행은 압축된 행 그룹으로 바로 삽입됩니다.If the batch size is >= 102400, the rows are directly into the compressed rowgroups. 백그라운드 스레드, 튜플 이동기(TM)에 의해 행이 결과적으로 압축된 행 그룹으로 이동되기 전에 데이터 행을 델타 행 그룹으로 이동하는 것을 방지할 수 있으므로 효율적인 대량 가져오기를 위해 102400 이상의 일괄 처리 크기를 선택하는 것이 좋습니다.It is recommended that you choose a batch size >=102400 for efficient bulk import because you can avoid moving data rows to a delta rowgroups before the rows are eventually moved to compressed rowgroups by a background thread, Tuple mover (TM).
  • 일괄 처리 크기가 102400 미만이거나 남은 행이 102400인 경우 행은 델타 행 그룹으로 로드됩니다.If the batch size < 102400 or if the remaining rows are < 102400, the rows are loaded into delta rowgroups.

![참고] 비클러스터형 columnstore 인덱스 데이터가 있는 rowstore 테이블에서 SQL ServerSQL Server은 항상 데이터를 기본 테이블에 삽입합니다.![Note] On a rowstore table with a nonclustered columnstore index data, SQL ServerSQL Server always inserts data into the base table. 데이터는 columnstore 인덱스에 직접 삽입되지 않습니다.The data is never inserted directly into the columnstore index.

대량 로드에는 다음과 같은 기본 제공 성능 최적화 기능이 있습니다.Bulk loading has these built-in performance optimizations:

  • 병렬 로드.Parallel loads. 각각 데이터 파일을 로드하는 여러 개의 동시 대량 로드(bcp 또는 대량 삽입)를 수행할 수 있습니다.You can have multiple concurrent bulk loads (bcp or bulk insert) that are each loading a separate data file. SQL Server에 대한 rowstore 대량 로드와 달리, 각 대량 가져오기 스레드가 배타적 잠금 상태의 별도 행 그룹(압축 또는 델타 행 그룹)으로 배타적으로 로드되므로 TABLOCK을 지정할 필요가 없습니다.Unlike rowstore bulk loads into SQL Server, you don't need to specify TABLOCK because each bulk import thread will load data exclusively into a separate rowgroups (compressed or delta rowgroups) with exclusive lock on it. TABLOCK을 사용하면 테이블에 대한 배타적 잠금이 강제 적용되어 데이터를 병렬로 가져올 수 없게 됩니다.Using TABLOCK will force an exclusive lock on the table and you will not be able to import data in parallel.

  • 최소 로깅.Minimal logging. 대량 로드는 압축된 행 그룹으로 바로 이동하는 데이터에 대해 최소 로깅을 사용합니다.A bulk load uses minimal logging on data that goes directly to compressed rowgroups. 델타 행 그룹으로 이동하는 데이터는 자세히 로깅됩니다.Any data that goes to a delta rowgroup is fully logged. 여기에는 102,400개의 행보다 작은 모든 일괄 처리 크기가 포함됩니다.This includes any batch sizes that are less than 102,400 rows. 그러나 대량 로드의 목표는 대부분의 데이터가 델타 행 그룹을 우회하도록 하는 것입니다.However, with bulk loading the goal is for most of the data to bypass delta rowgroups.

  • 잠금 최적화.Locking Optimization. 압축된 행 그룹으로 로드 시 행 그룹에 대한 X 잠금이 획득됩니다.When loading into compressed rowgroup, the X lock on rowgroup is acquired. 그러나 델타 행 그룹으로 대량 로드 시 행 그룹에서 X 잠금이 획득되지만 SQL Server는 X 행 그룹 잠금이 잠금 계층 구조의 일부가 아니므로, 잠금 PAGE/EXTENT를 그대로 잠급니다.However, when bulk loading into delta rowgroup, an X lock is acquired at rowgroup but SQL Server still locks the locks PAGE/EXTENT because X rowgroup lock is not part of locking hierarchy.

columnstore 인덱스에 비클러스터형 btree 인덱스가 있는 경우 인덱스 자체에 대한 잠금 또는 로깅 최적화는 없지만 위의 설명대로 클러스터형 columnstore 인덱스에 대한 최적화는 여전히 남아 있습니다.If you have a nonclustered btree index on a columnstore index, there is no locking or logging optimization for the index itself but the optimizations on clustered columnstore index as described above are still there.

델타 행 그룹을 최소화하는 대량 로드 크기 계획Plan bulk load sizes to minimize delta rowgroups

columnstore 인덱스는 대부분의 행이 columnstore로 압축되고 델타 행 그룹에 저장되지 않을 때 성능이 최적화됩니다.Columnstore indexes perform best when most of the rows are compressed into the columnstore and not sitting in delta rowgroups. 행이 columnstore로 바로 이동하고 최대한 deltastore를 우회하도록 로드 크기를 지정하는 것이 좋습니다.It's best to size your loads so that rows go directly to the columnstore and bypass the deltastore as much as possible.

다음 시나리오에서는 로드된 행이 언제 columnstore로 곧바로 이동하거나 deltastore로 이동하는지를 설명합니다.These scenarios describe when loaded rows go directly to the columnstore or when they go to the deltastore. 예를 들어, 각 행 그룹에는 행 그룹당 102,400-1,048,576행이 있을 수 있습니다.In the example, each rowgroup can have 102,400-1,048,576 rows per rowgroup. 실제로 행 그룹의 최대 크기는 메모리 압박이 있는 경우 1,048,576개 행보다 적을 수 있습니다.In practice, the maximum size of a rowgroup can be smaller than 1,048,576 rows when there is memory pressure.

대량 로드할 행Rows to Bulk Load 압축된 행 그룹에 추가된 행Rows Added to the Compressed Rowgrouo 델타 행 그룹에 추가된 행Rows Added to the Delta Rowgroup
102,000102,000 00 102,000102,000
145,000145,000 145,000145,000

행 그룹 크기: 145,000Rowgroup size: 145,000
00
1,048,5771,048,577 1,048,5761,048,576

행 그룹 크기: 1,048,576Rowgroup size: 1,048,576.
11
2,252,1522,252,152 2,252,1522,252,152

행 그룹 크기: 1,048,576, 1,048,576, 155,000Rowgroup sizes: 1,048,576, 1,048,576, 155,000.
00

다음 예제에서는 1,048,577개 행을 테이블로 로드하는 결과를 보여 줍니다.The following example shows the results of loading 1,048,577 rows into a table. 결과에는 columnstore에 COMPRESSED 행 그룹이 하나 있고(열 세그먼트로 압축됨) deltastore에 행이 1개 있습니다.The results show that one COMPRESSED rowgroup in the columnstore (as compressed column segments), and 1 row in the deltastore.

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  

일괄 처리 로드를 위한 행 그룹 및 deltastoreRowgroup and deltastore for a batch load

준비 테이블을 사용하여 성능 향상Use a staging table to improve performance

추가 변환을 실행하기 전의 준비 과정으로만 데이터를 로드하는 경우 힙 테이블에 테이블을 로드하는 것이 클러스터형 columnstore 테이블에 데이터를 로드하는 것보다 훨씬 더 빠릅니다.If you are loading data only to stage it before running more transformations, loading the table to heap table will be much faster than loading the data to a clustered columnstore table. 또한 [임시 테이블][Temporary]에 데이터를 로드하는 것이 영구 저장소에 테이블을 로드하는 것보다 훨씬 더 빠르게 로드됩니다.In addition, loading data to a [temporary table][Temporary] will also load much faster than loading a table to permanent storage.

데이터 로드에 대한 일반적인 패턴은 데이터를 준비 테이블로 로드하고 일부 변형을 수행한 후 다음 명령을 사용하여 대상 테이블로 로드하는 것입니다.A common pattern for data load is to load the data into a staging table, do some transformation and then load it into the target table using the following command

INSERT INTO <columnstore index>  SELECT <list of columns> FROM <Staging Table>  

이 명령은 BCP 또는 Bulk Insert와 유사한 방식이지만, 단일 일괄 처리로 columnstore 인덱스에 데이터를 로드합니다.This command loads the data into the columnstore index in similar ways to BCP or Bulk Insert but in a single batch. 준비 테이블의 행 수가 102400 미만인 경우 행은 델타 행 그룹으로 로드됩니다. 그렇지 않으면, 행은 압축된 행 그룹으로 바로 로드됩니다.If the number of rows in the staging table < 102400, the rows are loaded into a delta rowgroup otherwise the rows are directly loaded into compressed rowgroup. 한 가지 주요 제한 사항은 이 INSERT 작업이 단일 스레드라는 것입니다.One key limitation was that this INSERT operation was single threaded. 병렬로 데이터를 로드하려면 여러 준비 테이블을 만들거나 준비 테이블에서 행의 범위가 겹치지 않는 INSERT/SELECT를 실행할 수 있습니다.To load data in parallel, you could create multiple staging table or issue INSERT/SELECT with non-overlapping ranges of rows from the staging table. 이 제한 사항은 SQL Server 2016에서 사라집니다.This limitation goes away with SQL Server 2016. 아래 명령은 준비 테이블에서 데이터를 병렬로 로드하지만 TABLOCK을 지정해야 합니다.The command below loads the data from staging table in parallel but you will need to specify TABLOCK

INSERT INTO <columnstore index>  WITH (TABLOCK)  SELECT <list of columns> FROM <Staging Table>  

준비 테이블에서 클러스터형 columnstore 인덱스로 대량 로드 시 사용 가능한 최적화는 다음과 같습니다.There are following optimizations available when loading into clustered columnstore index from staging table

  • 로그 최적화: 데이터가 압축된 행 그룹으로 로드될 때 둘 다 최소로 기록됩니다.Log Optimization: Minimally logged both when the data is loaded into compressed rowgroup. 데이터가 델타 행 그룹으로 로드되는 경우 최소 로깅은 없습니다.No minimal logging when data gets loaded into delta rowgroup.

  • 잠금 최적화: 압축된 행 그룹으로 로드 시 행 그룹에 대한 X 잠금이 획득됩니다.Locking Optimization: When loading into compressed rowgroup, the X lock on rowgroup is acquired. 그러나 델타 행 그룹을 통해 X 잠금이 행 그룹에서 획득되지만 SQL Server는 X 행 그룹 잠금이 잠금 계층 구조의 일부가 아니므로, 잠금 PAGE/EXTENT를 그대로 잠급니다.However, with delta rowgroup, an X lock is acquired at rowgroup but SQL Server still locks the locks PAGE/EXTENT because X rowgroup lock is not part of locking hierarchy.

    하나 이상의 비클러스터형 인덱스가 있는 경우 인덱스 자체에 대한 잠금 또는 로깅 최적화는 없지만 위의 설명대로 클러스터형 columnstore 인덱스에 대한 최적화는 여전히 남아 있습니다.If you have or more nonclustered indexes, there is no locking or logging optimization for the index itself but the optimizations on clustered columnstore index as described above are still there

trickle insert란 무엇인가요?What is trickle insert?

trickle insert는 개별 행이 columnstore 인덱스로 이동하는 방식을 가리킵니다.Trickle insert refers to the way individual rows move into the columnstore index. trickle insert는 INSERT INTO 문을 사용합니다.Trickle inserts use the INSERT INTO statement. trickle insert를 사용할 경우 모든 행이 deltastore로 이동합니다.With trickle insert, all of the rows go to the deltastore. 이 기능은 행 수가 적은 경우에 유용하며 대규모 로드에는 적합하지 않습니다.This is useful for small numbers of rows, but not practical for large loads.

INSERT INTO <table-name> VALUES (<set of values>)  

단, 클러스터형 columnstore 인덱스로 값을 삽입하기 위해 INSERT INTO를 사용하는 동시 스레드는 행을 동일한 deltastore 행 그룹에 삽입할 수 있습니다.Note, concurrent threads using INSERT INTO to insert values into a clustered columnstore index can insert rows into the same deltastore rowgroup.

행 그룹에 1,048,576개 행이 포함되면 델타 행 그룹은 closed(닫힘)로 표시해도 쿼리와 업데이트/삭제 작업은 할 수 있지만 새로 삽입된 행은 기존 또는 새롭게 만든 deltastore 행 그룹으로 이동됩니다.Once the rowgroup contains 1,048,576 rows, the delta rowgroup us marked closed but it is still available for queries and update/delete operations but the newly inserted rows go into an existing or newly created deltastore rowgroup. 닫힌 델타 행 그룹을 5분 간격으로 압축하는 백그라운드 스레드 TM(튜플 이동기) 가 있습니다.There is a background thread Tuple Mover (TM) that compresses the closed delta rowgroups periodically every 5 minutes or so. 닫힌 델타 행 그룹을 압축하기 위해 다음 명령을 명시적으로 호출할 수 있습니다.You can explicitly invoke the following command to compress the closed delta rowgroup

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

델타 행 그룹에 닫힘 및 압축을 강제 적용하려는 경우 다음 명령을 실행할 수 있습니다.If you want force a delta rowgroup closed and compressed, you can execute the following command. 행 로드를 완료하고 새로운 행을 원하지 않을 경우 이 명령을 실행할 수 있습니다.You may want run this command if you are done loading the rows and don't expect any new rows. 델타 행 그룹을 명시적으로 닫고 압축하면 저장 공간을 추가로 절약하고 분석 쿼리 성능을 개선할 수 있습니다.By explicitly closing and compressing the delta rowgroup, you can save storage further and improve the analytics query performance. 새 행의 삽입을 원하지 않는 경우 이 명령을 호출하는 것이 가장 좋은 방법입니다.A best practice is to invoke this command if you don't expect new rows to be inserted.

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

분할된 테이블로 로드의 작동 방식How loading into a partitioned table works

분할된 데이터에 대해 SQL ServerSQL Server 에서는 먼저 각 행을 파티션에 할당한 다음 파티션 내에서 데이터에 columnstore 작업을 수행합니다.For partitioned data, SQL ServerSQL Server first assigns each row to a partition, and then performs columnstore operations on the data within the partition. 각 파티션에는 고유한 행 그룹과 하나 이상의 델타 행 그룹이 있습니다.Each partition has its own rowgroups and at least one delta rowgroup.

다음 단계Next steps

로드 방법에 대한 자세한 내용은 이 블로그 게시물을 참조하세요.For further discussion on loading, see this blog post.