인덱스의 크기 및 인덱스 조각화 정보 조회하기

MS SQL Server 2005 부터는 이전 버전보다 보다 다양하고 막강한 동적관리 뷰 및 함수를 제공하고 있다.

그 중에서 관리적 측면에서 이슈가 되는 인덱스의 크기 및 인덱스의 조각화 정도를 조회할 수 있는 sys.dm_db_index_physical_stats 함수를 소개하고 한다.

참고로 DBCC SHOWCONTIG를 통해서도 조회가 가능하지만 MS SQL Server 2008 이후의 차기 버전에서는 제거될 예정이다.

 

1. 구문

sys.dm_db_index_physical_stats (     { database_id | NULL | 0 | DEFAULT }     , { object_id | NULL | 0 | DEFAULT }     , { index_id | NULL | 0 | -1 | DEFAULT }     , { partition_number | NULL | 0 | DEFAULT }     , { mode | NULL | DEFAULT } )

  

2. 인수

인수	데이터 형식	설명
database_id | NULL | 0 | DEFAULT	smallint	데이터베이스 ID. 입력 가능한 인수는 데이터베이스의 ID 번호, NULL, 0 또는 DEFAULT이다. 기본값은 0이며 이 경우 NULL, 0 및 DEFAULT는 동등한 값이다. SQL Server 인스턴스의 모든 데이터베이스에 대한 정보를 반환하려면 NULL을 지정한다. database_id에 NULL을 지정하는 경우 object_id, index_id 및 partition_number에도 NULL을 지정해야 한다. :Track(?ctl00_rs1_mainContentContainer_cpe31375_c|ctl00_rs1_mainContentContainer_ctl11?,this);?>DB_ID 기본 제공 함수를 지정할 수 있으며, 이 경우 현재 데이터베이스의 호환성 수준은 90이어야 한다.  반드시 아래의 시스템 함수 사용하여 매개변수 값 지정 시 주의 사항을 읽어보자.
object_id | NULL | 0 | DEFAULT	int	인덱스가 있는 테이블 또는 뷰의 개체 ID. 입력 가능한 인수는 은 테이블 및 뷰의 ID 번호, NULL, 0 또는 DEFAULT이다. 기본값은 0이며. 이 경우 NULL, 0 및 DEFAULT는 동등한 값이다. 지정된 데이터베이스에 있는 모든 테이블 및 뷰에 대한 정보를 반환하려면 NULL을 지정한다. object_id에 NULL을 지정하는 경우 index_id 및 partition_number에도 NULL을 지정해야 한다. OBJECT_ID 기본 제공 함수를 지정할 수 있으며, 이 경우 현재 데이터베이스의 호환성 수준은 90이어야 한다. 반드시 아래의 시스템 함수 사용하여 매개변수 값 지정 시 주의 사항을 읽어보자.
index_id | 0 | NULL | -1 | DEFAULT	int	인덱스 ID. 입력 가능한 인수는 인덱스의 ID 번호, object_id가 힙인 경우 0, NULL, -1 또는 DEFAULT이다. 기본값은 -1이며, 이 컨텍스트에서 NULL, -1 및 DEFAULT는 동등한 값이다. 기본 테이블 또는 뷰에 대한 모든 인덱스 정보를 반환하려면 NULL을 지정한다. index_id에 NULL을 지정하는 경우 partition_number에도 NULL을 지정해야 한다.
partition_number | NULL | 0 | DEFAULT	int	개체의 파티션 번호. 입력 가능한 인수는 인덱스 또는 힙의 partion_number, NULL, 0 또는 DEFAULT이다. 기본값은 0이며, 이 경우 NULL, 0 및 DEFAULT는 동등한 값이다. 소유하는 개체의 모든 파티션에 대한 정보를 반환하려면 NULL을 지정한다. partition_number는 1부터 시작되며, 분할되지 않은 인덱스 또는 힙의 partition_number는 1로 설정되어 있다.
mode | NULL | DEFAULT	sysname	모드 이름. mode는 통계를 얻는 데 사용되는 검색 수준을 지정하는 것으로 입력하능한 인수는 DEFAULT, NULL, LIMITED, SAMPLED 또는 DETAILED이다. 기본값(NULL)은 LIMITED이다. 모드에 대한 더 자세한 내용은 아래의 검색 모드(mode)에 대한 부연 설명 참조

 

※검색 모드(mode)에 대한 부연 설명

