SQL Server 인덱스 구성 전략(시리즈-2. 일반인덱스)

김정선의 좋은 글을 찾아서……
SQL Server 인덱스 구성 전략(시리즈-2. 일반인덱스)

 

 

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

 

Part 1: 오프라인, 직렬, 일반 인덱스(파티션 되지 않은)

 

Builder (write data to the in-build index)

                           |

                     Sort (order by index key)

                           |

                     Scan (read data from source)

 

 

b-tree 인덱스를 구성하기 위해서 우리는 먼저 원본 데이터를 정렬해야 한다. 작업 순서는 원본 데이터를 스캔하고, 정렬한 뒤(가능한 메모리에서*) 그리고 b-tree를 구성하는 것이다.

 

왜 b-tree를 바로 만들지 않고 먼저 정렬을 하는가? 이론적으로는 정렬할 필요가 없다, 일반적인 DML를 사용해서 바로 인덱스 구성 작업에 데이터를 추가해도 된다, 그러나 이 경우 추가하는 데이터가 랜덤하다면 결국 b-tree 상의 적합한 리프 노드를 먼저 검색한 뒤에 입력하게 된다. b-tree 검색이 빠르긴 하지만, 최적은 아니다. 따라서 인덱스 구성 작업은 인덱스에 필요한 정렬을 사용해서 데이터를 먼저 정렬하고, 인덱스 구성 작업으로 넘겨주는 것이다, 이는 그저 b-tree상에 추가 작업만 요구된다.

 

정렬에서 인덱스 구성자 사이에 데이터를 넘기는 동안 각 익스텐트 별로 모든 행이 복사되는 즉시 해당 익스텐트를 해제한다. 이를 통해 인덱스 구성 시 이론적으로 필요한 작업 공간인 3 x 인덱스 크기(원본 + 정렬 테이블 + b-tree)를 2.2 x Index Size(대략)로 줄여준다.

 

*메모리 상에서 정렬된다고 보장하지는 않는다. 메모리 정렬 여부는 가용 메모리와 실제 행 수에 따라 의존적이다. ‘메모리 상에서 정렬’은 원론적으로 디스크 상에 정렬 테이블을 할당할 필요가 없으므로 빠르다. 그러나 반드시 필요한 것은 아니다. 물론 메모리 상 정렬 보다는 더 느리지만 디스크에서 데이터를 처리할 수 있다.

 

 

 

각 정렬 테이블(매우 작은 데이터일지라도)은 실행하는데 필요한 최소 40페이지(3,200KB)를 필요로 한다 (뒤에서 다루겠지만 병렬 처리의 경우 동시의 하나 이상의 정렬 테이블을 소비한다). 정렬 메모리를 계산할 때, 메모리 정렬에 필요한 충분한 메모리를 할당하려고 한다. 인덱스 생성 작업 시 최소 40페이지를 제공할 수 없을 정도로 메모리가 부족한 경우 작업은 실패할 수 있다.

 

인덱스 구성 마지막 단계는 항상 통계 정보 구성 작업이다. 적절한 통계 정보는 쿼리 최적화 프로그램(Optimizer)이 더 좋은 쿼리 계획을 산출하는데 도움을 준다, 사용자가 직접 ‘create’ 및 ‘update’ 통계 명령을 이용해서 SQL Server가 특정 개체에 대한 통계 정보를 생성하거나 갱신하도록 강제할 수도 있다. 그러나 새로운 인덱스를 생성할 때, 모든 행을 처리하게 되므로 결국 이 때가 전체 데이터(100%)의 통계 정보를 구성할 수 있는 절호의 기회가 되는 것이다.

 

정리:

직렬 계획과 오프라인으로 일반 인덱스를 구성하기 위해서는 대략 2.2 x 인덱스 크기에 해당하는 디스크 공간과 쿼리 실행자가 절차를 시작하기 위해 필요한 최소 40페이지의 메모리를 요구한다.

 

 

Part 2: 오프라인, 병렬, 일반 인덱스(파티션 되지 않은)

병렬 인덱스 구성 방식은 필요한 통계 정보를 가진 히스토그램이 있는지 없는지에 따라서 달라진다. 즉, 병렬 인덱스 계획은 크게 두 가지 범주를 가진다:

-       히스토그램이 가용

-       히스토그램이 가용하지 않음

 

히스토그램이 가용한 경우(병렬 정렬 및 구성 작업)

 

              X (Exchange)

   |          \            \

         Builder… Build…  Build… (write data to the in-build index)

                           |           |            |

                      Sort…      Sort…  Sort … (order by index key)

                           |          /            /

                       Scan (read data from source)

 

통계 정보가 가용한 경우 병렬 인덱스 구성이 가능하다(파티션 범위 정보를 사용할 수 있고 데이터의 분포를 결정하는데 사용할 수 있기 때문).

 

