[스크랩] 인덱스 조각화 상태 확인

http://laigo.kr/646

 

 

지정한 테이블의 데이터와 인덱스의 조각화 정도를 확인하는 방법에 대해서 아래와 같이 정리하였습니다.

SQL Server 2008 R2 이전까지 DBCC SHOWCONTIG 명령은 다음 버전부터 없어질 예정이므로 DMV 를 통해서 확인하는 방법을 함께 알고 있으면 좋을 것 같습니다.

인덱스의 조각화는 10% 이하가 안정적인 수치이며 이를 초과하는 경우 I/O 가 늘어나게 되어 성능에 영향을 미칠 수도 있으므로 인덱스 조각화를 줄이기 위한 관리 작업이 필요합니다. 일반적인 방법으로 Rebuild, Reorganize 작업을 수행할 수 있습니다. 두 가지 방법의 차이점에 대해서는 참고자료 관련 문서를 확인해 봅니다.


1. DBCC SHOWCONTIG 명령으로 테이블과 인덱스의 조각화 정도 확인

다음 명령을 통해 현재 선택된 데이터베이스 모든 테이블에 있는 인덱스에 대해서 Logical Fragmentation, ScanDensity 등을 한 번에 확인할 수 있습니다.

DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES

[실행결과]

논리적 조각화 상태는 0%에 가까울 수록 좋습니다.

2. DMV 쿼리를 사용하여 인덱스 조각화 정도 확인

sys.dm_db_index_physical_stats DMV 를 사용하여 인덱스 조각화 정보를 확인합니다. 아래 예제는 AdventureWorks 데이터베이스의 HumanResources.Employee 테이블의 인덱스에 대한 조각화 정보입니다.

SELECT
DB_NAME(database_id) AS [Database Name],
OBJECT_NAME(a.object_id) AS [Table Name],
a.index_id, b.name AS [Index Name], a.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('HumanResources.Employee'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b on a.object_id = b.object_id AND a.index_id = b.index_id

WHERE a.database_id = DB_ID()

[실행결과]

avg_fragementation_in_percent 값은 인덱스의 논리적 조각화 상태를 나타냅니다.

[참고자료]

DBCC SHOWCONTIG(Transact-SQL)

http://msdn.microsoft.com/ko-kr/library/ms175008.aspx

sys.dm_db_index_physical_stats(Transact-SQL)

http://msdn.microsoft.com/ko-kr/library/ms188917.aspx

작성자 : Lai Go / 작성일자 : 2011.04.15