검색 모드는 통계 데이터를 가져오기 위한 샘플링의 수준을 결정하는 인수이다. 

위의 표에서 살펴보았듯이 mode는 LIMITED, SAMPLED 또는 DETAILED로 지정할 수 있다.

sys.dm_db_index_physical_stats는 테이블이나 인덱스의 지정한 파티션을 구성하는 할당 단위에 대해 페이지 체인을 검색하게 되는데, 이때 실행되는 모드에 관계없이 내재된 공유(IS) 테이블 잠금을 사용한다.

• LIMITED 모드: 가장 빠른 모드이며 가장 적은 수의 페이지를 검색한다. 인덱스의 경우 부모 수준 페이지만(즉, 리프 수준 이상의 페이지) 검색한다. 힙의 경우 연결된 PFS 및 IAM 페이지만 조사되고 힙의 데이터 페이지는 검색하지 않는다. SQL Server 2005에서 힙의 모든 페이지는 LIMITED 모드로 검색한다.
• SAMPLED 모드: 인덱스나 힙의 모든 페이지에 대한 1% 샘플을 기준으로 통계를 반환한다. 인덱스나 힙의 페이지 수가 10,000개 미만이면 SAMPLED 대신 DETAILED 모드가 사용한다.
• DETAILED 모드: 모든 페이지를 검색하여 전체 통계를 반환한다.

따라서 수행속도는 LIMITED > SAMPLED> DETAILED 모드의 순이 된다.

 

※시스템 함수를 사용하여 매개변수 값 지정 시 주의 사항

DB_ID 및 OBJECT_ID 함수를 사용하여 database_id 및 object_id 매개 변수 값을 지정할 수 있으나 이러한 함수에 유효하지 않은 값을 전달하면 의도하지 않은 결과가 발생할 수 있다. 존재하지 않거나 철자가 틀린 경우와 같이 데이터베이스 또는 개체 이름을 찾을 수 없는 경우에는 두 함수 모두 NULL을 반환된다. 따라서 sys.dm_db_index_physical_stats 함수에서는 NULL을 모든 데이터베이스나 모든 개체를 지정하는 와일드카드 값으로 해석하므로 주의해야 한다.

또한 OBJECT_ID 함수는 sys.dm_db_index_physical_stats 함수가 호출되기 전에 처리되므로 database_id에 지정된 데이터베이스가 아니라 현재 데이터베이스의 컨텍스트에서 계산된다. 이 동작으로 인해 OBJECT_ID 함수에서 NULL 값이 반환될 수 있고, 또한 개체 이름이 현재 데이터베이스 컨텍스트와 지정된 데이터베이스에 둘 다 있는 경우에는 오류 메시지가 반환될 수도 있다.

이러한 오작동을 방지하기 위해서는 OBJECT_ID(N'AdventureWorks.Person.Address') 처럼 database_name.owner.object_name 의 세 부분으로 된 구성 이름을 사용하거나 sys.dm_db_index_physical_stats 함수를 실행하기 전에 사전 검사를 하는 것이 좋다.

 

3. 반환되는 테이블

