MSSQL 인덱스 조각모음 [Microsoft SQL Server 2000 Index Defragmentation Best Practices]

---

---

Microsoft SQL Server 2000 Index Defragmentation Best Practices

 

http://technet.microsoft.com/en-us/library/cc966523.aspx

 

Overview

 

이 문서에서는 어떤 경우에 인덱스 Defragmentation을 해야 성능상의 이점을 가질 수 있는지와 MSSQL 2000 에서 인덱스 Defragment에 사용되는 DBCC DBREINDEX와 DBCC INDEXDEFRAG 두 개의 명령어를 비교할 것이다. 이 비교 작업은 두 개의 다른 데이터베이스와 다른 환경(소규모 데이터베이스와 대규모 데이터베이스)에서 테스트한 결과를 보여줄 것이다.

 

테스트 환경

 

Microsoft Software

Microsoft Windows 2000 Data Center (Service Pack 3)

Microsoft SQL Server 2000 Enterprise Edition (Service Pack 2)

 

Hardware Platform

Small-Scale Environment

- Dell PowerEdge 6450

- 4 Intel Pentium III Xeon 550 MHz processors

- 4 GB RAM

Large-Scale Environment

- Dell PowerEdge 8450

- 8 Intel Pentium III Xeon 550 MHz processors

- 16 GB RAM

Storage

Small-Scale Environment

- 1 Dell PowerVault 660f, with 2, 18 GB 10,000 RPM disks

- Total Disk Space = 36 GB (Raid 0)

Large-Scale Environment

- 1 Hitachi Freedom Storage Lightning 9960 system, with 192, 73 GB, 10,000 RPM disks

- Total Disk Space = 13 TB (~6 TB after RAID 1+0 and further striping/slicing)

 

Host bus adapters (HBA)

8 Emulex LP9002L PCI Host Bus Adapters

Firmware 3.82A1

Port Driver v5-2.11a2

 

Fabric switch

1 McData Switch, 1 GB

 

Storage management software

Hitachi Command Control Interface (CCI)

Hitachi ShadowImage

 

Databases

Representative OLTP and DSS databases

 

 

Note : Defragment 작업이 항상 모든 경우에 성능을 개선시켜 주지는 않는다. 모든 시나리오에 따라 각기 다르기 때문에 우선 Defragmentation 작업을 해야되는지 분석할 필요가 있다.

 

Summary of Key points

 

- 인덱스 Defragment 작업을 하기 전에 물리적 디스크의 단편화나 혹은 부적절한 스키마 구조로 인한 성능 저하가 아닌지 확인하지 않으면 오히려 역으로 성능에 좋지 않은 결과를 초래하게 될 것이다.

- DBCC SHOWCONTIG를 사용하여 인덱스의 단편화 정보들 확인하고 결정해야 한다. 특히 논리적 단편화(Logical Fragmentation - 익스텐트 검색 조각화 상태)와 페이지 밀도(Page Density - 평균 페이지 밀도(전체) ) 항목을 유의깊게 살펴보자.

- 인덱스 defragment 작업을 할 때는 작업 유형을 가장 우선시로 고려해야 한다. 모든 작업 유형에 defragment가 이득을 안겨 주지는 않는다. 읽기 작업이 빈번하게 일어나는 작업 유형의 경우에는 확실하게 디스크 I/O의 성능 개선이 있을 것이다. 테스트 결과를 보면 OLTP(Online Transaction Processsing) 형태보다 DSS(Decision Support System) 형태에서 더 많은 성능 개선이 이루어진 것을 볼 수 있을 것이다.

- 단편화는 디스크 성능에 영향을 주고, SQL 서버의 read-ahead manager에 영향을 준다. 윈도우 성능 모니터링에서 이 항목을 확인할 수 있다.

- DBCC DBREINDEX를 사용할 지 아니면 DBCC INDEXDEFRAG를 사용할 지는 하드웨어 환경에 맞춰 선택해야 한다.

- DBCC DBREINDEX의 경우에는 통계 정보를 업데이트 해야하는 부작용이 있지만, DBCC INDEXDEFRAG는 통계 정보를 업데이트 할 필요가 없다.

 

Fragmentation 이해하기

 

단편화는 인덱스가 키의 값을 기준으로 정렬된 논리적 순서가 데이터 파일 안에 물리적 순서랑 일치하지 않는 경우에 나타난다. 모든 인덱스의 리프 페이지(leaf page)는 인덱스의 다음 페이지와 이전 페이지의 위치(pointer)를 포함하고 있다. 모든 인덱스/데이터 페이지는 이중 링크드 리스트 형태로 저장되어 있다. 데이터 파일의 물리적 순서와 논리적 순서가 일치하는 것이 이상적이다. 전체적 디스크 처리량은 데이터의 물리적 순서와 논리적 순서가 일치할 때 확실하게 증가한다. 이는 쿼리의 실행 속도를 빠르게 만들어준다. 물리적 순서와 논리적 순서가 일치하지 않는 경우에는 디스크 헤드가 인덱스 페이지를 검색하기 위해 한 방향이 아닌 앞 뒤로 움직여야 하기때문에 더 비효율적이다. 단편화는 디스크 I/O성능에 이슈를 주지만, SQL 서버의 데이터 캐쉬에서 존재하는 데이터를 가져오는 쿼리는 영향을 받지 않는다.

 

인덱스가 처음 생성되고 나면 단편화는 거의 존재하지 않는다. 하지만, 시간이 지나 데이터가 삽입, 업데이트, 삭제 작업이 반복되면 단편화 정도가 증가하게 된다. 이 단편화를 수정하기 위해 SQL 서버는 다음 구문들을 제공한다.

 

- DROP INDEX followed by CREATE INDEX

- CREATE INDEX WITH DROP_EXISTING

- DBCC INDEXDEFRAG

- DBCC DBREINDEX

 

DBCC INDEXDEFRAG 와 DBCC DBERINDEX 둘 다 이 문서에 테스트 하였다. 이 구문들은 온라인 오프라인 시나리오 둘 다 허용한다. DBCC DBREINDEX는 CREATE INDEX와 동일하게 작동한다. 그러므로 CREATE INDEX를 사용한 결과랑 DBCC DBREINDEX를 사용한 결과랑 거의 같다고 보면된다.

 

Defragmenting 전 고려 사항

 

시스템 리소스 이슈

인덱스 Defragment 작업을 하기 전에 먼저 시스템 리소스 제한으로 인해 발생하는 성능 저하가 아닌지 체크해봐야 한다. I/O에 관련된 대부분 이슈들은 메모리 사용량, CPU 최적화 등에 관련이 있다. 더 깊은 정보를 원하는 사람은 추가 정보에 나온 글들을 읽어보기를 바란다.

 

