[SQL 2005 GUIDE] 인덱스 조각화 확인하기 - DMV 활용

인덱스는 검색의 속도를 빠르게 해 주는 매우 유용한 기능입니다. 초보 DBA들의 경우에는 인덱스는 한번 만들어 두기만 하면 되는 것으로 알고, 인덱스에 대한 관리를 전혀 하지 않는 경우들이 흔히 있습니다. 수년동안 사용해 오면서 재구성 작업을 한번도 실행해 주지 않아서 인덱스 조각화가 매우 심해진 경우들도 심심찮게 볼 수 있습니다. 인덱스 조각화가 매우 심한 경우에 인덱스 재구성만으로도 성능이 개선되는 효과를 얻기도 합니다. 이와 같이 인덱스는 주기적인 관리가 필요한데, 그런 관리를 위해서는 인덱스의 조각화가 어느 정도 진행되었는지를 확인하는 방법을 알고 있어야 합니다. 그래서 오늘은 먼저 인덱스 조각화를 확인하는 방법에 대하여 소개해 보겠습니다. 


테이블에 INSERT, UPDATE, DELETE가 발생함에 따라서 조각화가 발생하게 됩니다.
조각화가 발생하면 원하는 데이터를 가져오기 위하여 읽어야 할 페이지 수가 늘어나, 성능에 좋지 않은 영향을 미칠 수 있으므로 주기적으로 조각화를 제거하는 작업이 필요합니다. 인덱스 관리에 대하여 알고 있는 DBA들은 주기적으로 인덱스 조각화 제거를 위한 인덱스 재구성 작업을 실행해 줍니다. 그런데 대부분의 경우에 특정 데이터베이스 내의 모든 사용자 테이블들의 모든 인덱스들에 대하여 한번에 인덱스 재구성을 수행하는 것이 일반적입니다. 시스템 오픈 초기에는 데이터의 크기가 작아서 속편하게 한달에 한번 또는 일주일에 한번 야간에 인덱스 재구성을 수행하여도 문제가 없었는데, 데이터의 크기가 점점 커져서 어느 날에는 야간 업무 휴지 시간동안 인덱스 재구성 작업이 완료되지 않아서 정상적인 서비스를 제공하지 못하는 장애가 발생하는 경우도 가끔 볼 수 있습니다.
인덱스 조각화 정도는 인덱스에 따라 천차만별인데 무조건 일률적으로 인덱스 조각화 제거 주기를 적용하는 것은 비효율적이라고 생각합니다. 그러므로 먼저 인덱스 별로 인덱스 조각화 상태를 모니터링하고, 인덱스 별로 인덱스 재구성 스케줄을 적절하게 구성하는 것을 권고합니다. 그리고 향후 소요시간이 증가할 경우를 대비하여 인덱스 별로 날짜를 분산하여 인덱스 재구성을 실행할 것을 권고합니다.


그러면 이제 본론으로 들어 가서 인덱스 조각화를 확인하는 방법을 설명하겠습니다. 

조각화 정보를 알기 위하여 이전 버전에서 사용했던 DBCC SHOWCONTIG의 사용이 가능하지만, SQL Server 2005에서는 테이블의 조각화 정보를 sys.dm_db_index_physical_stats 동적 관린 뷰를 사용하여 확인할 수 있습니다.

 

[구문]

sys.dm_db_index_physical_stats (

    { database_id | NULL }               /* NULL: 서버내의 모든 데이터베이스 */

    , { object_id | NULL }                /* NULL: 해당 데이터베이스내의 모든 오브젝트 */

    , { index_id | NULL | 0 }              /* NULL: 해당 테이블의 모든 인덱스 */

    , { partition_number | NULL } /* NULL: 해당 오브젝트의 모든 인덱스 */

    , { mode | NULL | DEFAULT } /* NULL: LIMIT */

)

mode에는 DEFAULT, NULL, LIMITED, SAMPLED, DETAILED를 입력할 수 있으며, DEFAULT 또는 NULL은 LIMITED를 의미합니다.

LIMITED 모드는 가장 빠르고 가장 적은 페이지를 스캔합니다. 즉, 힙의 모든 페이지와 인덱스의 잎 레벨을 제외한 페이지를 스캔합니다.

SAMPLED 모드는 인덱스 또는 힙의 모든 페이지중에서 1% 샘플에 대한 통계를 반환합니다. 만일, 인덱스 또는 힙이 10,000 페이지보다 적다면 SAMPLED 대신에 DETAILED 모드가 사용됩니다.

DETAILED 모드는 모든 페이지를 스캔하고 모든 통계를 반환합니다.


조각화는 다음과 같은 스크립트를 활용하여 확인 가능합니다.

DECLARE @db_id SMALLINT;

DECLARE @object_id INT;

 

SET @db_id = DB_ID(N'AdventureWorks');

SET @object_id = OBJECT_ID(N'AdventureWorks.Person.Address');

 

IF @db_id IS NULL

BEGIN;

    PRINT N'Invalid database';

END;

ELSE IF @object_id IS NULL

BEGIN;

    PRINT N'Invalid object';

END;

ELSE

BEGIN;

    SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');

END;

GO