컬럼스토어 인덱스 성능 (Columnsotre Index Performance)

컬럼스토어 인덱스 성능 (Columnsotre Index Performance)

 

• Version : SQL Server 2012, 2014, 2016

 

SQL Server 2012부터 도입된 컬럼스토어 인덱스는 열 기반 데이터 저장소 및 열 기반 쿼리 처리를 사용하여 데이터를 저장하고 관리한다. 인덱스는 주로 대량 로드 및 전용 쿼리를 수행하는 데이터웨어하우징 작업에 효과적이다. 전체 테이블 검색을 사용하는 쿼리에서는 뛰어난 성능을 제공하지만 특정 값을 찾아 데이터를 검색하는 쿼리에는 부적합 하다.

 

자세한 내용은 MSDN을 참고한다.

• Columnstore Index 소개

  https://msdn.microsoft.com/ko-kr/library/gg492088(v=sql.120).aspx

 

일반 B-tree 인덱스와 컬럼스토어 인덱스의 성능에 대해서 비교해본다. 아래 스크립트는 테스트를 위한 테이블 생성 및 데이터 생성 스크립트이다. (꽤 오랜 시간동안 테스트 데이터가 생성된다.)

-- Create SampleData table CREATE TABLE [dbo].[SampleData](     [RowKey] [int] NOT NULL,     [CreateDate] [int] NOT NULL,     [OtherDate] [int] NOT NULL,     [VarcharColumn1] [varchar](20) NULL,     [VarcharColumn2] [varchar](20) NULL,     [VarcharColumn3] [varchar](20) NULL,     [VarcharColumn4] [varchar](20) NULL,     [VarcharColumn5] [varchar](20) NULL,     [IntColumn1] int NULL,     [IntColumn2] int NULL,     [IntColumn3] int NULL,     [IntColumn4] int NULL,     [IntColumn5] int NULL,     [IntColumn6] int NULL,     [IntColumn7] int NULL,     [IntColumn8] int NULL,     [IntColumn9] int NULL,     [IntColumn10] int NULL,     [FloatColumn1] float NULL,     [FloatColumn2] float NULL,     [FloatColumn3] float NULL,     [FloatColumn4] float NULL,     [FloatColumn5] float NULL     ) GO   -- Load sample data into table DECLARE @val INT SELECT @val=1 WHILE @val < 5000000 BEGIN INSERT INTO SampleData VALUES (@val, CAST(CONVERT(varchar,DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 365), '2015-01-01'),112) as integer), CAST(CONVERT(varchar,DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 365), '2015-01-01'),112) as integer), 'TEST' + cast(round(rand()*100,0) AS VARCHAR), 'TEST' + cast(round(rand()*100,0) AS VARCHAR), 'TEST' + cast(round(rand()*100,0) AS VARCHAR), 'TEST' + cast(round(rand()*100,0) AS VARCHAR), 'TEST' + cast(round(rand()*100,0) AS VARCHAR), round(rand()*100000,0), round(rand()*100000,0),      round(rand()*100000,0),      round(rand()*100000,0),      round(rand()*100000,0), round(rand()*100000,0), round(rand()*100000,0), round(rand()*100000,0), round(rand()*100000,0), round(rand()*100000,0),      round(rand()*10000,2),      round(rand()*10000,2),      round(rand()*10000,2),      round(rand()*10000,2),      round(rand()*10000,2)) SELECT @val=@val+1 END GO

 

 

데이터 생성이 완료 되었으면 성능 비교를 위해 B-tree 인덱스와 컬럼스토어 인덱스를 생성한다.

-- b-tree index CREATE NONCLUSTERED INDEX IX_SampleData_Reg ON SampleData (VarcharColumn1,FloatColumn1); GO   -- column store index CREATE NONCLUSTERED COLUMNSTORE INDEX IX_SampleData_ColStore ON SampleData (VarcharColumn1,FloatColumn1); GO

 

 

각 인덱스를 사용한 쿼리를 실행하여 성능을 측정한다. 실행 측정 결과는 프로파일러를 사용하였다.

-- column store query SELECT VarcharColumn1,avg(FloatColumn1) FROM SampleData GROUP BY VarcharColumn1 GO   -- b-tree query SELECT VarcharColumn1,avg(FloatColumn1) FROM SampleData GROUP BY VarcharColumn1 OPTION (TABLE HINT(SampleData, INDEX (IX_SampleData_Reg))) GO

 

 

결과를 살펴보면 컬럼스토어 인덱스 성능이 훨씬 낫다는 것을 실험에서 확인할 수 있다.

 

[참고자료]

• Columnstore Index 소개

  https://msdn.microsoft.com/ko-kr/library/gg492088(v=sql.120).aspx

• https://www.mssqltips.com/sqlservertip/4010/sql-server-column-store-index-performance/

 

 

2015-09-14 / 강성욱 / http://sqlmvp.kr

 

SQL Server, MSSQL, 컬럼스토어 인덱스, Columnstore Index, 컬럼스토어 인덱스 성능, 열 저장소, 데이터베이스, 컬럼 기반 인덱스