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

 

  • Version : SQL Server 2012, 2014, 2016

 

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

 

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

 

일반 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

 

 

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

 

[참고자료]

 

 

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

 

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

SQL Server 2016 Temporal Table – 데이터 변경 내용 추적

 

  • Version : SQL Server 2016

 

SQL Server 2016에서 새롭게 소개된 Temporal Table 대해서 알아본다. 여기서 소개하는 임시테이블은 임시 테이블(Temporal Table)과 임시 테이블(temporary tables)을 착각하지 않도록 주의해야 한다.

 

SQL Server 2016의 Temporal Table은 테이블의 기록을 데이터로 보존할 수 있도록 하는 시스템 테이블의 새로운 이름이다. 일반 테이블은 현재 데이터를 반환 할 수 있지만 시스템 테이블은 업데이트와 삭제된 버전의 데이터를 조회할 수 있다. 만약 데이터를 5에서 10으로 변경하는 경우 일반 테이블을 조회하면 10이라는 값을 검색할 수 있지만 임시테이블(히스토리 테이블)은 변경된 기록을 유지하여 이전 값인 5를 검색할 수 있다. 이 히스토리 테이블은 레코드가 활성화 된 때를 표시하는 시작 및 종료 데이터와 함께 이전 데이터를 저장한다.

 

[시스템 버전 테이블 만들기]

새 임시 테이블을 생성 할 때 전체 조건의 몇 가지 사항을 충족해야 한다.

  • 기본 키를 정의 해야 한다.
  • 두 열은 Datetime2 타입의 시작 및 종료 날짜를 기록하도록 정의되어야 한다. 이 열은SYSTEM_TIME 기간 열이라고 한다.
  • INSTEAD OF 트리거는 허용되지 않는다.
  • In-Memory OLTP는 사용할 수 없다.

 

다음은 몇 가지 제한 사항이다.

  • 임시테이블 및 히스토리 테이블은 파일 테이블일 수 없다.
  • 히스토리 테이블은 constraints 제약이 없다.
  • INSERT 및 UPDATE 문은 SYSTEM_TIME 기간 열을 참조 할 수 없다.
  • 히스토리 테이블의 데이터는 수정할 수 없다

 

제약사항에 대한 자세한 내용은 MSDN을 참고한다.

 

아래 스크립트는 시스템 버전 테이블을 생성한다.

CREATE TABLE dbo.TestTemporal (

ID int primary key

,A int

,B int

,SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL

,SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL

,PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)) WITH(SYSTEM_VERSIONING = ON

);

 

테이블이 생성되었을 때 히스토리 테이블은 dbo.MSSQL_TemporalHistoryFor_XXXXXX로 생성되는데 XXXXX는 개체 ID이다. 히스토리 테이블의 이름을 지정하지 않으면 SQL Server는 자동으로 다음과 같은 구조를 생성한다.

 

 

히스토리 테이블 컬럼은 동일한 세트를 가지고 있으며 자신만의 인덱스와 통계 세트가 있다. 이러한 히스토리 테이블에 클러스터 컬럼스토어 인덱스를 생성하면 성능을 크게 향상 시킬 수 있다.

 

이제 테이블에 데이터를 삽입하여 테이블의 시간적 버전 기능을 테스트한다.

-- Initial Load

INSERT INTO dbo.TestTemporal(ID, A, B)

VALUES     (1,2,3)

        ,(2,4,5)

        ,(3,0,1);

 

SELECT * FROM dbo.TestTemporal;

 

 

이제 하나의 행을 삭제하고 다른 데이터는 업데이트 작업을 진행 한다.

-- Modify Data

DELETE FROM dbo.TestTemporal

WHERE ID = 2;

 

UPDATE dbo.TestTemporal

SET A = 5

WHERE ID = 3;

 

SELECT * FROM dbo.TestTemporal;

 

 

히스토리 테이블을 조회해보면 이전 버전의 데이터를 확인할 수 있다.

select * from dbo.MSSQL_TemporalHistoryFor_1253579504

 

 

 

[시스템 버전 테이블의 스키마 변경]

시스템 버전 테이블을 사용하는 경우 테이블 수정이 제한된다.

  • ALTER TABLE….REBUILD
  • CREATE INDEX
  • CREATE STATISTICS

 

다른 모든 스키마 변경도 허용되지 않는다. 예를 들면 임시테이블 삭제도 허용되지 않는다.

drop table dbo.TestTemporal

 

메시지 13552, 수준 16, 상태 1, 줄 14

테이블 삭제 작업은 시스템 버전 관리 임시 테이블에서 지원되는 작업이 아니므로 'SW_Test.dbo.TestTemporal' 테이블에서 테이블 삭제 작업을 수행할 수 없습니다.

 

 

히스토리 테이블에 새로운 열을 추가작업이나 스키마 변경을 위해서는 시스템 버전을 먼저 제거해야 한다.

ALTER TABLE dbo.TestTemporal SET (SYSTEM_VERSIONING = OFF);

 

스키마 변경 후 히스토리 테이블은 동기화를 유지하기 위해 시스템 버전을 다시 시작하여야 한다.

ALTER TABLE dbo.TestTemporal SET (SYSTEM_VERSIONING = ON

(HISTORY_TABLE=dbo.MSSQL_TemporalHistoryFor_1253579504,DATA_CONSISTENCY_CHECK=[ON/OFF])

);

 

 

