데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

인덱스 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라면 여러 가지 환경을 검토하여 빠르고 정확하게 작업 할 수 있도록 하자. 또한 다양한 옵션 및 방법을 통하여 효율적인 작업 방법을 찾을 수 있어야 한다.



강성욱 / jevida@naver.com

Microsoft SQL Server MVP

Blog : http://sqlmvp.kr

Facebook : http://facebook.com/sqlmvp

 

No. Subject Author Date Views
1730 SQL Server에서 Trigger 활성 / 비활성 감시 jevida(강성욱) 2016.09.14 1466
1729 DDL Trigger를 이용한 데이터베이스 변경 사항 추적 jevida(강성욱) 2016.09.14 1230
1728 Trigger를 이용한 SQL Server 커넥션 풀링 확인 jevida(강성욱) 2016.09.14 1088
1727 SQL Server Trigger jevida(강성욱) 2016.09.14 934
1726 인덱스에 대한 SORT_IN_TEMPDB 옵션 jevida(강성욱) 2016.09.14 846
» 인덱스 DDL 작업의 디스크 공간 요구 사항 jevida(강성욱) 2016.09.14 911
1724 XML nodes() 함수를 이용한 OPENXML 교체 jevida(강성욱) 2016.09.14 945
1723 XQuery를 사용한 XML 데이터 업데이트 jevida(강성욱) 2016.09.14 1871
1722 BCP XML 파일 형식 jevida(강성욱) 2016.09.14 1253
1721 SQL Server로 데이터 가져오기 jevida(강성욱) 2016.09.14 1223
1720 SQL Server Stored Procedure 암호화 jevida(강성욱) 2016.09.14 2676
1719 SQL Server 대칭키 vs 비대칭키 암호화 jevida(강성욱) 2016.09.14 1685
1718 SQL Server 마스터 키 관리 jevida(강성욱) 2016.09.14 1743
1717 대칭키를 사용하여 SQL Server 암호화(열 수준) 하기 jevida(강성욱) 2016.09.13 5640
1716 DMV를 사용하여 누락된 인덱스 확인 jevida(강성욱) 2016.09.13 1341
1715 DMV를 이용한 SQL Server 대기 상태 확인 jevida(강성욱) 2016.09.13 3747
1714 DMV를 이용한 SQL Server 성능 카운터 확인 jevida(강성욱) 2016.09.13 1770
1713 DMV를 이용한 SQL Server IO 성능 모니터 스냅샷 만들기 jevida(강성욱) 2016.09.13 1243
1712 DMV를 활용한 SQL Server 모니터링 jevida(강성욱) 2016.09.13 1258
1711 세션에 따른 캐시된 쿼리 플랜 설정 확인 jevida(강성욱) 2016.09.13 866





XE Login