columnstore 인덱스

  • 아티클

xVelocity 메모리 최적화 columnstore 인덱스는 각 열에 대한 데이터를 그룹화하여 저장한 후 모든 열을 조인하여 전체 인덱스를 완료합니다. 이에 반해 기존 인덱스는 각 행에 대한 데이터를 그룹화하여 저장한 후 모든 행을 조인하여 전체 인덱스를 완료합니다. 일부 유형의 쿼리에 대해 SQL Server 쿼리 프로세서는 Columnstore 레이아웃을 사용하여 쿼리 실행 시간을 크게 단축할 수 있습니다. 폭발적으로 증가하는 데이터 웨어하우징, 의사 결정 지원 및 BI 응용 프로그램에서는 매우 많은 데이터 집합을 읽은 후 빠르고 정확하게 처리하여 유용한 정보를 제공해야 합니다. 이러한 증가하는 데이터 볼륨과 높아지는 기대치로 인해 성능을 높은 수준으로 유지하거나 지속적으로 향상시켜야 하는 어려움이 있습니다. SQL Server Columnstore 인덱스 기술은 일반 데이터 웨어하우징 데이터 집합에 특히 적합합니다. Columnstore 인덱스는 필터링, 집계, 그룹화, 스타 조인 쿼리 등 일반 데이터 웨어하우징 쿼리에 대한 성능을 개선하여 사용자의 데이터 웨어하우징 환경을 바꿀 수 있습니다.

목차

기본 사항

  • 기본 사항: Columnstore 인덱스 소개

  • 기본 사항: Columnstore 인덱스 제한 사항

  • 데모 예: 분할된 테이블이 있는 Columnstore 인덱스

  • 기본 사항: 일반적인 Columnstore 인덱스 시나리오

  • 기본 사항: 비트맵 필터 최적화

최선의 구현 방법

  • 최선의 구현 방법: Columnstore 인덱스에서 데이터 업데이트

  • 최선의 구현 방법: Columnstore 인덱스에 대한 열 선택

  • 최선의 구현 방법: 분할된 테이블

방법

  • 방법: Columnstore 인덱스 만들기

  • 방법: Columnstore 인덱스 크기 확인

  • 방법: Columnstore 인덱스의 성능 문제 해결

기본 사항: Columnstore 인덱스 소개

SQL Server 데이터베이스 엔진의 Columnstore 인덱스를 사용하면 일반 데이터 웨어하우징 쿼리에 대한 처리 시간을 크게 단축할 수 있습니다. 일반 데이터 웨어하우징 작업은 많은 양의 데이터를 요약하는 과정을 포함합니다. 성능 향상을 위해 데이터 웨어하우징 및 의사 결정 지원 시스템에 주로 사용되는 기술은 사전 계산 요약 테이블, 인덱싱된 뷰, OLAP 큐브 등입니다. 이러한 기술은 쿼리 처리 속도를 크게 향상시키지만 고정적이고 유지 관리하기 어려울 수 있으며, 각 쿼리 문제에 대해 특별히 디자인해야 합니다.

예를 들어 dk1 및 dk2라는 차원 키 열이 있는 팩트 테이블 f1이 있다고 가정해 보겠습니다. M을 집계 함수(예: SUM)로 사용할 경우 M(dk1)을 참조하는 쿼리를 실행할 때마다 dk1 열에 대해 M을 계산하는 대신 요약 테이블 F2(dk1, M)를 만들어 사용하면 결과를 미리 계산하고 쿼리를 더 빠르게 실행할 수 있습니다. 그러나 M(dk2)을 참조하는 새 쿼리가 필요한 경우 이 정보를 포함하는 새 요약 테이블 F3(dk2, M)을 만들어야 합니다. 테이블의 열 수가 늘어나서 가능한 함수가 많아질수록 이 방법은 유지 관리하는 데 어려울 뿐만 아니라 필요한 쿼리가 모두 포함되지 않고 일부가 누락될 수 있습니다.

사용자에게는 이 오버헤드가 부담이 될 수 있습니다. SQL Server Columnstore 인덱스를 사용하면 다른 솔루션의 오버헤드를 줄일 수 있습니다. 또한 Columnstore 인덱스를 사용하면 결과를 빠르게 계산할 수 있으므로 사전 계산이 필요하지 않습니다.