이 경우 어떻게 데이터를 스캔할까? 첫 번째 키 칼럼에 대한 통계 정보가 필요하다, 따라서 해당 통계 정보가 없으면 샘플링 기반의 통계 정보를 생성하고 이를 통해 병렬 여부와 병렬 처리 방법을 결정하는데 사용한다. 만일 인덱싱 뷰(통계 계획이 없는)와 같이 샘플링 통계 정보를 구성할 수 없는 경우엔, 다른 인덱스 구성 계획이 생성된다. 통계 정보와 히스토그램을 사용해서 데이터 분포를 결정할 수 있다, 이를 통해 병렬 계획에서 적절한 작업 부하 분산을 결정할 수 있으며, 또한 시스템 자원에 대한 고도의 활용률을 위해 병렬 처리 수(DOP, Degree Of Parallelism)를 결정하는데 도움을 주게 된다. 히스토그램으로부터 데이터 분포 상의 각 버킷(bucket)에 대한 행 수를 예상함으로써, N(N = DOP)개의 범위로 작업 부하를 분리하며, 각 작업자(worker)당 하나씩의 범위를 처리하게 된다.

 

데이터를 스캔하기 위해 범위 파티션 스캔을 사용하며, 각 작업자는 해당 범위에 데이터를 이용해서 자신만의 정렬 테이블를 구성하고 그 데이터에 기반한 b-tree를 만들게 된다. 각 작업자는 결국 분리된 개별 정렬 테이블과 b-tree를 가지는 것이다. 이후 인덱스 구성 작업의 마지막 단계에서 이들을 조정하는 담당 스레드(thread)에 의해서 모두 짜집기를 하게 되며, 마지막으로 완성된 b-tree에 대해 전체 데이터 통계정보 구성을 완료하게 된다.

 

히스토그램을 사용한 병렬 인덱스 구성은 최적의 성능을 제공한다. 반면에 이로 이한 문제는 앞서 다루었던 대로 더 많은 메모리를 소비한다는 것이며 만일 충분한 가용 메모리가 없는 경우 작업이 실패할 수도 있다(각 작업자당 개별 정렬 테이블을 만들게 되므로). 필요 시 MAXOP) 옵션을 사용해서 이를 적절히 조정할 수 있다.

 

For example:

Create index idx_t on t(c1, c2)

WITH (MAXDOP = 2)

 

 

 

히스토그램이 가용하지 않은 경우

 

Build (serial) (write data to the in-build index)

                          |

                X (Merge exchange)

                           /          |           \

                      Sort…      Sort…  Sort …(order by index key)

                           |           |            |

                       Scan…    Scan… Scan…(read data from source)   

 

히스토그램을 사용할 수 없다면(예를 들어 뷰에 인덱스를 만드는 경우) 이전 글에서 다루었던 방법을 사용할 수 없다, 따라서 데이터 분포와는 무관하게 일반적인 병렬 스캔을 사용한다.

 

동작 방식

원본 데이터는 병렬로 스캔한다. 그러나 b-tree 구성은 직렬 작업이다. 병렬 처리를 수행하는 각 작업자를 이전에 병렬 스캔 방법과 같이 동일한 방법으로 힙으로부터 특정 페이지를 스캔한다. 스캔 후는 각 작업자 별로 정렬 테이블을 가지고 데이터를 구성하며, 나중에 병합(Merge)을 통해서 데이터를 결합시키게 된다(이전과 같은 개별 b-tree 구조와 짜집기 방식은 사용하지 못한다). 최종 정렬 데이터가 만들어지면 이를 통해 직렬로 인덱스 구성 작업을 수행하게 된다. 왜 이 계획이 상대적으로 느린 걸까? 이는 직렬로 수행되는 인덱스 구성작업과 ‘Merge exchange’에 의해서 발생하는 추가 오버헤드 때문이다.

 

메모리 고려 사항

병렬 인덱스 구성은 동시에 여러 정렬 테이블을 구성하므로 기본 메모리 요구가 더 크며 계산식 또한 약간 달라진다. 메모리 계산식은 1) 필요 메모리, 2) 추가 메모리를 가진다. 필요 메모리를 각 정렬 당 40페이지를 요구했다. 그런데 예를 들어 DOP = 2라고 한다면, 2개의 정렬 테이블에 대해 총 80페이지가 필요 메모리가 되는 것이다. 그러나 추가 메모리는 DOP 설정과 무관하게 동일하다. 이는 전체 행 수가 DOP 설정과 무관하게 동일한 값이기 때문이다. 예를 들어 직렬 계획으로 추가 메모리 500페이지가 필요하다면 병렬 계획도 동일한 요구를 가지는 것이다. 각 작업자는 500/DOP 페이지 만큼의 추가 메모리에 + 40 페이지 필요 메모리를 가질 것이다.

다음 주제는,

시리즈-3. 정렬된(Aligned) 파티션(Partitioned) 인덱스

입니다. 또 기다려 주세요~~~

[출처] SQL Server 인덱스 구성 전략(시리즈-2. 일반인덱스)|작성자 김정선