인덱스 재구성(DBCC DBREINDEX)과 인덱스 조각모음(DBCC INDEXDEFRAG)

현업에서 사용하고 있는 DB에 대해 인덱스 조각모음을 해야 할 경우

인덱스들은 데이터를 추가,삭제시 마다 인덱스의 단편화가 생기고 이 단편화로 인해 인덱스 사용시 조회시간이 늘어나고 공간도 늘어나게 됩니다. 

이를 해결할 방법은 인덱스를 재구성하거나(DBCC DBREINDEX)나 마치 디스크의 조각모음을 하는 것과 같은 인덱스 조각모음을(DBCC INDEXDEFRAG) 하는 것입니다.

두가지 방법 모두 나름대로 장단점이 있습니다.

인덱스를 재구성 할때에는(DBCC DBREINDEX) 해당 인덱스를 사용할 수가 없고

따라서 작업을 할 때에는 서비스를 막아놓고 작업을 해야 합니다. 하지만 인덱스를 새롭게

다시 구성하는 것인만큼 효과는 가장 좋습니다.

인덱스 조각모음은(DBCC INDEXDEFRAG) 작업하는 동안이라도 인덱스를 사용할 수 있습니다.

또한 락이 걸린 부분은 건너띄고 작업을 수행합니다. 따라서 인덱스를 재구성하는 것보다는

효과가 떨어집니다. 인덱스의 조각화가 심할 경우에는 조각 모음을 하는 것보다 새로 만드는 것이

시간적으로나 효과면에서 더 좋으나 실제 서비스를 중단해야 하므로 적합하지 않습니다.

INDEXDEFRAG 작업자체는 서버자체에 큰 부하를 주지 않는다고 합니다.

조각모음을 하게 될때에 가장 중요한 점은 트랜잭션 로그의 크기를 계속적으로 확인해야 합니다.

조각모음을 할 때에 로그 크기도 증가하게 되며 필요에 따라 로그 크기를 줄이기

위해 로그 백업을 자주 해줘야 합니다.

SQL도움말에는 "인덱스 조각모음 작업이 잠금을 오래 보유하지 않으므로 실행 중인 쿼리나 업데이트를 

차단하지 않습니다." 라고 나와 있고 "조각 모음은 일련의 짧은 트랜잭션으로 수행되므로 로그 백업을 

자주 하거나 복구 모델 설정이 SIMPLE인 경우에는 큰 로그가 필요하지 않습니다." 라고 나와 있습니다.

USE DB명

SP_HELPINDEX table명

--조회
DBCC SHOWCONTIG (table명) WITH TABLERESULTS

/*

Scan Density(logical extent density) 와 Avg Page Density(Avg free bytes/page) 확인.

30~50% under이면 Reorg가 필요

*/

--index defrag

DBCC INDEXDEFRAG(DB명, TABLE명, INDEX명)
--혹은 dbcc dbreindex(TABLE명, '', 100)

--reindex 후 다시 조회
DBCC SHOWCONTIG (SC_TRAN) WITH TABLERESULTS