[MSSQL][advanced] index 단편화 현상

쿼리 튜닝을 아무리 잘 하였다고 하더라도 데이터의 양에 따라 성능이 좌우될 수 있다.

많은 양의 insert, delete 및 update가 이루어지는 테이블의 경우 쉽사리 clustered index를 걸기 힘들다.

이는 인덱스의 재 갱신에 대한 부담이 크기 때문이며, 이런 경우 clustered index는 단순히 identity 컬럼으로 지정하고, 기타 컬럼을 index 지정하여 호출하게 된다.

하지만 이렇게 지정한 index도 문제가 발생할 소지가 있다.

많은 양의 delete와 insert, update가 이루어지면 점차적으로 index가 조각나버리게 된다.

이러한 인덱스 단편화 현상에 대한 체크가 가능하다.
dbcc 명령어중 show contig라는 명령어이다. (msdn 참조)

문법은 다음과 같다.

DBCC SHOWCONTIG 
[ ( 
    { table_name | table_id | view_name | view_id } 
    [ , index_name | index_id ] 
) ] 
    [ WITH 
        { 
         [ , [ ALL_INDEXES ] ] 
         [ , [ TABLERESULTS ] ] 
         [ , [ FAST ] ]
         [ , [ ALL_LEVELS ] ] 
         [ NO_INFOMSGS ]
         }
    ]

간단히 사용한 예제는 아래와 같다.

DBCC SHOWCONTIG (테이블이름, 인덱스이름)

index이름은 다음과 같은 명령어로 확인 할 수 있다.

SP_HELPINDEX 테이블이름

페이지 밀도가 낮을 수록 인덱스의 단편화 현상이 크므로 인덱스에 대한 조각모음이 필요하다.

index에 대한 도움말을 다음 페이지에서 참고하면 좋다. (msdn 참조)

ALTER INDEX의 문법은 다음과 같다.

ALTER INDEX { index_name | ALL }
    ON <object>
    { REBUILD 
        [ [ WITH ( <rebuild_index_option> [ ,...n ] ) ] 
          | [ PARTITION = partition_number 
                [ WITH ( <single_partition_rebuild_index_option>
                        [ ,...n ] )
                ] 
            ]
        ]
    | DISABLE
    | REORGANIZE 
        [ PARTITION = partition_number ]
        [ WITH ( LOB_COMPACTION = { ON | OFF } ) ]
    | SET ( <set_index_option> [ ,...n ] ) 
    }
[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ]
        table_or_view_name
}

<rebuild_index_option > ::=
{
    PAD_INDEX  = { ON | OFF }
  | FILLFACTOR = fillfactor 
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | ONLINE = { ON | OFF } 
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
}

<single_partition_rebuild_index_option> ::=
{
    SORT_IN_TEMPDB = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
}

<set_index_option>::=
{
    ALLOW_ROW_LOCKS= { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
}

index를 재구성하기 위한 방법이 여러 옵션으로 존재한다.

1. 인덱스를 제거한 후 다시 작성하는 방법
2. 인덱스 조각모음 (defrag)
3. 인덱스 재구성 (reindex)

Microsoft SQL Server 이전 버전에서는 인덱스 조각모음에 대해 DBCC INDEXDEFRAG 명령어로 처리가 가능하였지만 이후 버전에서는 이 명령어는 제거되고 ALTER INDEX에 기능이 보완되었다.(msdn 참조)
또한 인덱스 재구성에 대한 명령어로 DBCC DBREINDEX 명령어로 처리가 가능하였지만 이후 버전에서는 이 명령어도 제거되고 ALTER INDEX에 기능이 보완되었다. (msdn 참조)

인덱스 다시 구성 및 다시 작성하는 방법은 다음과 같다. (msdn 참조)

조각모음은 아래와 같다.

ALTER INDEX 인덱스이름 ON 테이블이름
REORGANIZE;

재구성은 아래와 같다.

ALTER INDEX ALL ON 테이블이름
REBUILD;

재구성을 할 경우 ALL이 아닌 인덱스별 구성은 오류가 발생하는데 아직 그 이유는 모르겠다.