물리적 디스크 단편화

물리적 디스크가 단편화된 경우도 시스템 성능에 안 좋은 영향을 준다. 물리적 디스크 단편화가 존재하는지 Microsoft Windows 에서 제공하는 툴이나 Third Party Tool을 사용하여 확인하여야 한다. 소규모의 데이터베이스 경우 I/O 서브시스템이 더 평범하기 때문에 반드시 인덱스 조각 모음을 하기 전에 물리적 디스크 단편화를 체크하기 바란다. 대규모 데이터베이스의 경우 SAN(Storage area Networks) 환경 등으로 더 잘 설계된 경우가 많기 때문에 물리적 단편화 체크가 불필요할 수도 있다.

 

성능이 안 좋은 쿼리

성능 이슈에 관한 문제를 조사할 때, 반드시 성능이 좋지 않은 쿼리를 확인할 필요가 있다. 이 문서는 defragmentation에 관한 글이기 때문에 더 깊이 얘기하지는 않겠다.

 

여러분은 SQL Profiler를 사용하여 성능이 좋지 않은 쿼리를 찾을 수 있을 것이다.(자세한 내용을 원하는 사람은 SQL Server Books Online에서 SQL Profile 관련 글을 읽어보기 바란다) SQL Profiler를 실행하는 경우 부하를 주기 때문에 성능에 최대한 영향을 주지 않게 수집할 필요가 있다. SQL Profiler는 SQLProfilerTSQL_Duration 이라는 템플릿을 제공하는데 이를 사용하여 성능이 좋지 않은 쿼리를 빠르게 찾을 수 있다. 아울러 다음 이벤트들도 트레이스

하는 것이 좋다.

* TSQL:SQLBatchCompleted

* Stored Procedures:RPC:Completed

 

서버의 성격에 따라 SQL Profiler를 돌리는 시간도 결정해야한다. 트레이스를 캡쳐한 다음에는 duration 컬럼에 초점을 맞추어 분석을 한다. duration 값은 배치 작업이나 쿼리의 실행 시간을 milliseconds 로 측정한다.

 

가장 성능이 좋지 않은 쿼리 찾아내기

트레이스를 캡쳐하여 가장 성능이 좋지 않은 쿼리를 찾아내는 방법을 제안하도록 하겠다.

 

- 쿼리 실행시간(duration)을 기준으로 그룹을 만든다. 그 중 최상의 10개를 추출해낸다.

- 만일 여러분의 시스템이 저장 프로시저를 많이 사용한다면, SQLProfilerSP_Counts 항목을 통해 저장 프로시저가 호출되는 횟수를 측정할 수 있다. 성능이 좋지 않으며 가장 많이 호출되는 저장 프로시저에 초점을 두자.

- SQL 서버 테이블안에 데이터가 축적되는 시간을 절약하고 싶다면, 평균 실행 시간(average duration), 최대 실행 시간(max duration) 등 세부사항들을 분석하도록 한다.

 

근본적인 스키마 문제

성능이 좋지 않고 가장 오래 실행되는 쿼리들을 찾은 다음에는 근본적인 스키마 구조가 최상의 구조인지 확인해야 한다. 적절한 인덱스가 존재하고 쿼리에서 알맞게 사용하고 있는지 확인해야 한다. SQL Query Analyzer 를 사용하여 실행 계획을 통해 쿼리가 어떤 인덱스를 사용하며 작업량이 얼마나 되는지 미리 확인해야 한다. SQL Query Analyzer 에서 그래픽으로 볼 때는 통계 정보가 갱신되지 않아 경고가 발생할 수 있다. defragmenting을 하기 전에 통계 정보를 갱신해서 해결해야 한다. 

 

실행 계획을 볼 때 유의해야 할 제안 사항.

 

- 실행 계획 단계를 분석할 때, 가장 부하가 많이드는 부분을 확인하자. 쿼리에서 가장 많은 부하를 갇고 있는 부분을 개선해야 그 만큼 많은 성능 개선 효과를 볼 수 있다.

- Index Scan을 수행하는 단계를 찾자. 인덱스 스캔의 경우 defragmenting으로부터 가장 많은 효과를 본다. 인덱스 스캔을 하는 단계의 성능 부하가 많아 수행이 느린 경우 해당 부분에 초점을 맞춰 인덱스 defragment를 수행하면 된다.

 

SQL Profiler 를 통해 트레이스 하는 것과 실행 계획을 분석하는 방법과 더불어 Index Tunning Wizard 를 사용하여 작업 부하를 분석할 수 있다. Index Tunning Wizard의 보고서를 사용하여 기존에 존재하는 스키마에서 어떤 것을 변경해야 하는지 결정할 수 있다. 인덱스 defragment를 하기 전에 변경이 필요한 스키마는 수정하도록 하자.

 

소규모 환경 VS 대규모 환경

이 테스트는 두 개의 다른 I/O서브시스템을 가지고 있는 서버에서 진행하였다. 하나는 소규모 환경을 대표하는 구성이고 다른 하나는 대규모 환경을 대표하는 구성이다. 테스트 결과는 각기 다른 두 환경에서의 결과값을 비교해서 보여줄 것이다.

 

소규모 환경

소규모 환경에서의 구성은 데이터베이스는 10 ~ 20GB의 사이즈를 가진다. 데이터는 두 개의 물리적 장치에 퍼져 저장되어 있고, 데이터베이스 로그 tempdb는 추가적인 두 개의 물리적 장치에 RAID 0 으로 구성되어 있다. DSS(Decision Support System)을 위한 데이터베이스 구성은 두 개의 파일 그룹에 하나의 데이터 파일을 가지도록 구성되어 있으며, OLTP 데이터베이스의 구성은 하나의 파일 그룹에 하나의 데이터 파일을 가지도록 구성되었다.

 

대규모 환경

대규모 환경은 히타치의 SAN Hitachi Freedom Storage Lightning 9900 Series Lightning 9960 시스템을 사용하였다. 테스트를 위한 데이터베이스 사이즈는 대략 1TB이다. 데이터는 64개의 물리적 장치에 RAID 1+0으로 구성되어 있다. 물리적 장치들은 8개의 LUN(Logical Unit Number)를 통해 연결되었으며, 하나의 파일 그룹에 8개의 데이터 파일들로 구성되어 있다. tempdb 는 데이터와 분리된 장치들에 구성하였으며 48개의 물리적 장치에 퍼져있고, 로그는 8 개의 물리적 장치에 연결되어 있다. 빠른 백업과 복구를 위해