SQL Server Columnstore 기술의 주요 특징은 다음과 같습니다.

  • 열 데이터 형식 – 기존의 행 기반 데이터 구조(rowstore 형식)와 달리 열 형식 데이터베이스 시스템(예: Columnstore 인덱스가 포함된 SQL Server)에서는 데이터를 그룹화하여 한 열에 하나씩 저장합니다. SQL Server 쿼리 처리에 새로운 데이터 레이아웃을 사용할 수 있으며 쿼리 실행 시간이 크게 단축됩니다.

  • 빠른 쿼리 결과 - Columnstore 인덱스는 다음과 같은 이유로 빠른 결과 생성할 수 있습니다.

    • 필요한 열만 읽습니다. 따라서 디스크에서 메모리로 적은 양의 데이터를 읽은 다음 메모리에서 프로세서 캐시로 이동합니다.

    • 열의 압축률이 높습니다. 그러면 읽은 다음 이동해야 하는 바이트 수가 줄어듭니다.

    • 대부분의 쿼리가 테이블의 일부 열만 쿼리합니다. 따라서 많은 열을 메모리로 가져오지 않습니다. 우수한 압축률과 이 기능이 결합되어 버퍼 풀 사용률이 향상되고 총 I/O가 감소됩니다.

    • 고급 쿼리 실행 기술에서는 열의 청크를 효율적인 방법으로 처리(일괄 처리)하여 CPU 사용을 줄입니다.

  • 키 열 – Columnstore 인덱스에는 키 열의 개념이 없으므로 인덱스의 키 열 개수 제한(16)이 Columnstore 인덱스에는 적용되지 않습니다.

  • 클러스터형 인덱스 키 – 기본 테이블이 클러스터형 인덱스인 경우 클러스터링 키의 모든 열이 비클러스터형 Columnstore 인덱스에 있어야 합니다. 클러스터링 키의 열이 create index 문에 나열되지 않는 경우 Columnstore 인덱스에 자동으로 추가됩니다.

  • 분할 - Columnstore 인덱스는 테이블 분할에 사용됩니다. 테이블 분할 구문을 변경할 필요가 없습니다. 분할된 테이블의 Columnstore 인덱스는 기본 테이블을 기준으로 파티션 정렬됩니다. 따라서 분할 열이 Columnstore 인덱스에 포함된 열 중 하나인 경우 비클러스터형 Columnstore 인덱스는 분할된 테이블에서만 만들 수 있습니다.

  • 레코드 크기 – Columnstore 인덱스에는 인덱스 키 레코드 크기 제한(900바이트)이 적용되지 않습니다.

  • 쿼리 처리 – SQL Server에서는 Columnstore 인덱스와 함께 열 방향 데이터를 활용하는 일괄 처리 기능을 도입했습니다. Columnstore 구조와 일괄 처리 기능은 모두 성능 향상에 기여하지만 두 기능을 모두 사용하면 하나만 사용할 때에 비해 성능 문제를 조사하는 것이 더 복잡할 수 있습니다.

  • 테이블을 업데이트할 수 없음 – SQL Server 2012에서는 Columnstore 인덱스가 포함된 테이블을 업데이트할 수 없습니다. 해결 방법은 최선의 구현 방법: Columnstore 인덱스에서 데이터 업데이트를 참조하십시오.

Columnstore 인덱스를 만드는 방법을 보여주는 구문은 CREATE COLUMNSTORE INDEX(Transact-SQL)를 참조하십시오.

데이터 형식

일반적인 비즈니스 데이터 형식을 columnstore 인덱스에 포함할 수 있습니다. columnstore 인덱스에 포함할 수 있는 데이터 형식은 다음과 같습니다.

  • char 및 varchar

  • nchar 및 nvarchar(varchar(max) 및 nvarchar(max) 제외)

  • decimal 및 numeric(전체 자릿수가 18자리를 초과하는 경우 제외)

  • int, bigint, smallint 및 tinyint

  • float 및 real

  • bit

  • money 및 smallmoney

  • 모든 날짜 및 시간 데이터 형식(소수 자릿수가 2자리를 초과하는 datetimeoffset 제외)

다음 데이터 형식은 Columnstore 인덱스에 포함할 수 없습니다.

  • binary 및 varbinary

  • ntext, text 및 image

  • varchar(max) 및 nvarchar(max)

  • uniqueidentifier

  • rowversion 및 timestamp

  • sql_variant

  • 전체 자릿수가 18자리를 초과하는 decimal 및 numeric

  • 소수 자릿수가 2자리를 초과하는 datetimeoffset

  • CLR 유형(hierarchyid 및 공간 형식)

  • xml

성능 저하 가능성