열 이름	데이터 형식	설명
database_id	smallint	테이블 또는 뷰의 데이터베이스 ID
object_id	int	인덱스가 있는 테이블 또는 뷰의 개체 ID
index_id	int	인덱스의 인덱스 ID   0 = 힙
partition_number	int	테이블, 뷰 또는 인덱스 등의 소유하는 개체 내의 파티션 번호   1부터 시작  1 = 분할되지 않은 인덱스 또는 힙
index_type_desc	nvarchar(60)	인덱스 유형에 대한 설명   HEAP CLUSTERED INDEX NONCLUSTERED INDEX PRIMARY XML INDEX SPATIAL INDEX XML INDEX
alloc_unit_type_desc	nvarchar(60)	할당 단위 유형에 대한 설명   IN_ROW_DATA LOB_DATA ROW_OVERFLOW_DATA   LOB_DATA: text, ntext, image, varchar(max), nvarchar(max), varbinary(max) 및 xml 형식의 열에 저장되는 데이터 ROW_OVERFLOW_DATA: varchar(n), nvarchar(n), varbinary(n) 및 sql_variant 형식의 열에 저장되는 행 외부로 밀어넣은 데이터
index_depth	tinyint	인덱스 수준의 수   1 = 힙 또는 LOB_DATA나 ROW_OVERFLOW_DATA 할당 단위
index_level	tinyint	인덱스의 현재 수준   인덱스 리프 수준, 힙 및 LOB_DATA 또는 ROW_OVERFLOW_DATA 할당 단위에 대해서는 0이다. 리프가 아닌 인덱스 수준의 경우 0보다 크다. index_level은 인덱스의 루트 수준에서 가장 높다. 리프가 아닌 인덱스 수준은 mode = DETAILED인 경우에만 처리된다.
avg_fragmentation_in_percent	float	인덱스의 논리적 조각화 또는 IN_ROW_DATA 할당 단위에서 힙의 익스텐트 조각화의 백분율    LOB_DATA 및 ROW_OVERFLOW_DATA 할당 단위에 대해서는 0이다. mode = SAMPLED인 경우 힙에 대해 NULL이다.   논리적 조각화 및 익스텐트 조각화에 대해서는 아래의 조각화 참조
fragment_count	bigint	IN_ROW_DATA 할당 단위의 리프 수준에 있는 조각 수   리프가 아닌 인덱스 수준과 LOB_DATA 또는 ROW_OVERFLOW_DATA 할당 단위에 대해서는 NULL이다. mode = SAMPLED인 경우 힙에 대해 NULL이다.
avg_fragment_size_in_pages	float	IN_ROW_DATA 할당 단위의 리프 수준에 있는 조각 하나의 평균 페이지 수   리프가 아닌 인덱스 수준과 LOB_DATA 또는 ROW_OVERFLOW_DATA 할당 단위에 대해 NULL이다. mode = SAMPLED인 경우 힙에 대해 NULL이다.
page_count	bigint	전체 인덱스 또는 데이터 페이지 수   인덱스의 경우 IN_ROW_DATA 할당 단위에서 B-트리의 현재 수준에 있는 총 인덱스 페이지 수, 힙의 경우 IN_ROW_DATA 할당 단위에서 총 데이터 페이지 수이다. LOB_DATA 또는 ROW_OVERFLOW_DATA 할당 단위의 경우 할당 단위에서 총 페이지 수이다.
avg_page_space_used_in_percent	float	모든 페이지에서 사용되는 사용 가능한 데이터 저장 공간의 평균 백분율   인덱스의 경우 IN_ROW_DATA 할당 단위에서 B-트리의 현재 수준에 평균이, 힙의 경우 IN_ROW_DATA 할당 단위에서 모든 데이터 페이지의 평균이다. LOB_DATA 또는 ROW_OVERFLOW DATA 할당 단위의 경우 할당 단위에서 모든 페이지의 평균이다. mode = LIMITED인 경우 NULL입니다.
record_count	bigint	총 레코드 수   인덱스의 경우 IN_ROW_DATA 할당 단위에서 B-트리의 현재 수준에 총 레코드 수가 적용되며, 힙의 경우 IN_ROW_DATA 할당 단위에서 총 레코드 수이다.   힙의 경우 이 함수에서 반환된 레코드 수는 힙에 대해 SELECT COUNT(*)를 실행하여 반환된 행 수와 다르다. 이는 한 행에 여러 레코드가 존재하기 때문이다.   LOB_DATA 또는 ROW_OVERFLOW_DATA 할당 단위의 경우 전체 할당 단위에서 총 레코드 수이다. mode = LIMITED인 경우 NULL이다.
ghost_record_count	bigint	할당 단위에서 삭제할 레코드 정리 태스크에 의해 제거될 삭제할 레코드 수   IN_ROW_DATA 할당 단위에서 리프가 아닌 인덱스 수준에 대해 0이다. mode = LIMITED인 경우 NULL이다.
version_ghost_record_count	bigint	할당 단위에서 처리 중인 스냅숏 격리 트랜잭션이 보유하고 있는 삭제할 레코드 수   IN_ROW_DATA 할당 단위에서 리프가 아닌 인덱스 수준에 대해 0이다. mode = LIMITED인 경우 NULL이다.
min_record_size_in_bytes	int	최소 레코드 크기(바이트)   인덱스의 경우 IN_ROW_DATA 할당 단위에서 B-트리의 현재 수준에 최소 레코드 크기가 적용되며, 힙의 경우 IN_ROW_DATA 할당 단위에서 최소 레코드 크기이다. LOB_DATA 또는 ROW_OVERFLOW_DATA 할당 단위의 경우 전체 할당 단위에서 최소 레코드 크기이다. mode = LIMITED인 경우 NULL이다.
max_record_size_in_bytes	int	최대 레코드 크기(바이트)   인덱스의 경우 IN_ROW_DATA 할당 단위에서 B-트리의 현재 수준에 최대 레코드 크기가 적용되며, 힙의 경우 IN_ROW_DATA 할당 단위에서 최대 레코드 크기이다. LOB_DATA 또는 ROW_OVERFLOW_DATA 할당 단위의 경우 전체 할당 단위에서 최대 레코드 크기이다. mode = LIMITED인 경우 NULL이다.
avg_record_size_in_bytes	float	평균 레코드 크기(바이트)   인덱스의 경우 IN_ROW_DATA 할당 단위에서 B-트리의 현재 수준에 평균 레코드 크기가 적용되며, 힙의 경우 IN_ROW_DATA 할당 단위에서 평균 레코드 크기이다. LOB_DATA 또는 ROW_OVERFLOW_DATA 할당 단위의 경우 전체 할당 단위에서 평균 레코드 크기이다. mode = LIMITED인 경우 NULL이다.
forwarded_record_count	bigint	다른 데이터 위치로의 전달 포인터가 있는 힙의 레코드 수   이 상태는 업데이트하는 동안 원본 위치에 새 행을 저장할 공간이 충분하지 않은 경우에 발생한다(Split).   힙의 IN_ROW_DATA 할당 단위 이외의 모든 할당 단위에 대해 NULL이다. mode = LIMITED인 경우 힙에 대해 NULL이다.
compressed_page_count	bigint	압축된 페이지 수   힙의 경우 새로 할당된 페이지는 압축된 페이지가 아니다. 힙은 데이터를 대량으로 가져오거나 힙을 다시 작성하는 경우의 두 가지 특별한 조건에서 압축된 페이지이다. 일반적으로 페이지 할당을 발생시키는 DML 작업은 압축된 페이지를 생성하지 않으며, 이 값이 원하는 임계값보다 커지면 힙을 다시 작성하는 것이 좋다.

 