데이터베이스 이미즈를 Hitachi ShadowImage 복사를 이용하여 SAN에 데이터/로그를 저장한다. 그리고 Lightning 9960을 사용하여 데이터를 ShadowImage의 소프트웨어 복사와 Production Copy를 재동기화 시킨다. 대규모 환경에서는 테스트 반복을 위해 2/3가 단편화되어 있는 상태로 복사되어 유지된다. 대략 1.4TB

 

인덱스 단편화에 따른 성능 영향

테스트 결과에 대해서는 후반부에 자세히 이야기하겠지만, 인덱스 단편화가 두 환경에서 모두 불리한 영향을 주는 것은 아니다. 소규모 환경보다는 대규모 환경에서 확실히 덜 불리한 영향을 끼칠것이다. 이 결과에서 대규모 환경 구성은 SAN 스토리지의 I/O 성능과 SAN에서 제공되는 16GB의 데이터 캐시로 인해 더 많은 이점을 가지고 있다. I/O 벤치마크 결과도 최대 읽기 시간이 소규모 환경의 71MB/sec 보다 354MB/sec 로 대규모 환경이 더 빠르게 동작한다.

 

분명 SQL 서버 성능은 I/O 서브 시스템의 성능에 영향을 많이 받지만, 인덱스 조각모음을 통해서도 성능을 개선시킬 수 있는 부분이 있다. 가능한 데이터베이스를 구성할 때 I/O서브 시스템을 데이터와 로그 파일을 다른 물리적 장치에 분리하도록 유의하여 구성해야 한다.

 

언제 인덱스 조각모음을 해야되는가?

인덱스 조각모음(Defragment)을 해야되는지 결정하기 위해서는 다음 사항을 고려해보도록 하자.

 

- 인덱스 단편화 정도 확인하기

- 조각모음을 하였을 경우 어떤 작업 유형에서 이득을 볼 수 있는지 이해하기

- 쿼리 수행시 I/O 양에 대해 결정하기

- SQL 서버의 먼저 읽기 매니저와 디스크 처리량에서 단편화에 따른 영향 이해하기

 

DBCC SHOWCONTIG 를 사용하여 인덱스 단편화 정도 확인하기

조각모음을 할 지 결정할 때, 먼저 인덱스 단편화 정도를 확인하여야 한다. DBCC SHOWCONTIG를 사용하여 인덱스 레벨의 단편화 정도와 페이지 밀도를 확인할 수 있다.

 

 

 

 

DBCC SHOWCONTIG 결과를 분석할 때, 특히 논리 스캔 조각화 상태(Logical Scan Fragmentation)과 평균 페이지 밀도(Avg. Page Density (full) )를 유의하여 살펴봐야 한다.논리 스캔 조각화 상태는 인덱스가 정리가 안된 상태의 비율이다. (주의 : 이 값은 힙에 올라가 있는 텍스트 인덱스의 경우에는 무의한 값이다. 힙에는 테이블의 클러스터드 인덱스가 없다.) 평균 페이지 밀도는 인덱스의 리프 페이지가 가득차있는지에 대한 측정값이다. 더 많은 정보가 필요할 경우 SQL Server Books Online에서 DBCC SHOWCONTIG 항목을 보기바란다.

 

DBCC SHOWCONTIG 결과값 분석하기

DBCC SHOWCONTIG 결과값을 분석할 때 다음 항목에 유의하도록 하자.

 

- 단편화는 distk I/O에 영향을 미친다. 그러므로 SQL Server 의 캐쉬처럼 덜 영향을 받는 페이지도 있기 때문에 인덱스 용량이 큰 부분에 초점을 맞춰야 한다. DBCC SHOWCONTIG를 실행하였을 때 나오는 페이지 카운트를 가지고 인덱스 사이즈를 구할 수 있다. 각 페이지는 8KB의 사이즈를 가지고 있다. 일반적으로 1,000 페이지 이하의 경우에는 인덱스 단편화를 걱정할 필요는 없다. 이 테스트에서는 최소 10,000 페이지 이상은 되야 성능 향상을 얻을 수 있었으며, 사이즈가 클 수록 (50,000 페이지 이상인 경우)더 확실한 개선 효과를 가질 수 있다.

 

- 논리 스캔 조각화 상태 값이 높을수록 인덱스 스캔에 악영향을 준다. 이번 테스트에서는 클러스터드 인덱스의 경우 10% 이상일 때, 확실한 효과를경헝하려면 논리 스캔 조각화 상태가 20% 이상일 때 조각 모음을 한 경우 작업 성능 개선 효과를 확실히 느낄 수 있었다. 인덱스 조각모음을 할 경우에는 20% 이상의 논리 스캔 조각화 상태에서 하기를 추천한다. 또 하나 기억할 것은 힙에 있는 데이터의 경우(index ID = 0) 아무런 상관이 없다.

 

- 평균 페이지 밀도 값이 낮으면 쿼리는 결과값을 읽기 위해 더 많은 페이지를 읽어들어야 한다. 그렇기 때문에 페이지 밀도가 높을 수록 동일 쿼리는 더 적은 디스크 I/O 만으로도 동일한 결과값을 가져올 수 있다. 일반적으로 최초 데이터가 적재된 다음 페이지 밀도는 매우 높지만, 데이터가 삽입되면 리프 페이지가 분할되며 시간이 지날수록 페이지 밀도는 낮아지게 된다. 평균 페이지 밀도를 분석할 때 유의할 점은 테이블을 최초 생성 당시 주어진 특정 fillfactor 요소 값에 의존하게 된다는 것이다.

 

- 스캔 밀도(Scan density[Base count : Actual count])도 페이지 단편화를 가리키는 항목이긴 하지만, 인덱스가 여러개의 파일에 분산되어 있는 경우에는 유효하지 않다. 그러므로 인덱스가 여러개 파일에 분산된 경우에는 스캔 밀도 항목 값을 고려할 필요는 없다.

 

Summary

- 스캔한 페이지(Page Scan)가 10,000 페이지 이상인 경우

- 논리 스캔 조각화 상태(Logical Scan Fragmentation)가 10% ~ 20% 이상인 경우

- 평균 페이지 밀도(Avg. Page Density(full) )가 낮은 경우

 

단편화 정도 모니터링하기

정기적으로 인덱스 단편화 정도를 모니터링하는 것은 좋은 습관이다. SQL Server Books Online에 “DBCC SHOWCONTIG” 항목을 보면 어떤 인덱스가 심하게 단편화가 되어 있는지 확인하고 자동으로 리빌드해주는 샘플 스크립트를 확인할 수 있다. DBCC SHOWCONTIG TABLERESULTS 옵션을 사용하면 정기적으로 테이블안에 분석 정보를 저장할 수 있다. 단편화 정도를 모니터링하는데 시간이 너무 많이 소용되는 경우에는 WITH FAST옵션을 사용할 수 있다. WITH FAST 옵션을 사용하면 인덱스의 리프 페이지 스캔을 피하도록 하여 좀 더 빠른 시간에 결과값을 추출해낼 수 있다. 그렇지만, 인덱스 리프 페이지 스캔을 하지 않으면 페이지 밀도에 관한 수치 값을 가져오지 못한다.

 

