인덱스 조각 상태 쿼리 MS-SQL

아래 함수로 테이블의 인덱스 조각 상태를 알 수 있다.

--조회
DBCC SHOWCONTIG (tableName)

익스텐드 검색 조각화 상태가 30%를 넘어가면 인덱스의 재구성이 필요하다.

 

다음 쿼리를 이용하면 해당 테이블의 인덱스별 인덱스 상태를 알수 있다.

SELECT a.object_id, object_name(a.object_id) AS TableName, 
    a.index_id, name AS IndedxName, avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats 
    (DB_ID (N'dbName') 
        , OBJECT_ID(N'tableName') 
        , NULL 
        , NULL 
        , NULL) AS a 
INNER JOIN sys.indexes AS b 
    ON a.object_id = b.object_id 
    AND a.index_id = b.index_id

avg_fragmentation_in_percent 의 숫자가 클수록 조각이 많이 나서 인덱스의 효율이 떨어진다.

보통 20이 넘어가면 재구성을 해주도록 하자.

재구성을 하는 경우 DB의 서비스가 멈추게 되므로 조각모음을 해주는 경우가 빈번하다.

다음 함수는 인덱스 조각 모음을 하는 함수다. 

DBCC INDEXDEFRAG(dbName, tableName, indexName)

자주 insert, update를 하는 테이블의 경우 인덱스가 조각 나는 경우가 많기 때문에

서비스를 멈추지 않고 조각모음을 일정간격을 두고 해주는 것이 인덱스의 효과를 유지 할 수 있다.