대형 테이블에서 Columnstore 인덱스를 사용하면 의사 결정 지원 쿼리 성능이 향상되는 경우도 있지만, 일부 쿼리와 전체 작업 성능이 저하될 수 있습니다. 쿼리 최적화 프로그램은 비용에 기반을 둔 방법을 사용하여 일반적으로 쿼리의 전체 성능이 향상되는 경우에만 Columnstore 인덱스를 사용하도록 결정합니다. 그러나 최적화 프로그램에 사용되는 비용 모델은 대략적으로 계산되며 행 저장소(B-트리 또는 힙)를 사용하여 테이블에 액세스하는 것이 더 나은 테이블에 대해 Columnstore 인덱스가 사용되는 경우도 있습니다. 이 경우 IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX 쿼리 힌트를 사용하거나 인덱스 힌트를 사용하여 최적화 프로그램을 행 저장소 인덱스로 전송합니다. 최적화 프로그램은 Columnstore 인덱스의 일부 정보를 여전히 포함할 수 있습니다. 따라서 드물지만 이 옵션으로 성능 문제를 해결할 수 없는 경우도 있습니다. Columnstore 인덱스가 작업 성능에 도움이 되지 않고 인덱스 힌트를 사용하여 문제를 해결할 수 없는 경우 Columnstore 인덱스를 삭제하여 행 저장소 처리로 되돌립니다.

문제 영역

SQL Server Columnstore 인덱스 및 열 기반 쿼리 처리는 대형 팩트 테이블과 별모양 스키마 구성에 조인된 다음 그룹화 및 집계되는 중소형 테이블이 포함된 일반 데이터 웨어하우징 쿼리에 최적화되었습니다. 일반적으로 이러한 쿼리에서는 데이터를 집계하기 때문에 팩트 테이블에 행의 수가 많더라도 상대적으로 적은 수의 결과 집합을 반환합니다. 다음 조건 중 하나 이상에 해당할 경우 Columnstore 인덱스를 사용하는 쿼리의 성능이 저하될 수 있습니다.

  • 데이터가 집계되지 않아서 결과 집합이 큰 경우. 큰 결과 집합을 반환하는 경우에는 작은 결과 집합을 반환할 때보다 기본적으로 더 느려집니다.

  • 조인, 필터링 또는 집계가 포함되지 않는 경우. 이 경우 일괄 처리가 수행되지 않으므로, Columnstore 인덱스를 사용해도 압축 및 적은 수의 열 읽기 이외의 이점이 없습니다.

  • 메모리를 초과하는 큰 해시 테이블을 만들어 디스크에 분산시키는 방식으로 두 개의 큰 테이블을 조인해야 하는 경우

  • 많은 열이 반환되어 많은 Columnstore 인덱스를 검색해야 하는 경우

  • Columnstore 인덱싱된 테이블에 대한 조인 조건에 두 개 이상의 열이 포함되는 경우

이러한 원인 중 하나로 인해 Columnstore 처리 성능이 저하될 경우 이 섹션의 앞부분에서 설명한 방법을 사용하여 문제를 해결할 수 있습니다.

맨 위로 이동

기본 사항: Columnstore 인덱스 제한 사항

기본 제한 사항

columnstore 인덱스:

  • 최대 1,024개의 열만 사용할 수 있습니다.

  • 클러스터형일 수 없습니다. 비클러스터형 columnstore 인덱스만 사용할 수 있습니다.

  • 고유 인덱스일 수 없습니다.

  • 뷰 또는 인덱싱된 뷰에서는 만들 수 없습니다.

  • 스파스 열을 포함할 수 없습니다.

  • 기본 키 또는 외래 키로 사용할 수 없습니다.

  • ALTER INDEX 문을 사용하여 변경할 수 없습니다. 대신 columnstore 인덱스를 삭제하고 다시 만듭니다. ALTER INDEX를 사용하여 columnstore 인덱스를 해제하고 다시 만들 수 있습니다.

  • INCLUDE 키워드로 만들 수 없습니다.

  • 인덱스를 정렬하기 위해 ASC 또는 DESC 키워드를 포함할 수 없습니다. columnstore 인덱스는 압축 알고리즘에 따라 정렬됩니다. 정렬은 인덱스에서 허용되지 않습니다. columnstore 인덱스에서 선택한 값은 검색 알고리즘에 의해 정렬될 수도 있지만 결과 집합이 정렬되도록 보장하려면 ORDER BY 절을 사용해야 합니다.

  • 기존 인덱스 방식에서는 통계를 사용하거나 보관하지 마십시오.

  • FILESTREAM 특성이 있는 열은 포함할 수 없습니다. 테이블에서 인덱스에 사용되지 않는 다른 열에는 FILESTREAM 특성이 포함될 수 있습니다.