테이블 1 결과 값은 소규모 환경과 대규모 환경에서 DBCC SHOWCONTIG가 소요되는 시간을 측정한 것이다. 각 테스트에 ALL_INDEXES 와 TABLERESULTS 옵션을 주고 테스트 하였다.

 

테이블 1. DBCC SHOWCONTIG 성능

 

DBCC SHOWCONTIG options	Total number of index pages (all indexes)	Run time (minutes)
Small-Scale Environment
Not using the WITH FAST option	1,702,889	5.02
Using the WITH FAST option	1,702,889	0.90
Large-Scale Environmentb>
Not using the WITH FAST option	111,626,354	382.35
Using the WITH FAST option	111,626,354	48.73

 

 

어떤 형태의 작업 유형이 인덱스 조각모음을 통해 더 많은 이점을 가지는지 알아보기

인덱스 조각모음을 할 경우, 인덱스 조각모음의 결과가 다른 방법보다 더 좋은 성능향상의 효과가 있는지 확인하는 것은 중요하다. 단편화는 디스크 I/O 성능에 안좋은 영향을 준다. 쿼리가 넓은 범위의 인덱스 페이지를 스캔해야 하는 경우라면 단편화된 상태에 비해 더 많은 조각모음을 통해 더 많은 것을 얻을 수 있다.

 

이 테스트에서는 두 가지 유형의 현재 단편화가 진행된 데이터베이스의 성능을 측정하고, 반대로 인덱스 조각모음을 한 다음 두 데이터베이스의 성능 차이를 비교해 볼 것이다. 테스트는 OLTP를 대표하는 데이터베이스와 DSS 형태의 데이터베이스에서 진행한다. OLTP 타입의 작업은 특정 범위의 데이터를 업데이트(insert, update, delete)하거나 빈번하게 데이터를 읽는 것에 초점이 맞춰져 있다. 그러나 DSS 타입의 작업은 짧은 시간에 여러 개의 테이블을 조인하여 결과 값을 가져오도록 되어있다. 이러한 쿼리는 전형적으로 존재하는 하나의 인덱스 또는 여러개의 인덱스를 사용하여 결과값을 추출해낸다. 테스트 결과는 OLTP의 경우 단편화에 영향을 덜 받지만, DSS 타입의 작업의 경우 인덱스 단편화를 조각모음하여 확실한 개선효과를 볼 수 있는 것으로 나타났다.

 

DBCC INDEXDEFRAG 와 DBCC DBREINDEX 구문은 인덱스 조각모음을 수행하는 명령어로 자세한 부분은 후반부에 설명하도록 하겠다.

 

OLTP 유형의 작업

이 테스트에서 OLTP 유형의 작업은 창고에 오더를 주문하는 형태의 시뮬레이션을 진행하였다. 다섯개의 트랜잭션을 일으키는 저장 프로시저를 사용하여 새로운 오더를 생성하고, 오더 상태 질의, 배송, 재고상태, 결제 등의 처리과정으로 구성되어 있다. 저장 프로시저는 데이터 삽입, 업데이트, 삭제를 포함하고 있으며 자주 데이터를 조회하도록 구성되어 있다.

 

그림 1은 다섯 개의 프로시저의 성능 차이를 DBCC INDEXDEFRAG, DBCC DBREINDEX 작업을 하기 전과 후로 비교한 자료이다.

 

그림 1. OLTP 형태에서 인덱스 단편화 상태와 조각모음을 한 다음의 성능 비교. 값이 낮을 수록 더 좋은 성능을 나타낸다.

그림 1에서 보듯이 인덱스가 단편화된 상태나 조각 모음을 한 이후 저장 프로시저의 성능 개선 효과가 매우 작은 것을 알 수 있다. 왜냐하면, 저장 프로시저에서 조회하는 데이터들은 특정 부분의 데이터들을 호출하는 경우이기 때문에, 인덱스 단편화에 영향을 많이 받지 않는다. 그림 1의 결과를 보면 어떤 경우에는 인덱스 조각모음을 한 다음에 저장 프로시저의 성능이 더 안 좋아지는 경우도 확인할 수 있다. 10~20%의 변화는 저장 프로시저가 실행된 시점의 서버환경에 영향을 받을 수 있다. 더 중요한 것은 단편화 정도를 계속해서 낮추더라도 성능은 더 개선되지 않는다는 것이다.

 

DSS 유형의 작업

이 테스트에서 DSS 유형의 작업은 복잡한 쿼리를 사용하여 리포트 형태의 데이터를 추출하는 22개의 쿼리를 구성하고 있다. 이 쿼리들은 서버에서 일괄 배치 작업으로 수행된다. 모든 쿼리들은 하나 이상의 테이블 조인을 사용하며, 인덱스가 존재하는 넒은 범위의 스캔을 통해 데이터를 가져오도록 되어 있다.

 

테이블 2는 테스트에서 사용한 인덱스의 평균 단편화 정도와 페이지 밀도를 나타내고 있다. 단편화 정도는 다음과 같은 작업들을 통하여 만들어냈다.

- 데이터베이스에 새로운 데이터를 Bulk insert, 데이터에 주기적인 리프레시 시뮬레이션

- 데이터의 특정 범위를 삭제

- 키 값에 대한 업데이트 수행. (데이터 업데이트 작업의 경우 삽입이나 삭제에 비해 상대적으로 단편화 정도에 적은 영향을 미친다.)

 

테이블 2. 소규모 환경/대규모 환경에서의 평균 논리적 단편화 정도 와 페이지 밀도

 

Fragmentation level	Average logical fragmentation (%)	Average page density (%)
Small-Scale Environment
Low (1)	7.8	80.1
Medium (2)	16.6	68.1
High (3)	29.5	69.2
Large-Scale Environment
Low (1)	5.9	84.4
Medium (2)	13.8	70.3

 

DSS 유형의 작업에서는 OLTP 유형과는 매우 다른 결과값을 보여주었다. 인덱스 조각모음을 통해 분명하게 성능이 개선된 것을 확인할 수 있다. 이는 작업유형이 디스크 처리량에 매우 높게 의존하고 있기 때문이라고 예측된다.(대부분의 쿼리들이 인덱스 스캔을 수행하였다.) 그림 2, 3을 보면 DSS 유형의 작업에서는 조각모음을 하기전과 후에 극명한 성능한 개선이 있음을 알 수 있다.

 

