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

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

온라인 인덱스 리빌드와 조각화 증가

 

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

 

데이터베이스를 설계하는 데 있어서 인덱스는 매우 중요하다. 처음 인덱스를 잘 설계 하더라도 운용하는 과정에서 데이터의 입력, 수정, 삭제 등으로 인하여 조각화가 발생하는 경우 성능이 저하 될 수 있다. SQL Server에서는 이러한 단편화를 제거하기 위해 인덱스 리빌드 라는 기능을 제공하고 있다.

 

SQL Server Standard 에디션에서는 인덱스를 생성하고 다시 빌드하는 과정에서 테이블이 잠김으로 작업이 끝날 때 까지 SELECT, INSERT, UPDATE 작업이 원활하지 않을 수 있다.

SQL Server Enterprise Edition을 사용할 경우 온라인 방식으로 인덱스를 생성, 빌드 또는 삭제할 수 있으며 인덱스 작업중에도 다른 동시 사용자가 기본 테이블 또는 클러스터형 인덱스 데이터 및 기타 관련된 비클러스터형 인덱스를 액세스 할 수 있다.

 

[온라인 인덱스 작업]

온라인 인덱스 작업을 하면 SQL Server에서는 인덱스의 복사본을 두 개로 유지하며 원본은 원래의 인덱스 또는 테이블이며 복사본은 다시 빌드 하는데 사용된다. 인덱스 작업 도중 테이블 또는 인덱스의 데이터가 수정된 경우 원래 인덱스 또는 테이블의 이전 스냅숏과 대상 모두에 수정 내용이 적용된다. 작업이 완료되면 이전 버전의 인덱스 또는 테이블이 삭제되고 새 버전으로 대체 된다.

 

온라인 인덱스는 작업 성능이 아닌 가용성을 위해서 설계된 만큼 서비스 중에 사용할 수 있는 장점이 있지만 오프라인의 인덱스 작업보다는 속도가 느린 단점이 있다.

 

[온라인 인덱스 작업 중 조각화]

CSS SQL Server Engineer 팀블로그에 게제된 내용으로 온라인 인덱스 작업과 조각화에 대한 이슈이다. 필자가 이해한 내용을 바탕으로 정리하였으며 번역의 오류나 기술적 오류가 있음을 인지 한다.

 

우리는 인덱스 리빌드를 수행 하는 몇 가지 이유가 있다.

  • 새로운 통계를 생성
  • 인덱스로 사용된 페이지 수 비율(fill factor) 변경
  • 데이터베이스에서 서로에 근접한 페이지 데이터 재구성

 

인덱스를 리빌드시 참여하는 프로세스는 다음과 같이 maxdop 명령을 사용하여 강제 할 수 있다. 예시 스크립트에서는 8개의 프로세스가 인덱스 작업에 참여 한다.

Alter Index all On test2 Rebuild With (Online = On, maxdop = 8)

 

 

[MAXDOP = 1 (Serial)]

온라인 인덱스 작업이 직렬화로 실행 될 때 새 행 집합(new rowset)의 할당 캐시는 새 페이지를 할당하고 새 행 집합(new rowset)에 데이터를 이동하는 것을 유지 관리 한다. 이 프로세스는 가까이 있는 페이지의 데이터를 패키지 한다. 근처의 정렬된 순서로 행을 배치하고 페이지 수를 줄인다.

 

 

[MAXDOP ALLOW_PAGE_LOCKS = OFF > 1 (Parallel)]

병렬로 실행할 때 ALLOW_PAGE_LOCKS = OFF 의 경우 단일 할당 캐시를 공유 한다.

 

이러한 leap frog style은 할당 조각화를 증가 시킨다. 인접한 할당된 인덱스 페이지는 100, 101, 102, 103…이 있을 수 있다. 그러나 페이지의 데이터를 스캔 할 때 페이지100(파티션 1), 101(파티션2), 102 (파티션 1)로 IAM 페이지 순서대로 페이지 조각화 수준이 올라갈 수 있다.

 

 

[MAXDOP ALLOW_PAGE_LOCKS = ON > 1 (Parallel)]

ALTER 인덱스가 잠금 페이지 또는 테이블(집합 레벨) 사용할 수 있게 될 때 할당 패턴은 대량 작업에 최적화 되어 있다. 대량 작업의 경우 추가 캐싱 계층 작업이 사용된다.

 

대량 할당 캐시는 주어진 파티션 등 예상 작업 부하에 따라 조정 된다. 페이지에 데이터 저장을 위해 각 파티션에 한번에 1개 이상의 익스텐트를 할당 할 수 있다. leap frog style을 줄이기 위해 필요한 분리 위험 수준을 제공하고 단편화를 줄일 수 있다.

 

 

 

[참고자료]

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

http://blogs.msdn.com/b/psssql/archive/2012/09/05/how-it-works-online-index-rebuild-can-cause-increased-fragmentation.aspx

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

 

 


강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1831 SQL Server CREATE TABLE syntax diagrams jevida(강성욱) 2016.09.30 1421
1830 SQL Server 2012 Sp1 설치 이슈 및 해결 jevida(강성욱) 2016.09.30 1582
1829 SSD에서 DBCC CHECKDB 성능 벤치마킹 jevida(강성욱) 2016.09.30 1932
1828 Collation에 따른 ALTER DATABASE 실패 jevida(강성욱) 2016.09.30 1942
1827 LDF 파일이 잘리지 않는 이유 jevida(강성욱) 2016.09.30 2054
1826 Tempdb 경합 확인 및 해결 (Tempdb Contention) jevida(강성욱) 2016.09.30 2222
1825 SQL Server IO 병목 확인과 오해 jevida(강성욱) 2016.09.30 2512
1824 SQL Version에 따른 sp_prepare 정보 반환 jevida(강성욱) 2016.09.30 1717
1823 DReplay 활성 세션 초과 에러 jevida(강성욱) 2016.09.30 1543
1822 저장 프로시저 내 임시 테이블 사용과 프로시저 재컴파일 jevida(강성욱) 2016.09.30 1872
1821 NUMA 노드와 추척플래그 8048 jevida(강성욱) 2016.09.30 820
» 온라인 인덱스 리빌드와 조각화 증가 jevida(강성욱) 2016.09.30 1608
1819 VARCHAR(MAX) and NTEXT 쿼리 성능 jevida(강성욱) 2016.09.30 4579
1818 CPU 리소스 상태에 따른 병렬 처리 제한 jevida(강성욱) 2016.09.30 1077
1817 매개변수 값의 변경과 SQL 서버 성능 저하 jevida(강성욱) 2016.09.30 1273
1816 IN 절 사용시 예기치 못한 액세스 위반과 SQL Server 종료 jevida(강성욱) 2016.09.30 1060
1815 SQL 버전과 CLR (.NET Framework 버전에 따른 오류) jevida(강성욱) 2016.09.30 1191
1814 SWITCHOFFSET 내장함수의 잘 못된 예측 - 미리 계산한 값을 쿼리에 연결하여 최적화 하기 jevida(강성욱) 2016.09.30 1331
1813 테이블 반환 매개변수 사용과 SQL 2012의 향상된 캐싱 기능 jevida(강성욱) 2016.09.30 1233
1812 디스크 섹터 크기와 데이터베이스 성능 jevida(강성욱) 2016.09.29 1638





XE Login