Columnstore 인덱스가 포함된 테이블을 업데이트할 수 없습니다.

이 문제를 해결하려면 최선의 구현 방법: Columnstore 인덱스에서 데이터 업데이트를 참조하십시오.

제한된 메모리의 효과

Columnstore 처리는 메모리 내 처리에 맞게 최적화됩니다. SQL Server에서는 메모리가 부족할 때 데이터와 대부분의 데이터 구조를 디스크에 분산하는 메커니즘을 구현합니다. 심각한 메모리 제한이 있으면 행 저장소가 처리에 사용됩니다. Columnstore 인덱스를 액세스 방법으로 선택했지만 메모리가 부족하여 필요한 데이터 구조를 작성할 수 없는 경우가 있습니다. Columnstore 작업으로 시작한 다음 느린 코드 경로를 기본값으로 지정하면 쿼리에서 심각한 메모리 제한이 발생할 경우 성능이 저하될 수 있습니다. 쿼리에 대한 효과적인 메모리 요구 사항은 쿼리에 따라 달라집니다. Columnstore 인덱스를 작성하려면 약 8MB x 인덱스의 열 수 x DOP(병렬 처리 수준)에 해당하는 메모리가 필요합니다. 일반적으로 문자열인 열의 비율이 높아질수록 메모리 요구 사항도 커집니다. 따라서 DOP를 줄이면 Columnstore 인덱스 구축을 위한 메모리 요구 사항을 줄일 수 있습니다.

일부 식이 다른 식보다 더 빠르게 계산됩니다.

Columnstore 인덱스를 사용할 때 일부 일반 식은 한 번에 한 행씩 계산하지 않고 일괄 처리 모드로 계산됩니다. 일괄 처리 모드에서는 Columnstore 인덱스를 사용하는 이점은 물론이고 추가적으로 쿼리 가속화 기능을 제공합니다. 일괄 처리 모드에서는 일부 쿼리 실행 연산자를 사용할 수 없습니다.

Columnstore 인덱스는 SEEK를 지원하지 않습니다.

쿼리에서 행의 일부만 반환할 경우 최적화 프로그램에서 Columnstore 인덱스를 선택할 가능성이 적습니다(예: 바늘 건초 유형 쿼리). FORCESEEK 테이블 힌트를 사용하는 경우 최적화 프로그램에서는 Columnstore 인덱스를 고려하지 않습니다.

columnstore 인덱스는 다음 기능과 함께 사용할 수 없습니다.

  • 페이지 및 행 압축과 vardecimal 저장소 형식(columnstore 인덱스가 이미 다른 형식으로 압축되어 있음)

  • 복제

  • 변경 내용 추적

  • 변경 데이터 캡처

  • Filestream

맨 위로 이동

데모 예: 분할된 테이블이 있는 Columnstore 인덱스

이 항목의 예에서는 AdventureWorksDW2012 예제 데이터베이스에서 만든 FactResellerSalesPtnd라는 분할된 테이블을 사용합니다. 분할된 테이블에서 Columnstore 인덱스를 테스트하려면 AdventureWorksDW2012 데이터베이스에 연결하고 다음 코드를 실행하여 팩트 테이블의 분할된 버전을 만듭니다.

[!참고]

예제 데이터베이스에 대한 자세한 내용과 데이터베이스를 다운로드하는 방법은 AdventureWorks 예제 데이터베이스를 참조하십시오.

FactResellerSalesPtnd 테이블 만들기

  • 다음 코드를 실행하여 FactResellerSalesPtnd라는 FactResellerSales 테이블의 분할된 버전을 만듭니다.

    USE AdventureWorksDW2012;
GO

CREATE PARTITION FUNCTION [ByOrderDateMonthPF](int) AS RANGE RIGHT 
FOR VALUES (
    20050701, 20050801, 20050901, 20051001, 20051101, 20051201, 
    20060101, 20060201, 20060301, 20060401, 20060501, 20060601, 
    20060701, 20060801, 20060901, 20061001, 20061101, 20061201, 
    20070101, 20070201, 20070301, 20070401, 20070501, 20070601, 
    20070701, 20070801, 20070901, 20071001, 20071101, 20071201, 
    20080101, 20080201, 20080301, 20080401, 20080501, 20080601, 
    20080701, 20080801, 20080901, 20081001, 20081101, 20081201
) 
GO