소규모 환경에서는 낮은 단편화 정도 상태에서 60%의 성능 개선이, 높은 단편화 정도에서는 460% 이상의 성능 개선효과가 나타났다. 대규모 환경에서도 낮은 단편화 정도에서는 13%, 중간 단편화 정도에서는 40%의 성능 개선을 확인할 수 있다. 결과를 보면 대규모 환경에서 디스크 성능이 더 좋기 때문에 단편화 정도에 영향을 덜 받는것으로 나타난다. 테스트 결과에 대한 더 자세한 사항은 “디스크 처리량과 SQL 서버의 먼저 읽기 매니저에서의 단편화 영향” 부분에서 확인하도록 하자.

 

그림 2. 소규모 환경에서 DSS 유형의 작업 처리시 성능 개선 정도. 낮은 값일 수록 성능이 좋다.

 

그림 3. 대규모 환경에서 DSS 유형의 작업 처리시 성능 개선 정도. 낮은 값일 수록 성능이 좋다.

그림 2의 결과를 보면 직관적으로 DBCC INDEXDEFRAG 보다 DBCC DBREINDEX가 소규모 환경에서는 더 나은 걸 알 수 있다. 대부분의 경우, 전체적으로 인덱스를 다시 빌드하는 것이 성능 개선이 더 좋다.

 

이 테스트 결과를 통해 우리는 다음 사항들을 주목해야 한다.

 

- 그림 2와 3의 결과를 보면 최선의 방법은 DBCC INDEXDEFRAG임을 알 수 있다. DBCC INDEXDEFRAG는 사용이 적은 시스템에서 수행하여 단편화 정도를 완벽하게 제거할 수 있다. DBCC INDEXDEFRAG를 사용중인 상태에서 실행할 경우 업데이트나 기타 락(lock)을 잡고 있는 페이지에 대해서는 Skip하게 된다. 그 결과, 완벽하게 단편화를 제거하지 못할 수도 있다. DBCC INDEXDEFRAG가 유효한지 체크하기 위해 DBCC SHOWCONTIG를 실행하고 DBCC INDEXDEFRAG를 수행하도록 하자.

 

- 데이터를 디스크에 어떻게 배치하였는지도 디스크 성능에 영향을 준다. 소규모 환경에서는 데이터베이스 구축 당시 데이터는 두 개의 물리적 장치에 분산시켜 저장하였다. 모든 데이터의 사이즈는 22GB에서 30DB였다. 원래 데이터에비스를 구축할 때 데이터 파일들은 물리적 장치의 가까운 외부 가장자리부터 저장되게 되어 있다. 그렇기 때문에 DBCC DBREINDEX로 인덱스 전체를 다시 빌드할 경우 이전의 인덱스 페이지를 풀기 전에 먼저 새로운 공간을 할당해 주어야 한다. 이런 할당 과정을 통해 데이터는 디스크의 가장자리에서 안 쪽으로 이동하게 되고 그 결과 약간의 디스크 처리량의 감소가 일어나게 된다. 소규모 환경에서의 벤치마크 결과 읽기가15% 정도 성능이 감소하였다.

 

- 또한 DBCC DBREINDEX의 경우 여유 공간에 영향을 받는다. 여유 공간이 충분하지 않은 상태에서 진행할 경우 DBREINDEX는 데이터 파일 안의 여유 공간을 강제로 재사용하여 약간의 인덱스의 논리적 단편화를 만들어낼 수 있다. DBCC REINDEX의 여유 공간에 관한 부분은 이 문서의 DBCC DBREINDEX 부분에서 설명하도록 하겠다.

 

쿼리 수행으로 발생하는 I/O 양 확인하기

조각모음은 I/O가 빈번하게 발생하는 쿼리에서 성능 개선 효과가 크기 때문에, 특정 쿼리의 I/O 양을 확인하는 것도 매우 가치있는 일이다. SET STATISTICS 구문을 사용하여 특정 쿼리가 SQL Server의 인스턴스에서 얼마만큼의 읽기 작업을 수행하는지 확인 할 수 있다. 이 구문은 쿼리 분석기에서 ON/OFF 옵션으로 설정하여 사용할 수 있다.

 

 

 

 

테이블 3. SET STATISTICS IO 결과에 대한 설명

 

Value	Description
Scan count (스캔 수)	Number of scans performed
logical reads (논리적 읽기 수)	Number of pages read from the data cache
physical reads (물리적 읽기 수)	Number of pages read from disk
read-ahead reads (미리 읽기 수)	Number of pages placed into the cache for the query

 

 

물리적 읽기 수와 미리 읽기 수 항목을 보면 쿼리가 수행하는 동안의 I/O 성능에 대해 알 수 있다. 물리적 읽기 수와 미리 읽기 수 둘 다 디스크로부터 페이지를 읽어들어오는 것을 가르킨다. 보통 물리적 읽기 수보다 미리 읽기 수를 더 중요하게 봐야 한다.

 

참고 : SQL Profiler trace로 읽어 들인 정보의 reads 컬럼은 물리적 읽기가 아닌 논리적 읽기 수를 나타낸다.

 

페이지가 정렬이 안되어 있는 경우에는 인덱스 조각모음을 통하여 인덱스 리프 페이지를 위한 페이지 밀도를 증가시켜 쿼리가 읽어 들이는 I/O 양을 감소시킬 수 있다. 페이지 밀도가 올라가면 동일 쿼리를 실행하였을 때 SQL 서버가 읽어 들이는 전체 페이지 수가 줄어들기 때문에 성능도 개선된다.

 

단편화 상태에서의 디스크 처리량과 SQL서버 미리 읽기 매니저 상관 관계

단편화는 디스크 단위로 읽기가 빈번한 작업의 성능에 안 좋은 영향을 미친다. 그러므로 윈도우 성능 모니터를 통해 어떤 작업이 단편화의 영향을 많이 받고 있는지 모니터링 할 수가 있다. 성능 모니터는 디스크 활동이 많은 작업을 모니터링하여 조각모음을 할 때 유용하게 쓸 수 있다.

 

왜 DSS 작업 유형이 단편화에 영향을 받는지 이해하기 위해서는 SQL Server의 미리 읽기 매너저가 단편화에 어떤 영향을 받는지 알아야 한다. 쿼리가 하나 또는 여러개의 인덱스를 스캔하면, SQL Server의 미리 읽기 매니저는 인덱스 페이지와 추가적인 데이터 페이지들을 가져와 SQL Server의 데이터 캐쉬에 가져오는 책임을 지고 있다. 미리 읽기 매니저는 존재하는 페이지의 물리적 순서에 기반하여 읽어들일 데이터의 사이즈를 다이나믹하게 적용한다. 단편화가 낮으면 미리 읽기 매니저는 한 번에 많은 블럭에서 데이터를 읽어 올 수 있으니 I/O 서브시스템을 사용하는 것보다 더 효율적이다. 데이터가 단편화되어 있으면 미리 읽기 매니저는 작은 블럭의 데이터만 읽어 들이게 된다. 데이터의 물리적 순서에 독립적으로 데이터를 미리 읽을 수도 있지만, 그럴 경우 각 블럭 자원에 더 많은 CPU 리소스가 할당이 되고 그 결과 전체적인 디스크 처리랑이 감소하게 된다.

 

