데이터 웨어하우징의 열 저장소 인덱스

2025-04-18

적용 대상:SQL Server Azure SQL Database Azure SQL Managed Instance Analytics Platform System (PDW)Microsoft Fabric의 SQL 데이터베이스

Columnstore 인덱스는 분할과 함께 SQL Server 데이터 웨어하우스를 빌드하는 필수 요소입니다. 이 문서에서는 SQL 데이터베이스 엔진 사용하는 데이터 웨어하우징 디자인의 주요 사용 사례 및 예제에 중점을 둡니다.

데이터 웨어하우징의 주요 기능

SQL Server 2016(13.x)에서는 columnstore 성능 향상을 위해 다음과 같은 기능을 도입했습니다.

Always On 가용성 그룹은 읽기 가능한 보조 복제본에서 columnstore 인덱스 쿼리를 지원합니다.
MARS(Multiple Active Result Set)는 columnstore 인덱스를 지원합니다.
새로운 동적 관리 뷰 sys.dm_db_column_store_row_group_physical_stats(Transact-SQL)는 행 그룹 수준에서 성능 문제 해결 정보를 제공합니다.
columnstore 인덱스의 모든 쿼리는 일괄 처리 모드에서 실행할 수 있습니다. 이전에는 병렬 쿼리만 일괄 처리 모드에서 실행할 수 있었습니다.
정렬, 고유 정렬 및 고유 연산자는 일괄 처리 모드에서 실행됩니다.
이제 창 집계는 데이터베이스 호환성 수준 130 이상에 대한 일괄 처리 모드로 실행됩니다.
집계의 효율적인 처리를 위한 집계 푸시다운입니다. 모든 데이터베이스 호환성 수준에서 지원됩니다.
문자열 조건자의 효율적인 처리를 위한 문자열 조건자 푸시다운입니다. 모든 데이터베이스 호환성 수준에서 지원됩니다.
데이터베이스 호환성 수준 130 이상에서의 스냅샷 격리입니다.
정렬된 클러스터형 columnstore 인덱스는 SQL Server 2022(16.x)에서 도입되었습니다. 자세한 내용은 CREATE COLUMNSTORE INDEX 및 순서가 지정된 컬럼스토어 인덱스를 사용한 성능 튜닝를 참조하세요. 순서가 지정된 columnstore 인덱스 가용성에 대해서는 순서가 지정된 columnstore 인덱스 가용성을 참조하세요.

SQL Server 및 Azure SQL 버전 및 플랫폼의 새로운 기능에 대한 자세한 내용은 columnstore 인덱스의 새로운 기능을 참조하세요.

비클러스터형 인덱스와 columnstore 인덱스를 결합하여 성능 향상

SQL Server 2016(13.x)부터 클러스터 집합 열 저장소 인덱스에 행 저장소 비클러스터 인덱스를 만들 수 있습니다.

예: 비클러스터형 인덱스를 사용하여 테이블 검색의 효율성 향상

Data Warehouse에서 테이블 검색의 효율성을 개선하기 위해 테이블 검색에 가장 적합한 쿼리를 실행하도록 설계된 비클러스터형 인덱스를 만들 수 있습니다. 예를 들어 일치하는 값을 찾고 작은 범위의 값을 반환하는 쿼리는 columnstore 인덱스보다 B-트리 인덱스에 대해 성능이 더 좋습니다. columnstore 인덱스의 전체 검색이 필요하지 않으며 B-트리 인덱스로 이진 검색을 수행하여 올바른 결과를 더 빠르게 반환합니다.

--BASIC EXAMPLE: Create a nonclustered index on a columnstore table.

--Create the table
CREATE TABLE t_account (
    AccountKey int NOT NULL,
    AccountDescription nvarchar (50),
    AccountType nvarchar(50),
    UnitSold int
);

--Store the table as a columnstore.  
CREATE CLUSTERED COLUMNSTORE INDEX taccount_cci ON t_account;

--Add a nonclustered index.
CREATE UNIQUE INDEX taccount_nc1 ON t_account (AccountKey);

예: 비클러스터형 인덱스를 사용하여 columnstore 테이블에 기본 키 제약 조건을 적용합니다.

테이블에는 최대 하나의 클러스터형 인덱스가 있을 수 있으므로 클러스터형 columnstore 인덱스가 있는 테이블에는 클러스터형 기본 키 제약 조건이 있을 수 없습니다. columnstore 테이블에 기본 키 제약 조건을 만들려면 비클러스터형으로 선언해야 합니다.

다음 예제에서는 비클러스터형 기본 키 제약 조건이 있는 테이블을 만든 다음 테이블에 클러스터형 columnstore 인덱스가 만들어집니다. columnstore 테이블의 삽입 또는 업데이트도 비클러스터형 인덱스를 수정하므로 기본 키 제약 조건을 위반하는 모든 작업으로 인해 전체 작업이 실패합니다.

--Create a primary key constraint on a columnstore table.

--Create a rowstore table with a nonclustered primary key constraint.
CREATE TABLE t_account (
    AccountKey int NOT NULL,
    AccountDescription nvarchar (50),
    AccountType nvarchar(50),
    UnitSold int,
    CONSTRAINT pk_account PRIMARY KEY NONCLUSTERED (AccountKey)
);

--Convert the table to columnstore.
--The primary key constraint is preserved as a nonclustered index on the columnstore table.
CREATE CLUSTERED COLUMNSTORE INDEX t_account_cci ON t_account;

행 수준 및 행 그룹 수준 잠금을 사용하도록 설정하여 성능 향상

columnstore 인덱스 기능의 비클러스터형 인덱스를 보완하기 위해 SQL Server 2016(13.x)은 SELECT, UPDATE, 및 DELETE 작업에 대한 세분화된 잠금 기능을 제공합니다. 쿼리는 비클러스터형 인덱스를 사용한 인덱스 검색 시 행 수준 잠금을, 컬럼스토어 인덱스를 사용한 전체 테이블 스캔 시 행 그룹 수준 잠금을 통해 실행할 수 있습니다. 행 수준 및 행 그룹 수준 잠금을 적절하게 사용하여 읽기/쓰기 동시성을 높이려면 이 방법을 사용합니다.

--Granular locking example
--Store table t_account as a columnstore table.
CREATE CLUSTERED COLUMNSTORE INDEX taccount_cci ON t_account

--Add a nonclustered index for use with this example
CREATE UNIQUE INDEX taccount_nc1 ON t_account (AccountKey);

--Look at locking with access through the nonclustered index
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRAN
    -- The query plan chooses a seek operation on the nonclustered index
    -- and takes the row lock
    SELECT * 
    FROM t_account 
    WHERE AccountKey = 100;
COMMIT TRAN;

스냅샷 격리 및 읽기 커밋된 스냅샷 격리

SI(스냅샷 격리)를 사용하여 트랜잭션 일관성을 보장하고, columnstore 인덱스 쿼리에 대한 문 수준 일관성을 보장하기 위해 RCSI(커밋된 스냅샷 격리)를 사용합니다. 이렇게 하면 데이터 기록기를 차단하지 않고 쿼리를 실행할 수 있습니다. 이 비차단 동작 덕분에 복잡한 트랜잭션에 대한 교착 상태의 가능성이 크게 줄어듭니다. 자세한 내용은 데이터베이스 엔진의 행 버전 관리 기반 격리 수준을 참조하세요.