김정선의 좋은 글을 찾아서……
SQL Server 인덱스 구성 전략(시리즈-1. 소개)
|
|
김정선(jskim@feelanet.com)
필라넷 DB사업부 수석컨설턴트
SQLServer 아카데미/트라이콤 교육센터 강사
Microsoft SQL Server MVP MCT/MCITP/MCDBA |
저자: SQL Server Query Processing Team 블로그
원본: 원본은 아래와 같이 총 9개의 포스트입니다만, 번역은 주제별로 나누어 총 4개의 포스트로 올리겠습니다.
(첫 번째-소개)
http://blogs.msdn.com/sqlqueryprocessing/archive/2006/11/08/index-build-strategy-in-sql-server-introduction-i.aspx
http://blogs.msdn.com/sqlqueryprocessing/archive/2006/11/09/index-build-strategy-in-sql-server-introduction-ii.aspx
(두 번째-일반 인덱스)
http://blogs.msdn.com/sqlqueryprocessing/archive/2006/11/20/index-build-strategy-in-sql-server-part-1-offline-serial-no-partitioning.aspx
http://blogs.msdn.com/sqlqueryprocessing/archive/2006/12/11/index-build-strategy-in-sql-server-part-2-offline-parallel-no-partitioning.aspx
http://blogs.msdn.com/sqlqueryprocessing/archive/2006/12/13/index-build-strategy-in-sql-server-part-2-offline-parallel-no-partitioning-non-stats-plan-no-histogram.aspx
(세 번째-정렬된 파티션 인덱스)
http://blogs.msdn.com/sqlqueryprocessing/archive/2007/01/16/index-build-strategy-in-sql-server-part-3-offline-serial-parallel-partitioning.aspx
http://blogs.msdn.com/sqlqueryprocessing/archive/2007/01/19/index-build-strategy-in-sql-server-part-3-offline-serial-parallel-partitioning-aligned-partitioned-parallel-index-build.aspx
(네 번째-정렬되지 않은 파티션 인덱스)
http://blogs.msdn.com/sqlqueryprocessing/archive/2007/05/08/index-build-strategy-in-sql-server-part-4-1-offline-serial-parallel-partitioning-non-aligned-partitioned-parallel-index-build.aspx
http://blogs.msdn.com/sqlqueryprocessing/archive/2007/05/13/index-build-strategy-in-sql-server-part-4-2-offline-serial-parallel-partitioning-non-aligned-partitioned-index-build.aspx
역자 서문
이번엔, Query Processing Team 블로그에 올라온 포스트입니다. 역시 좀 지난 글이긴 하지만 내용이 너무 좋습니다. 에전에 한 번 제목만 보고 스~윽 지나쳤던 글인데, 다시 보니 다른 곳에서는 보기 힘든 유용한 내용들로 소개되고 있었습니다.
사실 저로서도 조금 힘들만큼 어려운 내용들을 설명하고 있습니다. 모호한 내용도 있는 것 같습니다. 직접적으로 확인하기 어려운 내용이기도 하구요. SQL Server 인덱스에 대해서 자세히 모른다면 이 내용을 읽고 이해하기가 쉽지는 않을 것입니다
그런데 왜 이 포스트 시리즈를 선택했냐구요?
SQL Server가 인덱스를 어떻게 만드는지? 병렬 처리를 어떻게 하는지? 얼만큼의 메모리와 디스크를 요구하는지? 파티션 테이블과 인덱스는 또 어떻게 처리되는지? 정렬된 파티션 인덱스와 그렇치 않은 경우에는 또 어떻게 달라지는지? 그래서 어떻게 작업하는 것이 상황 별로 가장 좋을지에 대한 중요한 힌트를 얻을 수는 내용들이 포함되어 있습니다.
그 결론만 참고하셔도 실무에서 아주 유용하게 활용될 수 있는 내용입니다.
앞에서 분류된 대로, 총 9개의 포스트를 주제별로 묶어서 4개의 글로 올리겠습니다.
시리즈-1. 소개
시리즈-2. 일반 인덱스
시리즈-3. 정렬된(Aligned) 파티션 인덱스
시리즈-4. 정렬되지(Non-aligned) 않은 파티션 인덱스
그럼, 우선 소개 부분부터 올립니다.
소개(I)
SQL Server의 인덱스 구성 전략은 사용자의 요구에 따라 다양하다. 그 전략에 따라 서로 다른 메모리와 디스크 공간을 요구한다. 이러한 전략들에 대해서 살펴볼 것이다.
우선 SQL Server 2005의 어떤 종류의 인덱스 구성 방식이 있는 보자.
온라인 인덱스 구성 vs. 오프라인 인덱스 구성
SQL Server 2005에서는 온라인으로 인덱스의 만들기, 재구성, 삭제 작업이 가능하다. ONLINE 옵션은 이러한 인덱스 작업을 수행하는 동안에도 사용자가 테이블이나 클러스터형(Clustered) 인덱스와 관련된 비클러스터형(Nonclustered) 인덱스에 접근하는 것이 허용한다. 오프라인으로 클러스터형 인덱스를 구성하거나 재구성하는 등의 DDL 작업을 하면 이는 해당 데이터와 관련 인덱스에 배타적 잠금을 보유하게 되고 이로 인해 다른 사용자가 데이터나 인덱스에 접근하지 못하도록 방해하게 된다.
Example:
Create index idx_t ON t(c1, c2)
WITH (ONLINE = ON)
직렬 인덱스 구성 vs. 병렬 인덱스 구성
다중 프로세서를 가진 컴퓨터에서 인덱스 구문 또한 다른 쿼리를 실행할 때처럼, 스캔, 정렬, 그리고 구성 작업을 수행하는데 병렬 처리가 가능하다. 병렬 처리 수는 최대 병렬 처리 수(sp_configure로 설정한), MAXDOP 인덱스 옵션, 현재 작업부하의 크기, 파티션되지 않은 경우, 첫 번째 키 칼럼의 데이터 분포등에 의해서 결정될 수 있다.
Example:
Create index idx_t ON t(c1, c2)
WITH (MAXDOP = 2)
-- 인덱스 구성에 2개의 프로세서 사용
사용자 데이터베이스 사용 vs. tempdb 사용
인덱스 구성/재구성 작업 시 일반적으로 중간 단계에서 생성되는 정렬 결과를 저장하기 위해서 해당 사용자 데이터베이스를 사용하거나 tempdb 데이터베이스를 사용할 수 있다. 후자의 경우 SORT_IN_TEMPDB 인덱스 옵션으로 지정할 수 있다. 디폴트로 OFF로 설정되면, 정렬 결과는 해당 파일 그룹이나 파티션 스킴(scheme)에 저장된다.
(역주: 뒤에서 논의하겠지만, 직렬/병렬, 파티션/일반 테이블 등등의 따라서 이 옵션의 사용으로 인한 영향력은 달라지게 된다. 따라서 관심 있는 살펴볼 필요가 있는 옵션이다)
Example:
Create clustered index idx_t ON t(c1)
WITH (SORT_IN_TEMPDB = ON)
소개(II)
파티션(Partitioned) 인덱스 구성 vs. 일반 인덱스(Nonpartitioned) 구성
파티션 테이블과 인덱스는 데이터를 하나 이상의 파일 그룹으로 분리시켜 저장한다. 데이터가 수평 분할이 되므로, 행 그룹 단위로 개별 파티션(분할)에 놓이게 된다. 해당 테이블과 인덱스 데이터에 대해 쿼리나 수정이 일어날 때는 하나의 테이블로서 다루어진다. 단일 인덱스와 테이블에 모든 파티션은 동일 데이터베이스에 존재해야 한다.
정렬된 파티션 인덱스 구성하기:
파티션 인덱스가 종속된 해당 테이블과는 별도로 구현될 수 있지만, 일반적으로는 파티션 테이블을 설계하고 나서 그 테이블에 인덱스를 생성하는 것이 적합하다. 이 경우 SQL Server는 인덱스를 구성할 때 자동적으로 해당 테이블과 동일한 파티션 스킴과 파티션 열을 사용해서 파티션 인덱스로 만들어준다. 그 결과 테이블과 동일한 방식의 파티션 되는 것이다. 이것이 테이블과 인덱스가 정렬(Aligned, 혹은 맞춤)되는 것이다.
인덱스가 반드시 해당 테이블과 동일한 이름의 파티션 함수를 사용할 필요는 없다. 그러나, 다음 항목들은 동일해야 한다
- 파티션 함수의 인수는 동일 데이터 형식
- 동일 파티션 수
- 동일한 파티션 경계 값
또한 파티션 테이블 혹은 클러스터형 인덱스 상에서 비클러스터형 인덱스를 구성하면 파티션 함수를 지정하지 않아도 정렬된 파티션으로 구성된다.
Example:
Create Partition Function pf (int)
as range right for values (NULL, 1, 100)
Create Partition Scheme ps
as Partition pf
TO ([PRIMARY], [FileGroup1], [FileGroup1], [FileGroup1])
Create table t (c1 int, c2 int)
on ps(c1)
Create Index idx_t on t(c1)
일반 인덱스(파티션되지 않은) 구성:
인덱스 생성 시 다른 파티션 스킴이나 개별 파일 그룹을 지정하면 테이블에 정렬되지 않은 인덱스로 만들어진다. 일반 테이블에 파티션 클러스터형 인덱스를 만들어서 파티션 테이블로 바꿀 수도 있다. 물론 이 경우 정렬 인덱스를 아니다.
Example:
Create Partition Function pf (int)
as range right for values (NULL, 1, 100)
Create Partition Scheme ps
as Partition pf
TO ([PRIMARY], [FileGroup1], [FileGroup1], [FileGroup1])
Create table t (c1 int, c2 int)
Create clustered Index idx_t on t(c1)
on ps(c1)
참고. 기존 파티션 클러스터형 인덱스를 삭제하는 경우, 새로운 Heap 테이블은 그대로 파티션 상태로 남게되는 것이며 동일 파티션 스킴이나 파일 그룹에 위치하는 것이다. 이 경우 원한다면 MOVE TO 옵션을 사용해서 그 위치를 조정할 수 있다.
Example:
Drop Index idx_t on t
WITH(MOVE TO new_ps(c1))
----------------------------------------------------
여기까지입니다.
소개 부분이라 특별한 이슈는 보이지 않으시죠? 준비 운동 정도로 생각하셔도 될 듯.
다음 주제는,
시리즈-2. 일반 인덱스
입니다. 기다려 주세요~~~