CREATE PARTITION SCHEME [ByOrderDateMonthRange] 
AS PARTITION [ByOrderDateMonthPF] 
ALL TO ([PRIMARY]) 
GO

-- Create a partitioned version of the FactResellerSales table
CREATE TABLE [dbo].[FactResellerSalesPtnd]( 
    [ProductKey] [int] NOT NULL, 
    [OrderDateKey] [int] NOT NULL, 
    [DueDateKey] [int] NOT NULL, 
    [ShipDateKey] [int] NOT NULL, 
    [CustomerKey] [int] NOT NULL, 
    [EmployeeKey] [int] NOT NULL, 
    [PromotionKey] [int] NOT NULL, 
    [CurrencyKey] [int] NOT NULL, 
    [SalesTerritoryKey] [int] NOT NULL, 
    [SalesOrderNumber] [nvarchar](20) NOT NULL, 
    [SalesOrderLineNumber] [tinyint] NOT NULL, 
    [RevisionNumber] [tinyint] NULL, 
    [OrderQuantity] [smallint] NULL, 
    [UnitPrice] [money] NULL, 
    [ExtendedAmount] [money] NULL, 
    [UnitPriceDiscountPct] [float] NULL, 
    [DiscountAmount] [float] NULL, 
    [ProductStandardCost] [money] NULL, 
    [TotalProductCost] [money] NULL, 
    [SalesAmount] [money] NULL, 
    [TaxAmt] [money] NULL, 
    [Freight] [money] NULL, 
    [CarrierTrackingNumber] [nvarchar](25) NULL, 
    [CustomerPONumber] [nvarchar](25) NULL,
    OrderDate [datetime] NULL,
    DueDate [datetime] NULL,
    ShipDate [datetime] NULL
) ON ByOrderDateMonthRange(OrderDateKey);
GO

-- Using simple or bulk logged recovery mode, and then the TABLOCK 
-- hint on the target table of the INSERT…SELECT is a best practice
-- because it causes minimal logging and is therefore much faster.
ALTER DATABASE AdventureWorksDW2012 SET RECOVERY SIMPLE;
GO

-- Copy the data from the FactResellerSales into the new table
INSERT INTO dbo.FactResellerSalesPtnd WITH(TABLOCK)
SELECT * FROM dbo.FactResellerSales;
GO

-- Create the columnstore index
CREATE NONCLUSTERED COLUMNSTORE INDEX [csindx_FactResellerSalesPtnd]
ON [FactResellerSalesPtnd]
( 
    [ProductKey], 
    [OrderDateKey], 
    [DueDateKey], 
    [ShipDateKey], 
    [CustomerKey], 
    [EmployeeKey], 
    [PromotionKey], 
    [CurrencyKey], 
    [SalesTerritoryKey], 
    [SalesOrderNumber], 
    [SalesOrderLineNumber], 
    [RevisionNumber], 
    [OrderQuantity], 
    [UnitPrice], 
    [ExtendedAmount], 
    [UnitPriceDiscountPct], 
    [DiscountAmount], 
    [ProductStandardCost], 
    [TotalProductCost], 
    [SalesAmount], 
    [TaxAmt], 
    [Freight], 
    [CarrierTrackingNumber], 
    [CustomerPONumber], 
    [OrderDate],
    [DueDate],
    [ShipDate]
);

이제 Columnstore 인덱스를 활용할 수 있는 쿼리를 실행하여 Columnstore 인덱스가 사용되는지를 확인합니다.

Columnstore 인덱스 테스트

  1. Ctrl+M을 누르거나 쿼리 메뉴에서 실제 실행 계획 포함을 선택합니다. 그러면 SQL Server Management Studio에서 사용하는 실제 실행 계획이 그래픽으로 표현됩니다.

  2. 쿼리 편집기 창에서 다음 쿼리를 실행합니다.

    SELECT SalesTerritoryKey, SUM(ExtendedAmount) AS SalesByTerritory
FROM FactResellerSalesPtnd
GROUP BY SalesTerritoryKey;

    결과 창의 실행 계획 탭에서 비클러스터형 csindx_FactResellerSalesPtnd 인덱스의 인덱스 검색을 쿼리 계획에서 선택했는지 확인합니다.

    [!참고]

    그래픽 실행 계획 아이콘에 대한 자세한 내용은 실행 계획 논리 및 물리 연산자 참조을 참조하십시오.

    맨 위로 이동

기본 사항: 일반적인 Columnstore 인덱스 시나리오

