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

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

인덱스 리빌드 동작 (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

 

 

 



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

No. Subject Author Date Views
1970 전체 백업에서 포함되는 트랜잭션 범위 jevida(강성욱) 2016.11.23 2628
1969 REPL_SCHEMA_ACCESS 대기 유형 jevida(강성욱) 2016.11.23 2171
1968 Database 손상시 Emergency 모드로 복구하기 jevida(강성욱) 2016.11.23 2902
1967 서버 그룹을 이용한 다중서버 쿼리하기 jevida(강성욱) 2016.11.23 2070
1966 SQL Server Agent에서 CmdExec 오류 jevida(강성욱) 2016.11.23 2291
1965 Verbose SQL Server Agent Logging jevida(강성욱) 2016.11.23 1723
1964 Windows Event Log에 SQL Server Agent Log 기록 jevida(강성욱) 2016.11.23 2255
1963 SQL Server Agent Error log 위치 변경 jevida(강성욱) 2016.11.23 2478
1962 SQL Server에서 차단을 확인하는 다양한 방법 jevida(강성욱) 2016.10.18 4545
1961 SQL Server CPU 사용률이 높은 프로세서 및 쿼리 찾기 jevida(강성욱) 2016.10.18 6977
1960 Collation 변경 jevida(강성욱) 2016.10.18 4556
» 인덱스 리빌드 동작 (Gather Streams from SORT) jevida(강성욱) 2016.10.18 3465
1958 최소한의 다운타임으로 데이터베이스 이동하기 jevida(강성욱) 2016.10.18 3125
1957 백업 LSN 이해하기 jevida(강성욱) 2016.10.18 3420
1956 암호화 오버헤드 (작성자의 주관적인 자료임) jevida(강성욱) 2016.10.18 2847
1955 데이터베이스 연결 정보 수집 jevida(강성욱) 2016.10.18 3180
1954 FileStream Garbage Collection jevida(강성욱) 2016.10.18 3434
1953 프로시저 캐시에서 중복 쿼리 계획 확인 jevida(강성욱) 2016.10.18 3646
1952 Sp_reset_connection jevida(강성욱) 2016.10.18 3315
1951 성능분석 17탄 – SQLServer 블록킹 / 네트워크 관련 성능 카운터 jevida(강성욱) 2016.10.15 3714





XE Login