온라인 인덱스 구성(Online Index)

SQL 2005 부터 인덱스와 관련하여 개선된 기능 중 하나인 온라인 인덱스 작업에 대하여 알아 보겠습니다. 온라인 인덱스 작업은 1년 365일 운영되어야 하는 시스템을 관리하는 DBA들에게 획기 적인 기능입니다. 온라인 인덱스를 할수 있게 됨으로써 다운 타임을 감소할수 있게 되었으며 인덱스를 재구성할려고 할때도 소요 시간 때문에 많은 고민을 해야 하는 수고를 덜어 줄수 있게 되었습니다. Create Index , Alter Index , Drop index Alter Table 명령어에서 ONLINE 옵션을 사용할수 있습니다.

온라인 인덱스 구성 vs. 오프라인 인덱스 구성
ONLINE 옵션은 이러한 인덱스 작업을 수행하는 동안에도 사용자가 테이블이나 클러스터형(Clustered) 인덱스와 관련된 비클러스터형(Nonclustered) 인덱스에 접근할수 있습니다. 오프라인으로 클러스터형 인덱스를 구성하거나 재구성하는 등의 DDL 작업을 하면 이는 해당 데이터와 관련 인덱스에 배타적 잠금을 보유하게 되고 이로 인해 다른 사용자가 데이터나 인덱스에 접근하지 못하도록 방해하게 된다.

Example:

Create index idx_t ON t(c1, c2)

WITH (ONLINE = ON)

일반 인덱스 구성 vs. 병렬 인덱스 구성
멀티 프로세서를 가진 SQL서버에서 인덱스 구문 또한 다른 쿼리를 실행할 때처럼, 스캔, 정렬, 그리고 구성 작업을 수행하는데 병렬 처리가 가능 해졌습니다. 병렬 처리 수는 최대 병렬 처리 수(sp_configure로 설정한), MAXDOP 인덱스 옵션, 현재 작업부하의 크기, 파티션되지 않은 경우, 첫 번째 키 칼럼의 데이터 분포등에 의해서 결정될 수 있다.

Example:

Create index idx_t ON t(c1, c2)

WITH (MAXDOP = 2)

-- 인덱스 구성에 2개의 프로세서 사용

온라인 인덱스 가능한 SQL 버전
Microsoft SQL Server 2005 Enterprise Edition

온라인 인덱스는 어떻게 동작 할까?

인덱스 DDL(데이터 정의 언어) 작업 중에 동시 사용자 작업이 가능하도록 하려면 온라인 인덱스 작업 중에 원본 및 기존 인덱스, 대상 및 임시 매핑 인덱스(클러스터형 인덱스의 경우) 로 구성됨으로써 온라인 인덱스 작업 수행을 할수 있도록 합니다.

원본 및 기존 인덱스
원본은 원래 테이블이거나 클러스터형 인덱스 데이터입니다. 기존 인덱스는 원본 구조와 관련된 모든 비클러스터형 인덱스입니다. 예를 들어 온라인 인덱스 작업이 네 개의 관련 비클러스터형 인덱스가 있는 클러스터형 인덱스를 재구성하는 것이라면 원본은 기존 클러스터형 인덱스이고 기존 인덱스는 비클러스터형 인덱스입니다.
기존 인덱스는 여러 사용자의 동시 선택, 삽입, 업데이트 및 삭제 작업에 사용됩니다. 여기에는 대량 삽입(지원되지만 권장하지 않음)과 트리거 및 참조 무결성 제약 조건에 의한 암시적 업데이트가 포함됩니다. 쿼리와 검색에 모든 기존 인덱스를 사용할 수 있습니다. 즉, 기존 인덱스를 쿼리 최적화 프로그램에서 선택할 수 있으며 필요한 경우 인덱스 힌트에 지정할 수 있습니다.

대상
대상은 만들거나 다시 작성하는 새 인덱스(또는 힙)이거나 새 인덱스 집합입니다. SQL Server 데이터베이스 엔진에서는 인덱스 작업을 수행하는 동안 원본에 대한 사용자 삽입, 업데이트 및 삭제 작업을 대상에 적용합니다. 예를 들어 온라인 인덱스 작업이 클러스터형 인덱스를 다시 작성하는 것이라면 대상은 다시 작성되는 클러스터형 인덱스입니다. 데이터베이스 엔진에서는 클러스터형 인덱스를 다시 작성할 때 비클러스터형 인덱스를 다시 작성하지 않습니다.
대상 인덱스는 인덱스 작업이 커밋될 때까지 SELECT 문을 처리하는 동안 검색되지 않습니다. 내부적으로 인덱스가 쓰기 전용으로 표시됩니다.

임시 매핑 인덱스
클러스터형 인덱스를 만들거나 삭제하거나 다시 작성하는 온라인 인덱스 작업에는 임시 매핑 인덱스도 필요합니다. 이러한 임시 인덱스는 동시 트랜잭션이 기본 테이블의 행이 업데이트되거나 삭제될 때 작성되는 새 인덱스에서 삭제할 레코드를 결정하는 데 사용합니다. 이러한 비클러스터형 인덱스는 새 클러스터형 인덱스(또는 힙)와 같은 단계에서 만들어지므로 별도의 정렬 작업이 필요 없습니다. 동시 트랜잭션의 모든 삽입, 업데이트 및 삭제 작업에서도 임시 매핑 인덱스가 유지됩니다.

참고 사이트

 

http://blogs.msdn.com/sqlqueryprocessing/archive/2006/11/08/index-build-strategy-in-sql-server-introduction-i.aspx

http://technet.microsoft.com/ko-kr/library/ms191261.aspx