별모양 및 눈송이 데이터베이스 스키마는 일반적으로 데이터 조작의 효율성보다 데이터 검색 속도가 더 중요한 차원 데이터 웨어하우스 및 데이터 마트에 있습니다. SQL Server 2012의 Columnstore 기술은 별모양 및 눈송이 스키마를 대상으로 하는 쿼리를 검색하여 빠르게 처리할 수 있습니다.

예:

[!참고]

다음 예에서는 테이블이 충분히 크지 않기 때문에 일괄 처리를 사용할 수 없습니다. 병렬 처리와 같은 일괄 처리 실행 모드는 비용이 많이 드는 쿼리에만 사용됩니다.

A: 두 테이블을 조인하는 집계 쿼리

  • 분기별로 판매한 215 제품의 수를 계산하는 스타 조인 쿼리가 있다고 가정해 보겠습니다. 이때, FactResellerSalesPtnd라는 팩트 테이블이 OrderDateKey 열에서 분할됩니다. DimDate라는 차원 테이블 중 하나가 날짜 키에서 기본 키 - 외래 키 관계를 통해 팩트 테이블에 연결됩니다.

    SELECT d.CalendarYear,
    d.CalendarQuarter,
    COUNT(*) AS NumberSold
FROM dbo.FactResellerSalesPtnd AS f
    JOIN dbo.DimDate AS d
    ON f.OrderDateKey = d.DateKey
WHERE ProductKey = 215
GROUP BY d.CalendarYear, d.CalendarQuarter
ORDER BY d.CalendarYear, d.CalendarQuarter;

또한 단일 테이블만 대상으로 할 수 있는 쿼리가 있습니다. 이 경우 SQL Server에서는 일괄 처리 실행 및 Columnstore 기술을 활용하여 쿼리 실행 속도도 높이려고 시도합니다.

B: 단일 테이블에 대한 단순 집계 쿼리

  • 팩트 테이블은 FactResellerSalesPtnd이며 이 테이블은 OrderDateKey 열에서 분할됩니다. 다음은 행의 수와 주문 수를 반환하는 쿼리입니다.

    SELECT COUNT(*) AS NumberOfRows, 
    COUNT(DISTINCT(f.SalesOrderNumber)) AS NumberOfOrders
FROM dbo.FactResellerSalesPtnd AS f;

    일반 데이터 웨어하우징 시나리오 쿼리의 경우 쿼리 실행 중에 Columnstore 인덱스와 일괄 처리 실행 모드를 사용하면 일반적으로 속도가 1.5-10배 빨라집니다. 일부 스타 조인 쿼리의 경우 속도가 훨씬 더 빨라집니다.

기본 사항: 비트맵 필터 최적화

열 형식 데이터 레이아웃 이외에 SQL Server에서는 쿼리 실행 중에 성능 향상을 위해 스토리지 엔진에 전달되는 비트맵 필터를 사용합니다. 비트맵 필터는 조인이 구현되기 이전에 포함되는 행의 수를 줄여서 조인 연산에서 처리되는 행의 수를 줄임으로써 쿼리 실행 속도를 높여줍니다. 비트맵은 해시 조인을 작성할 때 만들어지지만 실제 비트맵 검사는 해시 조인을 검색할 때 수행됩니다. 그래픽 실행 계획 또는 xml 실행 계획을 사용하여 비트맵 필터 사용을 확인할 수 있습니다.

최선의 구현 방법: Columnstore 인덱스에서 데이터 업데이트

