MSSQL에서도 Fast Track 또는 PDW(Parallel Data Warehouse)와 같은 DW에 특화된, Appliance제품들이있지만, 이번 시간에는 일반적인 DW를 위한 셋팅에 대해서 알아보고자 합니다. MSSQL에서 대용량을 처리할 때 특히 DW로 사용하고자 할 때 많은 사람들이 정말 가능할까? 라는 편견을 가지고 있었는데, 설치 후 그대로 사용하게 된다면 성능은 나오지 않지만 DW를 위한 시스템으로 셋팅을 변경한다면, 아주 좋은 성능이 나오게 됩니다. 경험에 의하면 보통 배 이상 빨라 졌습니다. 주의할 점은 워크로드를 OLAP위주로 셋팅하기 때문에 OLTP와 공존하게 될 경우 문제가 발생할 수 있으니 OLAP전용에만 적용하셔야 합니다. 그리고 운영장비에 바로 적용은 금물입니다. 초기도입 시 또는 테스트서버에서 충분한 테스트를 한 후에, 적용하시기 바랍니다. 크게 3가지 부분에서 작업하게 되는데, 서버부터 시작하여 데이터베이스, 테이블로 끝납니다. 서버부터 시작해 점점 세밀하게 조정하게 되는 것입니다.

먼저 서버 셋팅을 살펴 보면 시작옵션에서 몇 가지 Trace Flag와 로컬보안정책을 고려할 수 있습니다. Trace Flag-T834, -T2301, -E, -T1117, -T610 입니다.

-T834
-T834
Large Page Allocation(T834)으로 메모리가 8G 이상이어야 하며, Enterprise이어야 적용 가능합니다. 서버의 워크로드가 DW일 경우에 장비 특성상 많은 메모리를 가지게 되는데, 한번에 많은 양의 데이터를 처리 하게 되므로, SQL Server 데이터 버퍼 풀 메모리 영역에 대형 페이지 할당을 함으로써, 처리 속도를 향상 시킬 수 있습니다. 또한 해당 옵션으로 Max memory 사이즈까지 시작 시 할당하게 되므로 다른 서비스들과의 메모리 경합을 피할 수 있고 단편화가 일어나지 않은 장점이 있지만, 할당을 위해 시작시간이 지연됩니다.

-T2301
-T2301
2301 Enable advanced decision support optimizations 을 이용하여, 옵티마이져가 더 많은 고민을 하고 쿼리를 실행 하도록 합니다. 옵티마이져는 기본적으로 최고의 선택은 하지 않고 보통보다 조금 나은 플랜을 선택하게 되어 있는데, 해당 옵션으로 최고의 선택이 될 수 있도록 조정하는 것입니다. OLAP특성상 대용량을 읽어서 처리하는 쿼리는 처리시간이 매우 긴 편인데, 예를 들어 20분간 조회를 해야 하는 쿼리가 있다면, 1분간 옵티마이져에게 좀더 고민을 하는 시간을 줘서 최고의 플랜으로 10분만에 결과값이 나오게 된다면 1분의 시간은 아깝지 않을 것입니다.

–E
–E
는 데이터베이스에서 테이블이 증가함에 따란 할당되는 연속 Extents의 수를 증가 하여 순차 디스크 액세스의 성능을 향상 시킵니다. 이 옵션은 한번에 파일 그룹의 각 파일에 할당되는 익스텐트의 수를 늘리게 되는데, 인덱스 또는 데이터 검색을 실행하는 사용자 수가 제한되는 DW에 유용합니다. 이 옵션은 SQL Server 32Bit 릴리스에서는 지원되지 않습니다.

-T1117
-T1117
은 파일 그룹 내에 균등한 파일증가를 보장해 줍니다. 이후에 CPU코어수 만큼 데이터베이스의 파일 그룹 내에 파일을 생성하게 되는데, 이때 파일들의 용량이 균일하게 증가해야 속도를 보장할 수 있습니다. CPU가 코어와 데이터파일이 쌍을 이뤄 읽을 때 한쪽파일만 비정상적으로 커진다면, 나머지는 다 읽었지만, 대기하는 시간이 발생하게 될 것 입니다. 이러한 현상을 방지하기 위해서 T1117 플래그를 사용해야 합니다.

 

-T610
-T610
Minimal logging로써, 로그를 가능한 적게 남길 수 있습니다. 대용량으로 데이터를 읽고 쓰는 DW 워크로드에서는 가능한 로그를 적게 남기는 게 성능에 유리 합니다.  클러스터 인덱스에 삽입하지 않는 행은 최소한으로 기록되므로, 일반적으로 성능 향상이 나타나게 됩니다.

 

SQL 2012부터는 시작매개변수(Startup Parameters)가 변경되었습니다

01.png

다음과 같이 구성 관리자에서

02.png

속성을 클릭한 후,

 03.png

-T834, -T1117, -E, -T2301, -T610 의 총 5개의 플래그를 입력 후, 확인을 클릭하여, 아래와 같이 메시지가 나오면 확인을 선택합니다.

.04.png

 

로컬 보안 정책은 먼저 아래와 같이 시작계정을 확인해야 합니다.

05.png

위와 같이 NT Service\MSSQLSERVER 계정으로 엔진이 실행될 시에, 몇 가지 보안정책에 해당 계정을 추가해 줘야 합니다.

