인덱스 DDL 작업의 디스크 공간 요구 사항

인덱스 DDL 작업의 디스크 공간 요구 사항

 

• Version : SQL Server 2000, 2005, 2008, 2008R2, 2012

 

디스크 공간은 인덱스를 생성, 다시 작성, 삭제 할 때 고려해야 할 주요 사항이다. 디스크 공간이 부족하면 성능이 저하되거나 인덱스 작업이 실패 할 수도 있다.

인덱스를 생성할 때 고려해야 할 일반적인 사항을 정리하여 보자.

 

[인덱스 작업 시 추가 공간 불필요]

• DROP INDEX, 넌클러스터 인덱스를 삭제할 때 추가 공간 불필요.
• DROP INDEX, 넌클러스터 인덱스가 없는 경우 MOVE TO 절을 지정하지 않고 오프라인으로 클러스터형 인덱스를 삭제 할 때 추가 공간 불필요.

 

[인덱스 작업 시 추가 공간 필요]

• CREATE INDEX
• CREATE INDEX WITH DROP_EXISTING
• ALTER INDEX REBUILD
• ALTER TABLE ADD CONSTRINAT(PRIMARY KEY or UNIQUE)
• ALTER TABLE DROP CONSTRAINT(PRIMARY KEY or UNIQUE)(제약조건이 클러스터 인덱스를 기반으로 하는 경우)
• DROP INDEX MOVO TO(클러스터 인덱스에만 적용)

 

새 인덱스 구조가 생성되면 해당 파일과 파일 그룹에 기존(원본)구조와 새(대상)구조를 위한 디스크 공간이 모두 필요하다. 기존 구조는 인덱스 생성 트랜잭션이 커밋된 후 할당 취소 된다.

 

 

[정렬을 위한 임시 디스크 공간]

쿼리 최적화 프로그램에서 정렬이 필요한 경우 정렬을 위한 임시 디스크 공간이 필요하다. 정렬이 필요한 경우 한 번에 한 인덱스만 정렬된다. 예를 들어 단일 문 내에서 클러스터 인덱스와 넌클러스터 인덱스를 다시 작성 할 때 인덱스는 차례대로 정렬되기 때문에 필요한 추가 공간은 가장 큰 인덱스 크기만큼만 필요하다. 대부분 클러스터 인덱스가 가장 크다.

 

[온라인 인덱스 작업을 위한 임시 디스크 공간]

온라인으로 클러스터형 인덱스를 생성, 다시 작성 또는 삭제할 경우 기존의 북마크를 새로운 북마크로 매핑하기 위하여 임시 넌클러스터형 인덱스가 생성 된다. 온라인 인덱스 작업은 행 버전 관리를 사용하여 다른 트랜잭션에서 수정하는 내용의 영향을 받지 않는다. 이미 읽은 행에 대해서는 공유 잠금을 요청할 필요가 없다. 인덱스 작업 중 여러 사용자가 동시에 업데이트 및 삭제 작업을 수행하려면 tempdb의 버전 레코드를 위한 공간이 필요하다.

 

 

[SORT_IN_TEMPDB]

인덱스를 만들거나 리빌드 할 때 SORT_IN_TEMPDB 옵션을 ON으로 설정하면 SQL Server 데이터베이스 엔진에서 tempdb를 사용하여 인덱스를 만드는데 사용되는 중간 정렬 결과를 저장하도록 지시 할 수 있다. 이 옵션을 사용하면 인덱스를 만드는데 사용되는 임시 디스크 공간이 늘어나지만 tempdb가 빠른 디스크에 위치한다면 성능상 이점이 있다.

 

 

[참고 자료]

http://msdn.microsoft.com/ko-kr/library/ms179542(v=sql.110).aspx

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

 

 

인덱스 생성시 원리나 필요한 리소스를 파악하지 못한다면 최악의 상황에는 데이터베이스가 멈추는 상황이 발생 할 수도 있다. 유능한 DBA라면 여러 가지 환경을 검토하여 빠르고 정확하게 작업 할 수 있도록 하자. 또한 다양한 옵션 및 방법을 통하여 효율적인 작업 방법을 찾을 수 있어야 한다.

 

2013-02-07 / 강성욱 / http://sqlmvp.kr