Columnstore 인덱스가 포함된 테이블은 업데이트할 수 없습니다. 세 가지 방법으로 이 문제를 해결할 수 있습니다.

  • Columnstore 인덱스로 테이블을 업데이트하려면 Columnstore 인덱스를 삭제하고 필요한 INSERT, DELETE, UPDATE 또는 MERGE 연산을 수행한 다음 Columnstore 인덱스를 다시 작성합니다.

  • 테이블 및 스위치 파티션을 분할합니다. 대량 삽입의 경우 준비 테이블에 데이터를 삽입하고 준비 테이블에서 Columnstore 인덱스를 작성한 다음 준비 테이블을 빈 파티션으로 전환합니다. 다른 업데이트의 경우 주 테이블의 파티션을 준비 테이블로 전환하고 준비 테이블에서 Columnstore 인덱스를 해제 또는 삭제하고 업데이트 작업을 수행하고 준비 테이블에서 Columnstore 인덱스를 다시 작성하거나 다시 만든 다음 준비 테이블을 주 테이블로 다시 전환합니다.

  • 정적 데이터를 Columnstore 인덱스가 포함된 주 테이블에 배치하고 Columnstore 인덱스를 포함하지 않는 동일한 스키마를 사용하여 새 데이터와 변경할 최신 데이터를 개별 테이블에 넣습니다. 최신 데이터로 테이블에 업데이트를 적용합니다. 데이터를 쿼리하려면 쿼리를 각 테이블에 하나씩 두 개의 쿼리로 다시 작성한 다음 UNION ALL을 사용하여 두 결과 집합을 결합합니다. 큰 기본 테이블에 대한 하위 쿼리에서 Columnstore 인덱스를 활용할 수 있습니다. 업데이트 가능한 테이블이 매우 작은 경우 Columnstore 인덱스 부족이 성능에 미치는 영향은 미미합니다. 두 테이블의 UNION ALL 뷰를 쿼리할 수도 있지만 이 경우 성능상의 이점이 크지 않을 수 있습니다. 성능은 쿼리 계획에 따라 다르며, 쿼리 계획은 쿼리, 데이터 및 카디널리티 예상치에 따라 달라집니다. 뷰를 사용하면 뷰의 INSTEAD OF 트리거가 업데이트를 Columnstore 인덱스가 포함되지 않은 테이블에 전달하고 사용자와 응용 프로그램이 뷰 메커니즘을 인식하지 못한다는 이점이 있습니다. 이러한 방법 중 하나를 UNION ALL과 함께 사용하는 경우 일반 쿼리에 대한 성능을 테스트하여 이 방법을 사용할 때의 편리함이 성능 손실보다 가치가 있는지 여부를 결정하십시오.

[!참고]

테이블을 읽기 전용으로 만드는 경우 Columnstore 인덱스를 메커니즘으로 만들지 마십시오. Columnstore 인덱스를 포함하는 테이블에 대한 업데이트 제한이 이후 릴리스에서는 변경될 수 있습니다. 읽기 전용 동작이 필요한 경우 읽기 전용 파일 그룹을 만든 후 테이블을 해당 파일 그룹으로 이동하여 동작을 적용해야 합니다.

최선의 구현 방법: Columnstore 인덱스에 대한 열 선택

Columnstore 인덱스의 성능상 이점 중 일부는 쿼리 처리를 위해 읽고 조작해야 하는 데이터 페이지 수를 줄여주는 압축 기술에서 얻을 수 있습니다. 압축은 많은 양의 중복 값이 있는 문자 또는 숫자 열에 가장 적합합니다. 예를 들어 차원 테이블에 우편 번호, 구/군/시 및 판매 지역에 대한 열이 포함될 수 있습니다. 각 구/군/시에 많은 우편 번호가 있고, 각 판매 지역에 많은 구/군/시가 있는 경우 판매 지역 열이 가장 많이 압축되고 구/군/시 열은 그 다음으로 많이 압축되고, 우편 번호 열이 가장 적게 압축됩니다. 모든 열은 Columnstore 인덱스의 대상이지만 Columnstore 인덱스에 판매 지역 코드 열을 추가할 때 Columnstore 압축률이 극대화되고, 우편 번호를 추가할 때 압축 효과가 가장 적습니다.

맨 위로 이동

최선의 구현 방법: 분할된 테이블

Columnstore 인덱스는 분할이 일반적으로 수행되는 매우 큰 데이터 웨어하우스 시나리오에서 쿼리를 지원하도록 설계되었습니다. 분할은 Columnstore 인덱스가 포함된 테이블의 데이터를 정기적으로 업데이트해야 하는 경우에 사용하는 것이 좋습니다. Columnstore 인덱스의 파티션을 업데이트하는 방법은 이전 섹션의 최선의 구현 방법: Columnstore 인덱스에서 데이터 업데이트를 참조하십시오.

맨 위로 이동

방법: Columnstore 인덱스 만들기

Columnstore 인덱스를 만드는 방법은 다른 인덱스를 만드는 방법과 비슷합니다. Transact-SQL을 사용하거나 SQL Server Management Studio 그래픽 도구를 사용하여 Columnstore 인덱스를 만들 수 있습니다.

Transact-SQL을 사용하여 Columnstore 인덱스 만들기

  • 쿼리 편집기 창에서 CREATE COLUMNSTORE INDEX 문을 실행합니다. 예를 들어 위의 FactResellerSalesPtnd 테이블 만들기를 참조하십시오. 자세한 내용은 CREATE COLUMNSTORE INDEX(Transact-SQL)를 참조하십시오.