인덱스 조각화 평가

avg_fragment_size_in_pages: 이 값이 클수록 범위 검색 성능이 좋아진다.

avg_fragment_size_in_pages: 이 값이 작을수록 범위 검색 성능이 좋아진다.

avg_fragmentation_in_percent: 이 값은 작을수록 범위 검색 성능이 좋아진다.

 

※조각화

조각화는 테이블에 정의된 인덱스에 대한 데이터 수정 작업(INSERT, UPDATE, DELETE)을 처리할 때 발생된다. 이러한 수정 작업은 B-Tree 인덱스의 밸런스를 무너뜨려 각 페이지의 사용률을 저하시킨다. 따라서 검색 시 읽어야 하는 페이지 수가 늘어나 성능에 영향을 미칠 수 있다. 이 경우 인덱스를 다시 구성하거나 작성할 필요가 있다. 이는 본 포스트에 언급하기에는 많은 내용이므로 다른 포스트에서 소개하도록 하겠다. 인덱스 재편성에 대한 내용은 일단 BOL의 인덱스 다시 구성 및 다시 작성 을 참조하기 바란다.

 

논리적 조각화

인덱스의 리프 페이지에서 순서가 잘못된 페이지의 비율을 의미한다.

인덱스의 리프 페이지는 Double-linked list 구조로 되어 있다. 예를 들어 101 페이지에는 "101 페이지의 다음 페이지는 102 페이지 입니다" 라는 포인터라는 포인터를, 102 페이지에는 "102 페이지의 이전 페이지에는 101페이지 입니다" 라는 포인터를 가지고 있어 서로를 가리킨다.

 

 

여기서 순서가 잘못된 페이지의 비율이란 물리적 페이지의 순서가 포인터가 가리키는 페이지의 순서가 다른 경우를 의미한다. 예를 들어, 인덱스의 리프 페이지가 101, 102, ... 110 순서로 10개가 있다고 가정할 경우, 101 페이지의 다음은 물리적으로 102 이지만 Split에 의해 논리적 순서가 101, 104, 102 ..  등과 같이 달라지기도 한다. 

익스텐트 조각화

힙의 리프 페이지에서 순서가 잘못된 익스텐트의 비율이다. 순서가 잘못된 익스텐트란 위에서 설명한 순서가 잘못된 인덱스의 경우를 보면 충분히 설명될 것이다.

 

실행 예

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