모든 경우 미리 읽기 매니저가 성능에 영향을 준다. 단편화가 존재한다고 하더라도 미리 읽기 매니저가 큰 블럭 사이즈의 데이터를 읽을 수 없는 것은 아니지만, 그럴 경우 전체적인 디스크 처리량에 영향을 주게 될 것이다. 성능 모니터에 나타나는 물리적 디스크 카운트가 어떤 것을 의미하는 지 아래 테이블에 나온 설명을 보도록 하자.

 

테이블 4. 성능 모니터 물리적 디스크 카운트

 

Physical Disk counter	Description
Avg Disk sec/ Read	디스크 호출 시간. 테스트 결과 단편화 정도가 매우 높을 수록(30% 이상일 경우) 디스크 호출 시간이 증가한다. Avg Disk sec/ Read은 디스크에서 데이터를 읽은 평균 시간(초) 입니다.
Disk Read Bytes/ sec	디스크 전체의 처리량에 관한 측정값. 일반적으로 동일 작업이 시간이 지날수록 하강의 형태가 되면 단편화에 따른 성능 영향이라고 보면 된다. Disk Read Bytes/ sec은 읽기 작업 동안 디스크에서 전송되는 바이트 수입니다.
Avg Disk Bytes/ Read	읽기 작업이 요청되었을 때 디스크에서 얼만큼의 데이터를 읽어 들였는지에 대한 측정값. 인덱스 페이지가 인접해있는 경우 SQL Server 미리 읽기 매니저는 한 번에 많은 데이터를 읽어 들일 수 있으며 I/O 서브시스템을 사용하면 더 효율적이다. 테스트는 이 값과 단편화의 양이 직접적으로 상관관계가 있다고 보여준다. 단편화 정도가 증가할 수록 이 값은 감소하며 전체적인 디스크 처리량에 영향을 미치게 된다. Avg Disk Bytes/ Read은 읽기 작업 동안 디스크로 전송되는 평균 바이트 수입니다.
Avg Disk Read Queue Length	throughput. 일반적으로 대상은 평균 두 개의 물리적 장치 아래에 위치하게 된다. 테스트에서는 이 값은 단편화가 증가할 수록 증가하며, 높은 대기 시간과 낮은 전체 디스크 처리량을 보여주게 된다. Avg Disk Read Queue Length은 샘플 간격 동안 선택된 디스크에 대해 큐에 있는 쓰기 요청의 평균 수입니다.

 

그림 4~7을 보면 DSS 유형의 작업을 수행하는 동안 평균적인 읽기 사이즈와 디스크 처리량에 관한 성능 모니터 결과를 확인할 수 있다.

 

그림 4. 소규모 환경에서 DSS 유형의 작업 수행 시 디스크 처리량. 값이 높을 수록 디스크 처리량이 높다.

 

그림 5. 대규모 환경에서 DSS 유형의 작업 수행 시 디스크 처리량. 값이 높을 수록 디스크 처리량이 높다.

 

그림 6. 소규모 환경에서 DSS 유형의 작업 수행 시 각 디스크로부터 평균적으로 읽어들인 사이즈. 값이 높을 수록 한 번에 읽어들이는 바이트가 많다.

 

그림 7. 대규모 환경에서 DSS 유형의 작업 수행 시 각 디스크로부터 평균적으로 읽어들인 사이즈. 값이 높을 수록 한 번에 읽어들이는 바이트가 많다.

이전 그림들에서 보듯이 단편화는 디스크 성능에 영향을 준다. DBCC DBREINDEX와 DBCC INDEXDEFRAG가 차이는 있지만, 모든 시스템이 일관되게 단편화 정도가 올라갈 수록 평균 읽기 사이즈가 줄며, 전체적인 디스크 처리량도 줄고 있다. 보다시피 인덱스 조각모음을 통해 디스크 처리량도 개선시킬 수 있다.

 

평균적인 읽기 사이즈의 수치는 단편화가 미리 읽기 매니저가 한 번에 많은 블럭의 데이터를 가져오는 능력에 영향을 주고 있다는 것은 매우 중요하다. 그러나 평균적인 읽기 사이즈가 높다는 것이 전체적인 디스크 처리량이 좋다는 것과 동일하다고 생각해서는 안된다. 많이 읽는다는 것은 데이터 전송을 위해 CPU에 부하를 줄 수도 있다. 인덱스가 단편화 되지 않았을 때, 데이터는 64KB ~ 256KB의 사이즈를 빠르게 읽어들일 수 있다. 이것은 특히 데이터가 여러 개의 물리적 장치에 분산되어 있어 대규모 환경에서는 사실이다. 일반적으로 시스템 사이의 여러가지 차이점( I/O 서브시스템, 작업 유형의 특성, 디스크에 데이터가 배치되어 있는 형태, 등)에 따라 다른 결과를 보여줄 수도 있다. 여러분의 시스템을 오랜 시간동안 데이터 처리량과 읽기 사이즈등을 관찰하여 하강 곡선의 형태가 되는지 모니터링하길 바란다. 추가적으로 DBCC SHOWCONTIG를 사용하여 언제 인덱스 조각모음을 해야되는지 확인하기 바란다.

 

또한 단편화는 디스크 대기 시간을 증가시킨다. 그러나 테스트에서 보면 소규모 환경에서 높은 단편화 정도인 경우에만 디스크 대기 시간이 부정적인 영향을 받았다. 디스크 대기시간은 대규모 환경에서는 SAN에서 제공하는 I/O 성능으로 인하여 큰 이슈가 되지는 않는다.

 

DBCC DBREINDEX VS DBCC INDEXDEFRAG

CREATE INDEX 구문을 사용하여 인덱스를 삭제하고 다시 생성하는 것과 더불어 DBCC DBREINDEX, DBCC INDEXDEFRAG 구문을 활용하여 인덱스 유지보수를 할 수 있다.

 

DBCC DBREINDEX

