지정한 테이블의 데이터와 인덱스의 조각화 정도를 확인하는 방법에 대해서 확인해 봅니다.
SQL Server 2008 R2 이전까지 DBCC SHOWCONTIG 명령은 다음 버전부터 없어질 예정이므로 DMV 를 통해서 확인하는 방법을 함께 알고 있으면 좋겠습니다.
상황에 따라 인덱스의 조각화가 증가했을 때 조각화를 줄이기 위한 관리 작업이 필요할 수 있습니다. 물론 조각화가 심하더라도 많은 페이지를 스캔하여 참조하지 않는 OLTP 쿼리라면 영향이 거의 없을 수도 있겠죠.. 인덱스 리빌드가 필요한지 여부는 전후 성능을 비교해 보고 개선 효과를 직접 확인해 보는 게 좋겠습니다. 일반적인 방법으로 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
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
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)
sys.dm_db_index_physical_stats(Transact-SQL)
작성자 : Lai Go / 작성일자 : 2011.04.15
