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

유지관리 계획과 병렬처리 – 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 설정을 이용하여 다른 서비스에 영향을 최소화 하면서 작업을 할 수 있지 않을까 생각한다.

 

[참고 자료]

• http://www.sqlskills.com/blogs/erin/sql-server-maintenance-plans-and-parallelism-index-rebuild/

 

2013-03-13 / 강성욱 / http://sqlmvp.kr