인덱스 리빌드 동작 (Gather Streams from SORT)

인덱스 리빌드 동작 (Gather Streams from SORT)

 

• Version : SQL Server 2005, 2008, 2008R2, 2012, SQL2014

 

SQL Server에서는 인덱스 리빌드 작업을 통하여 조각난 인덱스를 다시 작성한다. SQL Server 데이터베이스 엔진에서는 기본 데이터에 삽입, 업데이트, 삭제 작업을 수행 할 때마다 인덱스를 자동으로 유지 관리한다. 이러한 수정이 빈번이 발생하면 시간이 흐름에 따라 인덱스의 정보가 조각화 되어 데이터베이스 내에 흩어지게 될 수 있다.

조각화는 인덱스의 논리적 페이지 순서가 데이터파일의 물리적 순서와 일치하지 않을 때 나타난다. 조각화가 심할 경우에는 쿼리의 성능이 저하될 수 있다.

 

이번 포스트는 CSS SQL Engineers에 게시된 내용으로 인덱스 리빌드 동안 발생하는 CPU 자원의 사용량에 대해서 알아본다. 필자가 읽고 이해한 내용을 정리하였으며 번역의 오류 및 기술적 오류 가능성이 있으므로 원문은 참고 하길 바란다.

 

64 CPU, 128 GB RAM 시스템에서 1조 행이 있는 테이블에 인덱스 리빌드 작업을 테스트 하였다. 인덱스 리빌드를 시작하였을 때 64개의 CPU 사용량이 100%였다. 그러나 일정 시간이 지나서 1개의 CPU만이 100% 사용되는 것을 확인 할 수 있다.

 

리빌드가 시작되면 64개의 작업단위로 나누어 각각의 nested loop와 sort 작업이 수행되며 마지막에 Gather Streams을 거쳐 인덱스의 인서트 과정이 진행된다. 실행 계획은 아래 그림과 같으며 Sort 작업 다음에 Gather Streams 작업에서 CPU Node 사용이 1로 된 것을 확인 할 수 있다.

 

 

 

소비자는 각 작업자에서 행을 가져와 메모리 트리를 유지 한다. 64개의 병렬 작업이 있을 경우 64개의 트리 항목이 있으며 MAXOP 16일 경우 트리는 16 항목이 포함된다. 4개의 프로세스가 있는 경우 다음과 같은 순서로 진행 된다.

1.Get Row From Worker/Partition #1 – Insert into tree

2.Get Row From Worker/Partition #2 – Insert into tree

3.Get Row From Worker/Partition #3 – Insert into tree

4.Get Row From Worker/Partition #4 – Insert into tree

5.While (entries in tree)

{

Output lowest, sorted value from tree

Get Row from Worker/Partition you just removed from tree as lowest value

}

 

 

 

리빌드가 진행 되는 동안 sys.dm_exec_requests, sys.dm_os_waiting_tasks를 확인해 보면 CPU 노드의 활동을 확인 할 수 있다. Sys.dm_os_wait_stats를 확인해 보면 CXPACKET 대기가 증가한 것을 볼 수 있는데 병렬 처리에서 나머지 작업이 완료되기를 기다리는 부분이다.

 

병렬계획을 최적화 하기 위해 MAXDOP를 조절하며 테스트를 진행한 결과이다. 많은 CPU를 사용할 때 더 빠른 작업을 나타내었지만 계획의 마지막 CXPACKET 대기는 DOP 수준으로 크게 변경되지는 않았다.

• 64 CPUs = 01:50:00
• 32 CPUs = 02:17:00
• 16 CPUs = 03:16:00

 

테스트를 통하여 인덱스 리빌드는 병렬자원을 활용하는 것을 알 수 있으며 각 작업에 대해서는 많은 CPU를 사용할 때 더 빠른 작업을 나타내었다. 하지만 CXPACKET 대기에서는 큰 차이가 나지 않아 여러 쿼리가 실행되는 서버의 경우 MAXDOP를 조절하여 다른 서비스에 영향을 주지 않도록 하면 최적화된 리빌드 작업을 할 수 있을 듯 하다.

 

[참고자료]

• How It Works: Behavior of a 1 Trillion Row Index Build (Gather Streams from SORT)

http://blogs.msdn.com/b/psssql/archive/2014/04/29/how-it-works-behavior-of-a-1-trillion-row-index-build-gather-streams-from-sort.aspx

 

• 유지관리 계획과 병렬처리 – Index Rebuild : http://sqlmvp.kr/140183714036
• MAXDOP 극대화 하기 : http://sqlmvp.kr/140198060105

 

 

 

 

2014-05-02 / 강성욱 / http://sqlmvp.kr

 

SQLSERVER, mssql, SQL튜닝, SQL강좌, DB튜닝, 쿼리튜닝, 데이터베이스튜닝, 인덱스리빌드, 병렬처리, CXPACKET