DBCC DBREINDEX는 특정 테이블에 하나 또는 그 이상의 인덱스들을 다시 빌드할 때 사용한다. DBCC DBREINDEX는 오프라인 작업이다. 작업 중에는 데이터베이스의 해당 테이블에 다른 사용자가 접근하는 것이 불가능하다. DBCC DBREINDEX는 다이나믹하게 인덱스를 다시 생성한다. 여러분은 테이블의 구조나 PRIMARY KEY 또는 UNIQUE 제약조건 같은 것들에 대해 신경 쓰지 않아도 자동적으로 다시 빌드해준다. DBCC DBREINDEX는 완벽하게 인덱스드를을 다시 빌드해주며, 페이지 밀도를 기본으로 설정된 fillfactor값에 맞게 복구시켜 주며 또는 여러분이 설정한 다른 값으로 채워줄 수 있다. 내부적으로 DBCC DBREINDEX는 SQL 구문을 사용하여 인덱스를 삭제하고 다시 생성하는 것과 매우 유사하게 동작한다.

 

DBCC INDEXDEFRAG 보다 DBCC DBREINDEX 작업이 좋은 두 가지 이점은 다음과 같다.

- DBCC DBREINDEX 작업은 인덱스가 다시 빌드되는 동안 통계 정보를 자동으로 갱신해준다. 이로써 작업성능의 퍼포먼스를 증대시킬 수 있다.

- DBCC DBREINDEX 작업은 매우 크고 무거운 단편화된 인덱스에서 작업할 때, 멀티 프로세서 컴퓨터의 이점을 활용하여 빠르게 처리가 가능하다.

 

DBCC DBREINDEX에 의해 발생하는 모든 작업은 단일의 트랜잭션으로 나타난다. 새로운 인덱스는 완벽하게 배치되기 전에 오래된 인덱스 페이지가 해제되어야 한다. 인덱스를 다시 빌드하기 위해서는 데이터 파일안에 여유 공간이 충분히 남아 있어야 한다. 여유 공간이 충분하지 않은 상태에서 DBCC DBREINDEX 작업을 하면 인덱스를 다시 빌드하지 못하거나, 인덱스가 논리적 단편화 값이 0 이상인 상태로 빌드가 된다. 필요한 공간은 발생된 트랜잭션의 인덱스 수에 영향을 받는다.클러스터드 인덱스의 경우 필요 공간을 계산하는 방법은 다음과 같다.

필요 공간 = 1.2 * 평균 로우 사이즈 * 전체 로우 수

 

넌클러스터드 인덱스의 경우 필요 여유 공간을 예측하는 방법은 넌클러스터드 인덱스 안에 각 로우의 평균 로우 사이즈를 계산하면 된다.(넌클러스터드 키의 길이 더하기 클러스터링 키의 길이 또는 로우 ID). 그리고나서 로우 수를 곱하면 된다. 만일 전체 테이블의 인덱스들을 다시 빌드한다면, 클러스터드 인덱스와 모든 넌클러스터드 인덱스 만큼의 여유 공간이 필요할 것이다. 유사하게, 유일하지 않은 클러스터드 인덱스를 다시 빌드할 때도 클러스터드 인덱스와 넌클러스터드 인덱스 모두를 포함한 여유 공간이 있어야 된다. 넌클러스터드 인덱스의 경우 SQL Server에서 각 로우의 고유한 새로운 식별자를 생성해야 하기 때문에 무조건 적으로 다시 빌드된다. DBCC DBREINDEX 작업을 사용하면, 여러분이 조각모음하고자 하는 특정 인덱스에 좋은 영향을 줄 것이다. 여러분은 불필요한 작업을 피하고 성능에 도움이 되도록 도와줄 것이다.

 

DBCC INDEXDEFRAG

DBCC INDEXDEFRAG는 특정 인덱스를 다시 빌드하는 작업이다. DBCC DBREINDEX와 유사하게 여러분은 테이블 구조에 대하여 알 필요가 없다. 하지만, DBCC INDEXDEFRAG는 하나의 구문으로 모든 인덱스를 다시 빌드할 수는 없다. 여러분은 DBCC DBINDEXDEFRAG를 통해 각각의 인덱스에 대해 조각모음을 해줘야 한다.

 

DBCC DBREINDEX와 달리 DBCC INDEXDEFRAG는 온라인 작업이다. 그러므로 인덱스 조각모음을 하는 동안에도 테이블 접근이나 인덱스 사용이 가능하다. DBCC DBREINDEX와 다른 주요한 차이점은 작업이 중지되었다가 재개되더라도 진행된 작업 내용을 잃지 않는다. DBCC DBREINDEX는 한 번의 트랜잭션을 통해 전체 모든 작업이 수행된다. 이 말은 DBCC DBREINDEX 작업이 롤백되면 여러분은 처음부터 다시 시작해야 된다는 것이다. 그러나, DBCC INDEXDEFRAG 작업은 여러 개의 트랜잭션을 수행하여 진행되기 때문에 작업 중에 중지하여도 진행된 내용을 보존한다.

 

DBCC INDEXDEFRAG는 두 가지 단계로 구성되어 있다.

1. 인덱스가 생성되던 당시의 특정 fillfactor기반의 페이지 밀도를 적용하도록 페이지 압축을 시도한다. DBCC INDEXDEFRAG는 페이지의 원래 fillfactor 레벨의 페이지 밀도로 높이려고 시도하게 된다. 그러나 실패하게 되며 원래의 fillfactor 보다 높은 현재의 페이지를 아래로 내리게 된다.

2. 조각모음은 섞여 있는 인덱스 페이지의 리프 노드를 논리적 순서와 물리적 순서를 일치시키는 것이다. 이것이 수행되면 작은 별개의 여러 트랜잭션이 발생하고 그리하여 전체적인 시스템 성능 증대의 효과는 작을 수 있다. 그림 8은 DBCC INDEXDEFRAG를 수행하는 동안 페이지들의 이동하는 모습을 나타내고 있다.

 

그림 8. 데이터 파일 안에서 DBCC INDEXDEFRAG 작업으로 발생하는 페이지 이동

 

 

 

 

DBCC INDEXDEFRAG는 인덱스를 풀어주는 데는 도움을 주지 못하고 데이터 파일 안에 단순히 순서에 맞게 끼어준다. 마찬가지로 DBCC INDEXDEFRAG는 인덱스의 익스텐트 단편화를 수정해주지는 않는다. 데이터 파일 안에 인덱스 익스텐트(8개의 인덱스 그룹 페이지)가 완벽하게 연속적으로 배치되지 않을 때, 파일 안에 존재하고 있는 하나 또는 그이상이 섞여있는 익스텐트 사이안에 끼워넣는 것이다. 끼워넣기를 하면 논리적, 물리적 순서가 일치하고 모든 필요한 인덱스 페이지들이 연속적으로 배치되기 때문에 논리적 단편화는 발생하지 않게 된다.

 

