데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

유지관리 계획과 병렬처리 – Index Rebuild

 

  • Version : SQL Server 2012

 

  • 엔터프라이즈 에디션 지원

지난 아티클에 유지관리 계획에서 CHECKDB의 병렬처리에 대해서 알아 보았다.

유지관리계획 CHECKDB 링크 : http://sqlmvp.kr/140183594899

 

이번 시간에는 Index Rebuild에서의 병렬처리에 대해서 알아 본다. 실습을 하기에 앞서 지난 아티클을 참고 하여 SQL Server 2012에서 이벤트를 생성해야 한다.

 

테스트를 위하여 데모 데이터를 생성 한다. 데모데이터 생성은 아래 링크를 참고하여 스크립트를 실행 한다.

데모 데이터 링크 :

http://www.sqlskills.com/blogs/jonathan/enlarging-the-adventureworks-sample-databases/

 

 

 

SP_CONFIGURE 설정을 통하여 현재 CPU의 코어는 4Core이지만 MAXDOP을 2Core로 설정 하였다.

 

 

인덱스 리빌드 작업을 진행한다 이때 쿼리 옵션으로 MAXDOP = 4를 할당 하였다. 어떻게 처리 될까?

ALTER EVENT SESSION [CapturePlans]

ON SERVER

STATE=START;

GO

 

ALTER INDEX [IX_SalesOrderDetailEnlarged_ProductID] ON [Sales].[SalesOrderDetailEnlarged]

REBUILD WITH (MAXDOP = 4);

GO

 

ALTER EVENT SESSION [CapturePlans]

ON SERVER

STATE=STOP;

GO

 

 

우선 실행계획을 보면 병렬로 처리 된 것을 확인 할 수 있다. XML을 이용한 실행계획을 보면 병렬처리에 프로세스 4개가 할당된 것을 확인 할 수 있다. 즉 SQL Server의 옵션은 MAXDOP = 2 이지만 쿼리 옵션을 통하여 MAXDOP를 지정 할 수 있다.

 

 

이번에는 쿼리 옵션 없이 테스트한 결과 이다.

ALTER EVENT SESSION [CapturePlans]

ON SERVER

STATE=START;

GO

 

ALTER INDEX [IX_SalesOrderDetailEnlarged_ProductID] ON [Sales].[SalesOrderDetailEnlarged]

    REBUILD

GO

 

ALTER EVENT SESSION [CapturePlans]

ON SERVER

STATE=STOP;

GO

 

 

SQL Server의 MAXDOP 설정이 2로 되어 있기 때문에 병렬로 처리 되었다. XML 정보에서도 프로세스가 2개 할당된 것을 확인 하였다.

 

 

이번에는 MAXDOP의 설정을 1로 변경하여 테스트 하였다.

sp_configure 'max degree of parallelism', 1;

GO

 

RECONFIGURE WITH OVERRIDE;

GO

 

ALTER EVENT SESSION [CapturePlans]

ON SERVER

STATE=START;

GO

 

ALTER INDEX [IX_SalesOrderDetailEnlarged_ProductID] ON [Sales].[SalesOrderDetailEnlarged]

REBUILD

GO

 

ALTER EVENT SESSION [CapturePlans]

ON SERVER

STATE=STOP;

GO

 

 

 

실행 계획에서는 싱글 스레드로 처리 된 것을 확인 할 수 있다.

 

 

XML 정보에서도 싱글로 처리 되었음을 나타내고 있다

 

지금까지 MAXDOP를 통하여 인덱스 리빌드시 병렬로 처리되는 부분을 확인해 보았다.

Index Rebuild의 경우에는 병렬로 처리 되지만 reorganize 경우에는 항상 싱글 스레드로 처리 된다.

또한 SQL Server 2012에서는 분할된 인덱스를 만들거나 다시 작성할 때 테이블의 모든 행을 검사하여 통계를 작성하지 않는다. 대신 옵티마이저가 기본 샘플링 알고리즘을 사용하여 통계를 생성한다. 테이블의 모든 행을 검사하여 분할된 인덱스에 대한 통계를 얻으려면 FULLSCAN 절에서 CREATE STATISTICS 또는 UPDATE STATISTICS를 사용해야 한다.

 

운영환경에서 인덱스를 리빌드 해야 하거나 생성해야할 이슈가 있을 때 서로 다른 인스턴스 환경이라면 MAXDOP 설정을 이용하여 다른 서비스에 영향을 최소화 하면서 작업을 할 수 있지 않을까 생각한다.

 

[참고 자료]

 



강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1750 SQL Server 인증 실패시 반환되는 클라이언트 메시지 정보 jevida(강성욱) 2016.09.15 3703
1749 SQL Server에 할당된 메모리 개체 확인 jevida(강성욱) 2016.09.15 1583
1748 SQL Server 비동기 업데이트 활성 / 비활성에 따른 특성 jevida(강성욱) 2016.09.15 1704
1747 DBCC CHECKDB와 Compute Column 인덱스의 성능 관계 jevida(강성욱) 2016.09.15 1435
1746 Collation에 따른 DMV 실행 오류 jevida(강성욱) 2016.09.15 1310
1745 참조 개체 확인 (sys.sql_expression_dependencies) jevida(강성욱) 2016.09.15 1580
1744 특정 테이블의 마지막 접근 시간 알아보기 jevida(강성욱) 2016.09.15 1239
1743 SQL Server Fill Factor (채우기 비율)에 관한 오해와 진실 jevida(강성욱) 2016.09.15 3664
1742 LOB 데이터와 Shrink 작업 jevida(강성욱) 2016.09.15 1239
1741 데이터베이스 함수 검색 하기 jevida(강성욱) 2016.09.15 1063
1740 필터 통계 사용과 파리미터 사용 jevida(강성욱) 2016.09.15 1180
» 유지관리 계획과 병렬처리 – Index Rebuild jevida(강성욱) 2016.09.15 1071
1738 유지관리 계획과 병렬 처리 – CHECKDB jevida(강성욱) 2016.09.15 1132
1737 쿼리 사이즈(길이) 에 따른 CPU 사용량 증가 jevida(강성욱) 2016.09.14 1302
1736 Ad-hoc 쿼리를 매개변수화 하여 성능 높이기 jevida(강성욱) 2016.09.14 1662
1735 통계 업데이트 옵션(ROWCOUNT and PAGECOUNT) jevida(강성욱) 2016.09.14 1233
1734 SQL Server Plan Guide 생성 및 사용 jevida(강성욱) 2016.09.14 1422
1733 SQL Server 그래픽 실행 계획 노드 정보 jevida(강성욱) 2016.09.14 1066
1732 프로파일러를 이용한 실행계획 캡처하기 jevida(강성욱) 2016.09.14 959
1731 SQL Server 그래픽 실행 계획 및 텍스트 실행 계획 jevida(강성욱) 2016.09.14 3123





XE Login