데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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 차단 잠금 문제가 발생한다.

 

 

[참고자료]

 

 


강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1810 DMV를 이용한 CPU 사용량 높은 쿼리 찾기 jevida(강성욱) 2016.09.29 4395
» DMV를 이용한 인덱스 크기 및 조각화 정보 반환 jevida(강성욱) 2016.09.29 1156
1808 Checkpoint 추적하기 jevida(강성욱) 2016.09.29 1285
1807 중복 인덱스와 성능(Duplicate Indexes with Performance) jevida(강성욱) 2016.09.29 2237
1806 823, 824, 825, 832 오류 (DISK IO 오류) jevida(강성욱) 2016.09.29 2139
1805 DISK I/O 병목 확인 jevida(강성욱) 2016.09.29 3719
1804 SQL Server 2012에서 비상계정 생성하기 - 비밀번호를 잊어 버렸을 경우 대처하기 jevida(강성욱) 2016.09.29 1251
1803 SQL Server 차단 최소화 jevida(강성욱) 2016.09.29 1129
1802 자주 사용되는 System 함수 jevida(강성욱) 2016.09.29 1070
1801 프로시저와 임시테이블, 그리고 리컴파일 jevida(강성욱) 2016.09.29 2399
1800 access check cache 크기에 따른 성능 문제 jevida(강성욱) 2016.09.29 1049
1799 Hot Add CPU jevida(강성욱) 2016.09.29 849
1798 스레드 및 파이버 실행 jevida(강성욱) 2016.09.29 1000
1797 CPU에 스레드 할당 및 lightweight pooling 옵션 사용 jevida(강성욱) 2016.09.29 1695
1796 스레드 및 태스크 아키텍처 jevida(강성욱) 2016.09.29 1392
1795 메모리 관리 아키텍처 – NUMA 버퍼 풀 증가 및 축소 jevida(강성욱) 2016.09.29 1231
1794 메모리 관리 아키텍처 – NUMA 지원 방법 jevida(강성욱) 2016.09.29 1545
1793 메모리 관리 아키텍처 – NUMA(Non-Uniform Memory Access)이해 jevida(강성욱) 2016.09.29 1416
1792 메모리 관리 아키텍처 – Hot Add 메모리 jevida(강성욱) 2016.09.28 929
1791 메모리 관리 아키텍처 – 버퍼 관리_페이지 쓰기 jevida(강성욱) 2016.09.28 1053





XE Login