06.png

07.png

에서 사용자 또는 그룹 추가를 클릭 후 NT SERVICE\MSSQLSERVER를 추가해 주셔야 합니다.

여기서 MSSQLSERVER는 숨겨진 그룹으로 보여지지 않으니, 이름을 정확히 입력하셔야 추가 하실 수 있습니다.

[NT SERVICE\MSSQLSERVER]를 추가해야 하는 정책은 아래 4가지 입니다.

메모리에 페이지 잠금

볼륨 유지 관리 작업 수행

운영 체제의 일부로 작동

일괄 작업으로 로그온

 

다음은 데이터베이스 입니다 Trace Flag와 로컬서버보안정책으로 서버 설정변경이 끝났다면, TempDB DW데이터베이스는 CPU코어 수만큼 파일을 생성해 줍니다. 여기서 CPU코어 수에 대한 의견이 분분한데, CPU코어수가 16개라고 가정하면, 8개까지는 성능향상이 있으며, 그 이상은 하나씩 올려가면서 테스트하여 가장 적합한 개수로 설정하게 됩니다. 너무 많은 파일 수는 관리에 대한 비용이 증대되어 성능이 오히려 나쁘게 되는 것에 주의하시면 됩니다.

이렇게 생성된 파일은 데이터를 저장할 때 분산된 파일그룹으로 병렬로 처리가 가능하고 읽을 때도 병렬로 읽음으로써, 속도가 향상 됩니다. 경험상 이렇게 데이터베이스에 대한 설정을 변경한 것만으로도 속도향상이 되었습니다.

다음과 같이 코어 수만큼 데이터베이스파일을 생성하여 줍니다.

08.png

TempDB는 기본적으로 MSSQL설치 폴더에 있지만, 다른 디스크로 옮겨야 합니다. DW데이터베이스도 마찬가지이며, OS의 경합을 피하기 위해 다른 디스크로 옮기고, 가급적 디스크를 분리해서 한 디스크에 하나의 파일그룹이 존재하는 것이 가장 좋습니다.

SELECT name, physical_name

FROM sys.master_files

WHERE database_id = DB_ID('tempdb');

GO

 

 

USE master;

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf');

GO

 

-- 서비스 재기동

SELECT name, physical_name

FROM sys.master_files

WHERE database_id = DB_ID('tempdb');

 

TempDB UI에서는 설정이 안되므로, 다음과 같은 스크립트를 실행 한 후, 서비스를 재기동 해야 합니다.

 

TempDB에 대한 설정이 끝난 후, DW 용도로 데이터베이스를 만들어 보겠습니다. 코어 수 만큼  데이터베이스 파일을 같고,  한가지만 주의 하시면 됩니다. 바로 복구모델입니다. 기본적으로 복구모델은 전체 입니다만, 로그를 적게 남기고 데이터를 저장하려면, 단순으로 변경해야 합니다.

09.png

마지막으로 데이터베이스 옵션 중 통계 비동기 업데이트 입니다. 쿼리가 실행되기 전에 통계가 업데이트가 된다면 테이블 전체를 Full-Scan하게 되는데 이러한 불상사를 막으려면 비동기 업데이트를 하는 것이 좋습니다. 아래 [통계를 비동기적으로 자동 업데이트] 옵션을 [True]로 변경하여 줍니다.

10.png

이번 시간에는 데이터베이스 알아 보았습니다. 몇 가지 셋팅 변경만으로 성능을 극대화 할 수 있다면 정말 매력적일 것입니다. 노파심에서 다시 한번 말씀 드리지만, 운영장비 바로 적용은 금물입니다. 초기 도입 시 또는 테스트서버에서 충분한 테스트 다음에 적용하시길 바랍니다. 다음 시간에는 테이블 압축 및 몇 가지 옵션에 대해서 알아 보겠습니다.

 

<목차>

  1. BI, Microsoft BI 소개
  2. DW, DM, OLAP의 이해
  3. 다차원모델링(1/2) – 스키마, Fact테이블
  4. 다차원모델링(2/2) - Dimension테이블, 다차원DB구축 과정 정리
  5. MSSQL 2012 DW 셋팅
  6. 압축
  7. 파티션
  8. 컬럼스토어 인덱스
  9. MSSQL 2012 Semantic Model 소개
  10. MSSQL 2012 Power Pivot
  11. MSSQL 2012 Tabular Model(1/2) – 기본편
  12. MSSQL 2012 Tabular Model(2/3) – 고급편
  13. MSSQL 2012 Tabular Model(3/3) - DAX
  14. MSSQL 2012 Dimension Model(1/5) - 차원
  15. MSSQL 2012 Dimension Model(2/5) - 측정값그룹, 큐브
  16. MSSQL 2012 Dimension Model(3/5) - 파티션
  17. MSSQL 2012 Dimension Model(4/5) - 집계 디자인
  18. MSSQL 2012 Dimension Model(5/5) - 계산된 메져, MDX
  19. MSSQL 2012 DQ
  20. MSSQL 2012 MDS




profile

안녕하세요, SQLER의 바쉬*^^*, 김상수입니다. 행복 하세욤~. ^.^
위세아이텍 연구소 책임 / SQLER 시샵 / BI Developer
E-Mail: bash1130@hotmail.com / Mobile: +82 10-6231-7672 / NateOn:bash1130@nate.com