컬럼스토어 인덱스 성능 (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
2015-09-14 / 강성욱 / http://sqlmvp.kr
SQL Server, MSSQL, 컬럼스토어 인덱스, Columnstore Index, 컬럼스토어 인덱스 성능, 열 저장소, 데이터베이스, 컬럼 기반 인덱스
'SQL Server > SQL Server Tip' 카테고리의 다른 글
| SQL Server 임시 테이블 특성 (0) | 2015.09.30 |
|---|---|
| 트리거를 사용하여 특정 컬럼 업데이트 하기 (After 트리거) (0) | 2015.09.16 |
| In-Memory 최적화 파일 경로 변경 (0) | 2015.09.08 |
| SQL Server 2016 Query Store -실행된 쿼리에 대한 실행 계획 및 통계 정보를 확인하자 (0) | 2015.09.01 |
| SQL Server 2016 동적 데이터 마스킹을 우회하여 원본 데이터 출력하기 (0) | 2015.08.27 |