[참고자료]

 

 

2015-08-05 / 강성욱 / http://sqlmvp.kr

 

 

 

SQL Server 2016, SQL 2016 New Feature, MS SQL, 데이터베이스, SQL 2016 신기능, 변경내역 추적, Temporal Table, 히스토리 테이블

SQL Server 2016 Feature in CTP2

 

  • Version : SQL Server 2016

 

SQL Server 2016에 포함된 새로운 기능들에 대해서 알아 본다.

 

[데이터베이스 엔진]

 

[Azure]

 

 

[Business Intelligence]

  • 마스터 데이터 서비스(MDS) 향상. MDS의 백엔드에 상당한 변화가 있다. (http://sqlblog.com/blogs/mds_team/archive/2015/05/27/what-s-new-in-sql2016-ctp2-release.aspx)
    • 향상된 성능 : 더 큰 모델을 작성하고 데이터를 로드하여 더 나은 성능을 나타낸다. 수 있다. 엑셀용 추가 기능(add in)은 성능 업그레이드를 가지고 더 많은 수천개의 개체를 처리 할 수 있다.
    • 향상된 관리 : 이름 속성 50자 이상 지원, 이름 속성을 숨기거나 변경 가능
    • 보안 향상 : 서버 관리자와 동일한 권한을 가진 새로운 슈퍼 사용자 기능이 있어 권한이 더 세분화된 수준의 정의된 읽기 업데이트 생성 및 삭제 작업을 진행 한다.
    • 더 나은 트랜잭션 로그 유지 관리
    • 향상된 문제 해결
    • 병렬 파티션 처리
    • 새로운 DAX 함수. 평균과 백분위 기능 뿐만 아니라 날짜 테이블, 달력(자동) 등
    • 새로운 DBCC 명령어로 SSAS 멀티디멘젼 모델의 커럽션을 확인할 수 있다.
    • SSAS에서 파워 피봇에 대한 쉬운 셋업
    • .NET 프레임워크 4 지원
    • 보고서 작성기의 높은 DPI 제공
    • 구독 향상
      • 사용 및 구독 해제(현재 기본 모드에서 지원)
      • 설명(SharePoint 및 기본 모드)
      • 변경 등록 소유자 (SharePoint 및 기본 모드)
      • 구독에 재사용 할 수 있는 하나의 파일 공유 계정을 정의할 수 있는 기능(기본모드)
    • SSIS에 대한 증분 패키지 배포

 

 

[참고자료]

What's New in Database Engine : https://msdn.microsoft.com/en-us/library/bb510411.aspx

 

 

2015-07-30 / 강성욱 / http://sqlmvp.kr

 

 

SQL Server 2016, SQL 2016 New Feature, MS SQL, 데이터베이스, SQL 2016 신기능

Temp table 객체 생성시 세션간 충돌하지 않는 이유

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

 

데이터베이스를 사용할 때 temp table(임시 테이블)을 많이 사용한다. 단일 tempdb가 여러 세션에서 동일한 개체를 만드는 경우에도 어떻게 충돌을 방지할 수 있을까? 다음 간단한 테스트를 통해서 임시테이블 생성과 충돌 방지에 대해서 알아본다.

 

아래 스크립트는 임시 테이블을 생성한다.

-- Session 1: Table creation

CREATE TABLE #temptable_test (

id INT NOT NULL IDENTITY (1,1)

,Name CHAR(100) NOT NULL

,DOJ DATETIME NOT NULL

);

 

테이블을 만든 후에 메타데이터에서 tempdb 데이터베이스 내부에 생성된 객체를 확인한다.

-- SHOW USER TABLE

SELECT * FROM tempdb.sys.sysobjects WHERE TYPE = 'U'

 

 

생성된 임시 테이블은 부정적인 개체ID와 이름에 긴 밑줄과 함께 번호가 있는것으로 확인할 수 있다. SQL Server에서는 임시테이블에 번호를 부여하여 전체 인스턴스에 대한 단일 tempdb가 여러 세션에서 동일한 개체를 만드는 경우에도 충돌을 방지할 수 있다. 동일한 이름으로 두 번째 임시 테이블을 생성한다.

-- Session 2: Table creation

CREATE TABLE #temptable_test (

id INT NOT NULL IDENTITY (1,1)

,Name CHAR(100) NOT NULL

,DOJ DATETIME NOT NULL

);

 

 

메타데이터를 확인해 보면 다른 번호를 가지고 있는 객체가 생성된 것을 확인할 수 있다.

 

이렇게 생성된 임시테이블은 다른 세션에서 동일한 이름을 사용해도 충돌을 방지 할 수 있으며 세션이 종료될 때 자동으로 제거된다. 임시 테이블인 세션이 종료될 때 삭제되는 것을 제외하면 일반 테이블과 유사하다.

 

 

[참고자료]

http://blog.sqlauthority.com/2014/11/27/sql-server-inside-temp-table-object-creation/

 

2015-06-24 / 강성욱 / http://sqlmvp.kr

 

SQL Server, TempDB, Temp table, 임시테이블, mssql, 데이터베이스, sys.sysobjects

+ Recent posts