SQL Server Management Studio을 사용하여 Columnstore 인덱스 만들기

  1. Management Studio에서 개체 탐색기를 사용하여 SQL Server 데이터베이스 엔진의 인스턴스에 연결합니다.

  2. 개체 탐색기에서 SQL Server 인스턴스 및 데이터베이스를 차례로 확장하고 원하는 데이터베이스와 테이블을 차례로 확장한 다음 테이블을 마우스 오른쪽 단추로 클릭하고 새 인덱스를 가리킨 다음 비클러스터형 Columnstore 인덱스를 클릭합니다.

  3. 인덱스 이름 대화 상자의 일반 탭에서 새 인덱스의 이름을 입력한 다음 추가를 클릭합니다.

  4. 열 선택 대화 상자에서 Columnstore 인덱스에 참여할 열을 선택한 다음 확인을 두 번 클릭하여 인덱스를 만듭니다.

방법: Columnstore 인덱스 크기 확인

Columnstore 인덱스는 세그먼트와 사전으로 구성됩니다. 다음 예에서는 sys.column_store_segments 및 sys.column_store_dictionaries의 on_disk_size 열을 결합하여 FactResellerSalesPtnd 테이블에서 Columnstore 인덱스의 총 크기를 확인하는 방법에 대해 설명합니다.

SELECT SUM(on_disk_size_MB) AS TotalSizeInMB
FROM
(
   (SELECT SUM(css.on_disk_size)/(1024.0*1024.0) on_disk_size_MB
    FROM sys.indexes AS i
    JOIN sys.partitions AS p
        ON i.object_id = p.object_id 
    JOIN sys.column_store_segments AS css
        ON css.hobt_id = p.hobt_id
    WHERE i.object_id = object_id('FactResellerSalesPtnd') 
    AND i.type_desc = 'NONCLUSTERED COLUMNSTORE') 
  UNION ALL
   (SELECT SUM(csd.on_disk_size)/(1024.0*1024.0) on_disk_size_MB
    FROM sys.indexes AS i
    JOIN sys.partitions AS p
        ON i.object_id = p.object_id 
    JOIN sys.column_store_dictionaries AS csd
        ON csd.hobt_id = p.hobt_id
    WHERE i.object_id = object_id('FactResellerSalesPtnd') 
    AND i.type_desc = 'NONCLUSTERED COLUMNSTORE') 
) AS SegmentsPlusDictionary

방법: Columnstore 인덱스의 성능 문제 해결

Columnstore 인덱스가 사용되고 있는지 여부를 확인하려면 쿼리 실행 계획을 검사합니다. 이익을 극대화할 수 있는 세 가지 요소가 있습니다.

  • Columnstore 인덱스가 쿼리 실행 계획에 있어야 합니다.

    Columnstore 인덱스 검색

    Columnstore 인덱스 검색 연산자 아이콘

    Columnstore 인덱스를 사용하고 있지 않지만 Columnstore가 쿼리에 유용하다고 판단되는 경우 WITH (INDEX(<indexname>)) 힌트를 사용하여 Columnstore 인덱스 사용을 강제로 적용하여 쿼리 성능을 평가할 수 있습니다. 다음 예에서는 인덱스 힌트가 포함된 쿼리를 보여 줍니다.

    SELECT d.CalendarYear,
    d.CalendarQuarter,
    COUNT(*) AS NumberSold
FROM dbo.FactResellerSalesPtnd AS f WITH (INDEX(csindx_FactResellerSalesPtnd))
    JOIN dbo.DimDate AS d
    ON f.OrderDateKey = d.DateKey
WHERE ProductKey = 215
GROUP BY d.CalendarYear, d.CalendarQuarter
ORDER BY d.CalendarYear, d.CalendarQuarter;

  • 그래픽 쿼리 계획의 Columnstore 인덱스 아이콘 위로 포인터를 이동하면 실제 실행 모드가 행 대신 일괄 처리로 나열됩니다.

  • 비트맵 물리적 연산자 아이콘이 그래픽 실행 계획에 있습니다. 이는 조인 연산 이전에 비트맵 필터가 적용되어 행의 수를 줄여 준다는 것을 의미합니다.

    Bitmap 연산자 아이콘

    비트맵 연산자 아이콘

맨 위로 이동

관련 태스크

CREATE COLUMNSTORE INDEX(Transact-SQL)

관련 내용

sys.column_store_dictionaries(Transact-SQL)

sys.column_store_segments(Transact-SQL)