DMV를 이용한 인덱스 크기 및 조각화 정보 반환

DMV를 이용한 인덱스 크기 및 조각화 정보 반환

 

• Version: SQL Server 2005, 2008, 2008R2, 2012

 

동적 관리 뷰(DMV) sys.dm_db_index_physical_stats는 지정한 테이블 또는 뷰 데이터 및 인덱스에 대한 크기 및 조각화 정보를 반환한다. 저장소의 성격에 따라 다음과 같은 특징이 있다.

• 인덱스의 경우 B-tree 수준에 대해 행이 반환
• 힙의 경우 각 파티션의 IN_ROW_DATA 할당 단위에 대해 행이 반환
• LOG(Large Object)데이터의 경우 각 파티션의 LOB_DATA 할당 단위에 대한 행이 반환
• 테이블에 행-오버플로 데이터가 있는 경우 각 파티션의 ROW_OVERFLOW_DATA 할당 단위에 대한 행 반환
• xVelocoty 메모리 최적화 columnstore 인덱스에 대한 정보는 반환 하지 않음

 

[검색 모드]

함수가 실행되는 모드에 따라 함수에 사용되는 통계 데이터를 가져오기 위해 수행하는 검색 수준이 결정 된다. 탐색은 테이블이나 인덱스의 지정한 파티션을 구성하는 할당 단위에 대해 페이지 체인을 탐색 한다.

• LIMITED : 가장 빠른 모드이며 가장 적은 페이지수를 검색한다. 인덱스의 경우 B-tree 부모 수준 페이지만 검색한다. 힙의 경우 연결된 PFS 및 IAM 페이지가 검사되고 힙의 데이터 페이지는 LIMITED 모드로 검색 된다. 따라서 compressed_page_count가 NULL 이다.
• SAMPLED : SAMPLED 모드는 인덱스 또는 힙에서 모든 페이지의 1% 샘플을 기반으로 통계를 반환 한다.
• DETAILED : 모든 페이지를 검색하여 전체 통계를 반환 한다.

 

 

[조각화 검색]

조각화는 테이블에 정의된 인덱스에 대한 데이터 수정 작업(INSERT, UPDATE, DELETE)을 처리 할 때 발생하며 수정 사항은 테이블 및 인덱스의 행에서 균등하게 분산되지 않으므로 각 페이지 사용률이 시간에 따라 달라지게 된다. 테이블의 인덱스를 검색할 때 조각화로 인해서 읽어야 하는 페이지 수가 늘어날 수 있으며 데이터 병렬검색의 성능 저하시키기도 한다.

 

인덱스 또는 힙의 조각화 수준은 avg_fragmemtation_in_percent 열에 표시 된다. 힙의 경우 이 값은 힙의 익스텐트 조각화를 나타내며 인덱스의 경우 논리적 조각화를 나타낸다.

 

최상의 성능을 얻기 위해서는 avg_fragmemtation_in_percent 값이 0에 가까워야 한다. 일반적으로 0~ 10% 정도면 적당하다고 판단한다.

 

• 논리적 조각화 : 인덱스의 리프 페이지에서 순서가 잘못된 페이지의 비율. 인덱스에 할당된 다음 물리적 페이지가 현재 리프 페이지의 다음 포인터가 가리키는 페이지와 다른 경우를 나타낸다.
• 익스텐트 조각화 : 힙의 리프페이지에서 순서가 잘못된 익스텐트 비율. 힙의 현재 페이지가 들어 있는 익스텐트가 실제로 이전 페이지가 들어 있는 익스텐트의 다음 익스텐트가 아닌 경우.

 

 

다음 스크립트는 모든 매개변수에 와일드 카드인 NULL을 지정하여 SQL Server 인스턴스 내의 모든 테이블과 인덱스에 대한 통계를 반환 한다.

SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL); GO

 

 

 

[주의할 점]

동적 관리 뷰 sys.dm_db_index_physical_stats에서 주의 할 점은 고가용성 구성 중 AlwaysOn의 읽기 가능한 보조 복제본을 호스팅 하는 서버 인스턴스에서 호출 할 경우 지정된 사용자 테이블 또는 뷰에 대해 IS 잠금을 획득하여 REDO 스레드에서 해당 사용자 테이블 또는 뷰에 대한 X 잠금 요청이 차된 되기 때문에 REDO 차단 잠금 문제가 발생한다.

 

 

[참고자료]

• http://technet.microsoft.com/ko-kr/magazine/gg132708(en-us).aspx
• http://msdn.microsoft.com/ko-kr/library/ms188917.aspx

 

 

2013-07-04 / 강성욱 / http://sqlmvp.kr