이런 제한사항들이 있음에도 테스트 결과 DBCC DBREINDEX만큼 DBCC INDEXDEFRAG도 성능 개선에 효율적임을 보여준다. 사실 테스트를 보면 인덱스를 다시 빌드하더라도 최소의 끼워넣기가 발생하기 때문에, 성능에 영향은 크지 않아 보인다. 논리적 단편화를 제거하는 것만을도 작업 성능에 더 큰 영향을 줄 수 있다. 이것이 여러분이 인덱스 단편화를 조사할 때, 논리적 단편화와 페이지 밀도를 주의깊게 살펴보라고 한 이유이다. 표 5에 DBCC DBREINDEX와 DBCC INDEXDEFRAG를 차이점을 요약하였다.

 

표 5. DBCC DBREINDEX 와 DBCC INDEXDEFRAG의 비교

 

Functionality	DBCC DBREINDEX	DBCC INDEXDEFRAG
Online/Offline	Offline	Online
Faster when logical fragmentation is:	High	Low
Parallel processing	Yes	No
Compacts pages	Yes	Yes
Can be stopped and restarted without losing work completed to that point	No	Yes
Able to untangle interleaved indexes	May reduce interleaving	No
Additional free space is required in the data file for defragmenting	Yes	No
Faster on larger indexes	Yes	No
Rebuilds statistics	Yes	No
Log space usage	High in full recovery mode (logs entire contents of the index), low in bulk logged or simple recovery mode (only logs allocation of space)	Varies based on the amount of work performed
May skip pages on busy systems	No	Yes

 

 

성능 : DBCC DBREINDEX VS DBCC INDEXDEFRAG

 

테스트 결과 DBCC DBREINDEX, DBCC INDEXDEFRAG 둘 다 인덱스 조각모음에 효과적이며 테이블의 원래 filfactor 에 가깝게 페이지 밀도를 맞춰주었다. 이 테스트 결과를 바탕으로 여러분이 어떤 구문이 필요한지 확인할 수 있다.

 

여러분이 만약 인덱스를 오프라인 상태에서 다시 빌드할 시간이 있다면, 일반적으로 DBCC INDEXDEFRAG 보다 DBCC DBREINDEX가 더 빠르다. DBCC DBREINDEX의 경우 멀티 프로세서의 기반에서 사용가능한 프로세서의 병렬 처리로 통한 인덱스 생성의 이점을 전체적으로 활용한다. DBCC INDEXDEFRAG는 운영중인 환경에 영향을 덜 주도록 디자인되어 있고, 성능 개선의 효과도 상대적으로 작다. 이 테스트 결과를 보면 DBCC INDEXDEFRAG를 병렬로 여러 개를 동시에 실행시키더라도 DSS 작업 성능에 10% 이상으로 영향을 주지 않는다는 것이다. 이는 DBCC INDEXDEFRAG 가 매우 큰 사이즈의 인덱스를 오래 실행하는 형태로 디자인되어 있기 때문이다. 게다가 DBCC INDEXDEFRAG는 구문이 실행될 떄 서버에 올라오는 시간까지 의존하여 조각모음이 수행된다.

 

그림 9는 DBCC INDEXDEFRAG와 DBCC DBREINDEX 사이의 차이점을 비교한 것이다 . 값은 소규모 환경의 데이터베이스에서는 모든 인덱스를 다시 빌드하는 전체 시간이 얼마나 걸리는지 보여준다. (대규모 환경에서는 DBCC INDEXDEFRAG가 DBCC DBREINDEX 보다 8배 더 느리게 동작한다). 단편화 정도와 인덱스 사이즈가 증가할수록, DBCC DBREINDEX가 DBCC INDEXDEFRAG보다 더 빨리 작업을 처리한다.

 

그림 9. 소규모 환경에서 데이터베이스의 전체 인덱스를 조각모음하는데 필요한 전체 실행 시간

 

 

 

로그에 관한 고려사항 : DBCC DBREINDEX VS DBCC INDEXDEFRAG

 

DBCC DBREINDEX와 DBCC INDEXDEFRAG를 사용할 때 데이터의 양에 따라 트랜젝션 로그를 발생시키는 것에 대해 고려해야 한다. DBCC INDEXDEFRAG는 단편화 정도와 작업이 수행되는 양에 따라 피료한 로그 정보도 의존적이다. 테스트 결과를 보면 DBCC INDEXDEFRAG가 데이터베이스 전체 복구 모드에서 DBCC DBREINDEX 보다 로그를 덜 사용하고 있다. 그러나 DBCC INDEXDEFRAG는 데이터 양에 따라 로그 사용량도 달라지기 때문에 매우 커질수도 있다. 이는 DBCC INDEXDEFRAG는 인덱스 조각모음을 수행하기 위해 페이지의 이동과 페이지 압축에 필요한 로그가 많기 때문이다. DBCC INDEXDEFRAG 에서 사용한 로그 공간은 작업이 완료되고 나서 다시 반환받을 수 있다. 왜냐하면 작업이 여러개의 작은 트랜잭션들로 구성되어 있기 때문이다.

 

로그 사용에 관해서는 DBCC DBREINDEX는 DBCC INDEXDEFRAG와 다르게 행동한다. 가장 큰 차이점은 대규모 로그 복구 모드인 경우이다. 전체 복구 모드에서 DBCC DBREINDEX는 각 인덱스 페이지의 이미지를 로그로 남기지만, 대규모 로그 모드인 경우에서는 발생하지 않는다. 이런 이유로 전체 복구 모드에서는 DBCC DBREINDEX의 경우 전체 인덱스 페이지의 수 * 8KB의 로그 여유 공간이 필요로 한다. 여러분은 DBCC SHOWCONTIG를 확인하여 전체 인덱스 페이지의 수를 계산해야 한다. 대용량의 환경에서는 DBCC DBREINDEX를 사용할 때 대규모 로그 복구 모드로 변경하는 거슬 고려할 필요가 있다.

 

참고 : 대규모 환경에서는 오래 실행되는 트랜잭션의 경우 롤백에 따른 로그 발생이 비싸므로 필요한 로그 사이즈 측정이 중요하다.

 

그림 10은 DBCC DBREINDEX와 DBCC INDEXDEFRAG 사이의 로그 공간 사용에 관한 차이를 비교한 그림이다. 로그 사용 공간이 DBCC INDEXDEFRAG의 경우 변동이 크지만 이 테스트 결과는 단순히 DBCC DBREINDEX와 DBCC INDEXDEFRAG 사이의 차이를 비교하기 위한 목적이다.

 

그림 10. DSS 데이터베이스에서 전체 인덱스를 조각모음하는 동안 DBCC DBREINDEX, DBCC INDEXDEFRAG의 전체 로그 사용 공간