SQL Server 인덱스 구성 전략(시리즈-4. 정렬되지 않은 파티션 인덱스)

김정선의 좋은 글을 찾아서……
SQL Server 인덱스 구성 전략(시리즈-4. 정렬되지 않은 파티션 인덱스)

 

 

김정선(jskim@feelanet.com) 필라넷 DB사업부 수석컨설턴트 SQLServer 아카데미/트라이콤 교육센터 강사   Microsoft SQL Server MVP MCT/MCITP/MCDBA

 

Part 4: 오프라인, 직렬/병렬 파티셔닝(정렬되지 않은 파티션 인덱스 구성)

정렬되지 않은 경우는 힙과 인덱스가 서로 다른 파티션 스킴을 사용하거나, 힙이 파티션되지 않은 경우이다.

이번 글에서 원본 데이터가 파티션 된 경우와 그렇지 않은 2가지 경우의 정렬되지 않은 파티션 인덱스 구성에 대해 다룰 것이다.

 

원본이 파티션 되지 않은 경우

다음 쿼리를 보자.

 

Create Partition Function pf (int) as range right for values (1, 100, 1000)

Create Partition Scheme ps as Partition pf
ALL TO ([PRIMARY])

Create table t (c1 int, c2 int) – 테이블은 디폴트로 PRIMARY 파일 그룹에 생성

Create clustered Index idx_t on t(c1) on ps(c1)  -– 정렬되지 않은 인덱스 구성

 

직렬 계획은 간단하다,

 Index Insert (write data to the in-build index)
   |
 Sort (order by index key)
   |
 Scan (read data from source)

 

Sort 반복연산자(iterator)는 각 파티션당 하나의 정렬 테이블을 만든다(예제에서는 4개의 파티션이 존재하므로 동시의 4개의 정렬 테이블이 만들 것이다). 디폴트로, 데이터 정렬을 위해 사용자 데이터베이스를 사용한다. 이전에 언급한대로, 정렬 데이터에서 모든 데이터를 복사하고 나면 각각의 익스텐트를 해제한다. 이 동작을 통해서 각 파티션 별로 필요한 디스크 공간을 3 x 파티션크기에서 2.2 x 파티션 크기로 줄일 수 있다. 따라서 각 파일 그룹은 2.2 x (해당 파일 그룹에 속한 전체 파티션 크기)만큼의 디스크 공간을 요구한다. SORT_IN_TEMPDB 옵션을 지정한다면, 모든 정렬 테이블은 tempdb에서 다루어질 것이고, 2.2 x (인덱스 전체 크기)만큼의 여유 공간을 tempdb에 요구할 것이다.

 

Index Insert 반복연산자는 sort 반복연산자가 정렬을 완료하고 나면 인덱스 구성을 시작한다. 파티션 수 만큼의 정렬 테이블이 필요하므로, 각 정렬 테이블 당 최소 40페이지가 필요함을 상기한다면, 최소 필요 메모리는 파티션 수 x 40페이지가 될 것이다.

 

병렬 계획이 된다면,

 

X (Exchange)
   |
 Index Insert
   |
 Sort
   |
 Scan

 

각 작업자 스레드가 병렬 처리 수와 해당 파티션 수만큼 계산되어 할당된다 (예를 들어, 4개의 파티션에 4개의 작업자 스레드이면 각 스레드 당 1개의 파티션). Sort 반복연산자를 할당된 각 파티션에 대해 하나의 정렬 테이블을 생성한다. 각 작업자는 원본 데이터를 한 번 스캔하고 그 파티션에 속한 행들을 처리한다, 처리된 행들은 소속된 파티션에 따라 해당 정렬 테이블에 입력된다.

 

모든 정렬 테이블이 완성되면, 인덱스 구성자가 정렬 테이블을 하나씩 처리하며, 각 파티션의 파일 그룹별로 b-tree를 구성하게 된다.

디스크 공간 및 메모리 필요 사항은 이전의 직렬 계획과 동일하다. 두 경우 모드, 모든 정렬 테이블이 완성되기 전까지 인덱스 구성을 시작할 수 없기 때문이다.

 

 

 

원본이 파티션된 경우

원본 테이블이 이미 파티션된 경우엔, 파티션 인덱스를 구성할 때 그 방법을 바꿀 수 있다.

예를 들어, 원본 테이블과 동일한 파티션 함수와 스킴을 사용하되, 새로운 인덱스는 다른 칼럼으로 파티션될 수 있다.

CREATE TABLE t (c1 int, c2 int) ON ps (c2)

……

CREATE CLUSTERED INDEX idx_t ON t(c1) ON ps(c1)

 

직렬 계획은 다음과 같다,

    Index Insert
       |
     Sort
       |
      NL (Nested Loop)
     /    \
 CTS   Scan

Constant Table Scan 연산자에 의해 파티션 ID를 하나씩 넘겨주면, NL 연산자에 의해서 해당 파티션 데이터를 스캔하고 결과를 Sort 반복연산자에 넘겨준다. 여기서부터 원본이 정렬되지 않은 시나리오와 동일하다. 메모리와 디스크 요구 사항 또한 같다.

 

병렬 계획의 경우는,

 

X (Distribute Streams)
       |
     Index Insert
       |
     Sort
       |
       X (Repartition Streams)
       |
      NL
     /   \
   X    Scan
  /
CTS

 

CTS위에 있는 연산자가 Gather Streams 연산자이다, 그 말은 생성자 하나의 다중 소비자를 가진다는 뜻이다 (역주: Gather Stream연산자에 다중 입력 처리를 통해 병렬 처리를 수행하고 이를 단일 출력으로 제공하는 연산자이다). Gather Streams와 Repartition Streams 연산자 사이에, 원본 파티션 수에 병렬 처리 수를 나눈 만큼의 작업자가 할당된다. 원본은 한 번만 스캔한다.

Repartition Streams 연산자는 쿼리 계획을 두 개의 병렬 처리 영역으로 분리한다. 최상위에 있는 Distribute Streams 연산자와 Repartition Streams 연산자 사이에서는 앞서 Repartition Streams 아래에서 만들어진 작업자 집합과는 또 다른 집합을 가진다. 각 작업자가 대상 파티션에 할당되는데 그 수는 대상 인덱스 파티션 수를 병렬 처리 수로 나눈 만큼 할당된다. 나머지 정렬 및 인덱스 구성 작업은 이전의 원본이 파티션되지 않은 경우의 병렬 처리와 동일하며, 메모리 및 디스크 공간 또한 동일하다.

 

 

여기까지입니다.

계속 살펴봐 주신 분들에게 감사드립니다.

더불어 원본에서 다음 문서가 또 올라오면 이어서 계속 소개해 드리도록 하겠습니다.

그럼, 일단 이 시리즈 문서는 접구요,

다음에 또 다른 재미있는 자료를 번역해서 공유하도록 하겠습니다.

행복한 하루 되세요~~~

[출처] SQL Server 인덱스 구성 전략(시리즈-4. 정렬되지 않은 파티션 인덱스) |작성자 김정선