순서가 지정된 columnstore 인덱스를 사용하여 성능 튜닝
<`c0>적용 대상:
효율적인 세그먼트 제거를 사용하도록 설정하면 정렬된 columnstore 인덱스는 쿼리 조건자와 일치하지 않는 대량의 정렬된 데이터를 건너뛰어 더 빠른 성능을 제공합니다. 정렬된 columnstore 인덱스에 데이터를 로드하고 인덱스 다시 작성을 통해 정렬된 상태로 유지하면 데이터 정렬 작업으로 인해 정렬되지 않은 인덱스보다 오래 걸릴 수 있지만 순서가 지정된 columnstore 인덱스를 사용하면 나중에 쿼리가 더 빠르게 실행될 수 있습니다.
사용자가 columnstore 테이블을 쿼리할 때 최적화 프로그램은 각 세그먼트에 저장된 최소값과 최대값을 확인합니다. 쿼리 조건자의 경계를 벗어난 세그먼트는 디스크에서 메모리로 읽지 않습니다. 읽을 세그먼트 수와 총 크기가 더 작은 경우 쿼리가 더 빨리 완료할 수 있습니다.
순서가 지정된 컬럼스토어 인덱스의 가용성에 대해서는 순서가 지정된 컬럼스토어 인덱스의 가용성을 참조하세요.
columnstore 인덱스에 대해 최근에 추가된 기능에 대한 자세한 내용은 columnstore 인덱스의 새로운 기능을 참조하세요.
순서가 지정된 columnstore 인덱스 및 순서가 지정되지 않은 columnstore 인덱스 비교
columnstore 인덱스에서 각 행 그룹의 각 열에 있는 데이터는 별도의 세그먼트로 압축됩니다. 각 세그먼트에는 최소값과 최대값을 설명하는 메타데이터가 포함되어 있으므로 쿼리 조건자의 범위를 벗어난 세그먼트는 쿼리 실행 중에 디스크에서 읽지 않습니다.
columnstore 인덱스가 정렬되지 않은 경우 인덱스 작성기는 데이터를 세그먼트로 압축하기 전에 정렬하지 않습니다. 즉, 값 범위가 겹치는 세그먼트가 발생할 수 있으므로 쿼리가 디스크에서 더 많은 세그먼트를 읽고 완료하는 데 시간이 더 오래 걸립니다.
정렬된 columnstore 인덱스를 만들 때 데이터베이스 엔진은 인덱스 작성기에서 세그먼트로 압축하기 전에 지정한 순서 키를 기준으로 기존 데이터를 정렬합니다. 정렬된 데이터를 사용하면 세그먼트 겹침이 줄어들거나 제거되므로 디스크에서 읽을 세그먼트가 적기 때문에 쿼리가 보다 효율적인 세그먼트 제거 및 더 빠른 성능을 가질 수 있습니다.
사용 가능한 메모리, 데이터 크기, 병렬 처리 수준, 인덱스 형식(클러스터형 및 비클러스터형) 및 인덱스 빌드 유형(오프라인 및 온라인)에 따라 정렬된 columnstore 인덱스의 정렬이 꽉 찼거나(세그먼트가 겹치지 않음) 부분(일부 세그먼트 겹침)일 수 있습니다. 예를 들어 전체 정렬에 사용 가능한 메모리가 부족한 경우 부분 정렬이 발생합니다. 정렬된 columnstore 인덱스를 사용하는 쿼리는 정렬된 인덱스가 부분 정렬을 사용하여 빌드된 경우에도 순서가 지정되지 않은 인덱스보다 빠르게 실행되는 경우가 많습니다.
전체 정렬은 ONLINE = ON 및 MAXDOP = 1 옵션을 모두 사용하여 만들거나 다시 작성한 정렬된 클러스터형 columnstore 인덱스에 대해 제공됩니다. 이 경우 정렬은 tempdb 데이터베이스를 사용하여 메모리에 맞지 않는 데이터를 분산하기 때문에 사용 가능한 메모리에 의해 제한되지 않습니다. 이로 인해 추가 tempdb I/O로 인해 인덱스 빌드 프로세스가 느려질 수 있습니다. 그러나 온라인 인덱스를 다시 작성하면 순서가 지정된 새 인덱스를 다시 작성하는 동안 쿼리에서 기존 인덱스를 계속 사용할 수 있습니다.
정렬할 데이터의 양이 사용 가능한 메모리에 완전히 맞을 만큼 충분히 작은 경우 ONLINE = OFF 및 MAXDOP = 1 옵션을 모두 사용하여 만들거나 다시 작성한 정렬된 클러스터형 및 비클러스터형 columnstore 인덱스에 대해 전체 정렬이 제공될 수도 있습니다.
다른 모든 경우에, 정렬된 columnstore 인덱스의 정렬은 부분적입니다.
참고
현재 순서가 지정된 columnstore 인덱스는 Azure SQL Database 및 Azure SQL Managed Instance에서만 Always-up-to-date 업데이트 정책사용하여 온라인으로 만들거나 다시 작성할 수 있습니다.
열의 세그먼트 범위를 확인하고 세그먼트가 겹치는지 확인하려면 다음 쿼리를 사용하여 자리 표시자를 스키마, 테이블 및 열 이름으로 대체합니다.
SELECT OBJECT_SCHEMA_NAME(o.object_id) AS schema_name,
o.name AS table_name,
cols.name AS column_name,
pnp.index_id,
cls.row_count,
pnp.data_compression_desc,
cls.segment_id,
cls.column_id,
cls.min_data_id,
cls.max_data_id
FROM sys.partitions AS pnp
INNER JOIN sys.tables AS t
ON pnp.object_id = t.object_id
INNER JOIN sys.objects AS o
ON t.object_id = o.object_id
INNER JOIN sys.column_store_segments AS cls
ON pnp.partition_id = cls.partition_id
INNER JOIN sys.columns AS cols
ON o.object_id = cols.object_id
AND
cls.column_id = cols.column_id
WHERE OBJECT_SCHEMA_NAME(o.object_id) = '<Schema Name>'
AND
o.name = '<Table Name>'
AND
cols.name = '<Column Name>'
ORDER BY o.name, pnp.index_id, cls.min_data_id;
예를 들어 완전히 정렬된 columnstore 인덱스에 대한 이 쿼리의 출력은 다음과 같이 표시될 수 있습니다.
min_data_id 및 max_data_id 열은 서로 다른 세그먼트 간에 겹치지 않습니다.
schema_name table_name column_name index_id row_count data_compression_desc segment_id column_id min_data_id max_data_id
----------- ---------- ----------- -------- --------- --------------------- ---------- --------- ----------- -----------
dbo Table1 Column1 1 479779 COLUMNSTORE 0 1 -17 1469515
dbo Table1 Column1 1 887658 COLUMNSTORE 1 1 1469516 2188146
dbo Table1 Column1 1 930144 COLUMNSTORE 2 1 2188147 11072928
참고
순서가 지정된 columnstore 인덱스에서 동일한 DML 일괄 처리 또는 데이터 로드 작업으로 인한 새 데이터는 해당 일괄 처리 내에서만 정렬됩니다. 테이블에 기존 데이터를 포함하는 전역 정렬은 없습니다.
새 데이터를 삽입하거나 기존 데이터를 업데이트한 후 인덱스 데이터를 정렬하려면 인덱스 다시 작성합니다.
분할된 columnstore 인덱스의 오프라인 다시 작성의 경우 한 번에 하나의 파티션을 다시 빌드합니다. 다시 작성 중인 파티션의 데이터는 해당 파티션에 대해 다시 작성이 완료될 때까지 사용할 수 없습니다.
데이터는 온라인 다시 빌드 중에 계속 사용할 수 있습니다. 자세한 내용은 온라인인덱스 작업 수행을 참조하세요.
쿼리 성능
정렬된 columnstore 인덱스의 성능 향상은 쿼리 패턴, 데이터 크기, 데이터 정렬 정도, 세그먼트의 물리적 구조 및 쿼리 실행에 사용할 수 있는 컴퓨팅 리소스에 따라 달라집니다.
다음 패턴을 사용하는 쿼리는 일반적으로 순서가 지정된 columnstore 인덱스를 사용하여 더 빠르게 실행됩니다.
- 같음, 같지 않음 또는 범위 조건자가 있는 쿼리입니다.
- 조건자 열과 정렬된 CCI 열이 동일한 쿼리입니다.
이 예제에서 테이블 T1은 Col_C, Col_B, Col_A의 순서로 정렬된 클러스터형 columnstore 인덱스를 가지고 있습니다.
CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON T1
ORDER (Col_C, Col_B, Col_A);
쿼리 1과 2의 성능은 모든 순서가 지정된 열을 참조하기 때문에 쿼리 3과 4보다 순서가 지정된 columnstore 인덱스를 활용할 수 있습니다.
-- query 1
SELECT *
FROM T1
WHERE Col_C = 'c' AND Col_B = 'b' AND Col_A = 'a';
-- query 2
SELECT *
FROM T1
WHERE Col_B = 'b' AND Col_C = 'c' AND Col_A = 'a';
-- query 3
SELECT *
FROM T1
WHERE Col_B = 'b' AND Col_A = 'a';
-- query 4
SELECT *
FROM T1
WHERE Col_A = 'a' AND Col_C = 'c';
데이터 로드 성능
정렬된 columnstore 인덱스가 있는 테이블에 데이터 로드 성능은 분할된 테이블과 유사합니다. 데이터 정렬 작업으로 인해 정렬되지 않은 columnstore 인덱스보다 데이터를 로드하는 데 시간이 오래 걸릴 수 있지만 나중에 쿼리가 더 빠르게 실행될 수 있습니다.
세그먼트 겹침 줄이기
겹치는 세그먼트의 수는 정렬할 데이터의 크기, 사용 가능한 메모리 및 정렬된 columnstore 인덱스 빌드 중 최대 병렬 처리 수준(MAXDOP) 설정에 따라 달라집니다. 다음 전략은 세그먼트 겹침을 줄이되 인덱스 빌드 프로세스가 더 오래 걸릴 수 있습니다.
- 온라인 인덱스 빌드를 사용할 수 있는 경우 순서가 지정된 클러스터형 columnstore 인덱스 만들 때
ONLINE = ON및MAXDOP = 1옵션을 모두 사용합니다. 이렇게 하면 완전히 정렬된 인덱스가 만들어집니다. - 온라인 인덱스 빌드를 사용할 수 없는 경우
MAXDOP = 1옵션을 사용합니다. - 로드하기 전에 정렬 키를 기준으로 데이터를 미리 정렬합니다.
MAXDOP이 1보다 클 경우, 정렬된 columnstore 인덱스를 빌드하기 위해 사용되는 각 스레드는 데이터의 하위 집합에서 작동하며 로컬에서 정렬합니다. 서로 다른 스레드에 의해 정렬된 데이터에서는 전체 정렬이 없습니다. 병렬 스레드를 사용하면 인덱스를 만드는 시간을 줄일 수 있지만 단일 스레드를 사용하는 경우보다 더 많은 겹치는 세그먼트가 생성됩니다. 단일 스레드 작업을 사용하면 최고의 압축 품질을 제공합니다.
CREATE INDEX 명령을 사용하여 MAXDOP 지정할 수 있습니다.
예제
정렬된 열과 서수 정렬을 확인하십시오.
SELECT object_name(c.object_id) AS table_name,
c.name AS column_name,
i.column_store_order_ordinal
FROM sys.index_columns AS i
INNER JOIN sys.columns AS c
ON i.object_id = c.object_id
AND
c.column_id = i.column_id
WHERE column_store_order_ordinal <> 0;
순서가 지정된 columnstore 인덱스 만들기
클러스터형 순서가 지정된 columnstore 인덱스:
CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1, Column2);
비클러스터형 순서형 컬럼스토어 인덱스:
CREATE NONCLUSTERED COLUMNSTORE INDEX ONCCI
ON dbo.Table1
(
Column1, Column2, Column3
)
ORDER (Column1, Column2);
정렬된 열 추가 또는 제거 및 기존 정렬된 columnstore 인덱스 재구성
클러스터형 순서가 지정된 columnstore 인덱스:
CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1, Column2)
WITH (DROP_EXISTING = ON);
비클러스터형 정렬 columnstore 인덱스:
CREATE NONCLUSTERED COLUMNSTORE INDEX ONCCI
ON dbo.Table1
(
Column1, Column2, Column3
)
ORDER (Column1, Column2)
WITH (DROP_EXISTING = ON);
힙 테이블에서 전체 정렬을 통해 온라인으로 순서가 지정된 클러스터형 columnstore 인덱스를 만들기
CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1)
WITH (ONLINE = ON, MAXDOP = 1);
온라인으로 전체 정렬을 사용하여 순서가 있는 클러스터형 columnstore 인덱스를 재구성
CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1)
WITH (DROP_EXISTING = ON, ONLINE = ON, MAXDOP = 1);