Index Fragmentation

아래 내역은 Pro SQL Server Internals 의 내용과 google 검색 이미지를 추가, 편집한 내용입니다.

What's the Index Fragmentation?

---

인덱스 정보가 조각화 되어  Key 값을 기준으로 인덱스의 논리적 페이지 순서가 데이터 파일 내의 물리적 순서와 일치 하지 않음

심하게 조각화된 인덱스는 Query 성능을 저하시키고 Application 응답을 늦출 수 있음

 

 

Type of Fragmentation

---

 

• Internal Fragmentation : Data pages in the index have an excessive amount of free space.
  • 실제 사용량 보다 더 많은 공간이 할당 되었을 경우
  • It increases number of logical reads during query execution.
  • SQL Server uses more memory in the buffer pool to cache index pages.
  • Small degree of internal fragmentation : Reduces page splits during insert and update oprations.
  • Large degree of internal fragmentation : Wastes index space and reduce the performance of the system.
  • Indexes with ever-increasing keys : on identity column, internal fragmentation is not desirable. because the data is always inserted at the end of the index.
• External Fragmentaion : Logical order of pages does not match their physical order, logically subsequent pages are not located in the same or adjacent extents. 
  • 중간중간 사용하지 않거나 페이지 이동으로 실제 위치와 논리 위치가 맞지 않을 경우
  • It forces SQL Server to jump around reading the data from the disk which makes READ-AHEAD less efficient and increases the number of physical reads required.
  • It increases random disk I/O.

Analyze Fragmentation

---

• sys.dm_db_index_physical_stats
  • avg_page_space_used_in_percent : internal index fragmentation
  • avg-fragmentation_in_percent : external index fragmentation
  • fragment_count : indicates how many continuous data fragments the index has.
• 사용 예
  SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N'Test'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED'); 
  
  • Detailed mode는 서비스 중일 경우 사용 주의 필요
• Mode
  • Detailed : 전체 인덱스 페이지 스캔 (I/O 영향)
  • Sampled : 1만건 이상의 데이터 페이지가 있을 경우 1% 샘플링, 1만건 미만일 경우 Detailed 옵션 적용
  • Limited : 빠른 모드로 internal 조각화 정보는 제공하지 않음

 

Index Maintenance

---

• Reorganize

   

  • It reorders leaf-level data pages into their logical order.

  • It tries to compact pages by reducing their internal fragmentation.

  • Online

  • Alter Index Reorganize

• Rebuild

  • It removes external fragmentation by creating another index as a replacement of the old, fragmented one.

  • Clustered Index의 경우 삭제 후 다시 만들면 데이터가 재구성 되며 페이지가 다시 만들어 짐.

  • Offline

  • Enterprise Edition 에서는 Online으로도 수행이 가능하나 마지막에 Sch-M lock이 실행되기 때문에 OLTP 성 DB에서는 서비스 중 사용을 지양해야 함.

• Reorganize, Rebuild 작업 시 주의 사항

• OLTP 성 DB에서는 서비스 중 사용을 권장하지 않음
  

• 실행 계획에서 Table 또는 Index Scan을 사용하지 않을 경우, 조각화를 줄이는 것은 큰 효과가 없을 수 있음
  

• Transaction log가 늘어나므로 Transaction Backup 사이즈에 영향을 줄 수 있으며, HA/Replication 구성이 있을 경우 Network Traffic에도 영향을 줌

• HA 구성일 경우 failover 시 영향을 줌

• 축소 작업 중에 인덱스 일부나 전부가 이동한 경우 DBCC SHRINKFILE 또는 DBCC SHRINKDATABASE를 실행하면 조각화가 발생할 수 있음. 따라서 조각화를 제거하기 전에 축소 작업을 수행 필요.
  

• 작은 인덱스는 다시 작성하거나 다시 구성해도 조각화가 줄어들지 않는 경우가 많은데, 작은 인덱스의 페이지는 혼합 익스텐트에 저장되는 경우가 많으며, 혼합 익스텐트는 최대 8개의 개체가 공유할 수 있으므로 인덱스를 다시 작성하거나 다시 구성한 후에도 작은 인덱스의 조각화가 줄어들지 않을 수 있다.
  

•  Index Fragmentation 해소 작업 기준 예)
• avg_fragmentation_in_percent 값이 10~30%미만 일 경우 reorganize 수행 및 통계 업데이트 실행
  

• avg_fragmentation_in_percent 값이 30% 이상 일 경우 reindex 수행 

 

참조

---

• sys.dm_db_index_physical_stats : SQL Server Page 의 index 정보 참조

  
  
• SQL Server 2012부터 page-split 알고리즘 향상으로 page split 시 새로운 페이지 allocation 작업이 줄어듬
  Page split 참조.
  
  
• Memory Fragmentation 해결 방법 (Index Fragmentation과 비슷한 이론이 있어 참고용으로 첨부)
  • 페이징(Paging) - 가상메모리사용, 외부 단편화 해결, 내부 단편화 존재보조기억장치를 이용한 가상메모리를 같은 크기의 블록으로 나눈 것을 페이지라고 하고 RAM을 페이지와 같은 크기로 나눈 것을 프레임이라고 할 때,페이징 기법이란 사용하지 않는 프레임을 페이지에 옮기고, 필요한 메모리를 페이지 단위로 프레임에 옮기는 기법.페이지와 프레임을 대응시키기 위해 page mapping과정이 필요해서 paging table을 만든다.페이징 기법을 사용하면 연속적이지 않은 공간도 활용할 수 있기 때문에 외부 단편화 문제를 해결할 수 있다.대신 페이지 단위에 알맞게 꽉채워 쓰는게 아니므로 내부 단편화 문제는 여전히 있다.* 페이지 단위를 작게하면 내부 단편화 문제도 해결할 수 있겠지만 대신 page mapping 과정이 많아지므로 오히려 효율이 떨어질 수 있다. 
  • 세그멘테이션(Segmentation) - 가상메모리사용, 내부 단편화 해결, 외부 단편화 존재페이징기법에서 가상메모리를 같은 크기의 단위로 분할했지만 세그멘테이션기법에서는 가상메모리를 서로 크기가 다른 논리적 단위인 세그먼트로 분할해서 메모리를 할당하여 실제 메모리 주소로 변환을 하게 된다.각 세그먼트는 연속적인 공간에 저장되어 있다.세그먼트들의 크기가 다르기 때문에 미리 분할해 둘 수 없고 메모리에 적재될 때 빈 공간을 찾아 할당하는 기법이다.마찬가지로 mapping을 위해 세그먼트 테이블이 필요하다.(각 세그먼트 항목별 세그먼트 시작주소와 세그먼트의 길이 정보를 가지고 있음)프로세스가 필요한 메모리 만큼 할당해주기 때문에 내부단편화는 일어나지 않으나 여전히 중간에 프로세스가 메모리를 해제하면 생기는 구멍, 즉 외부 단편화 문제는 여전히 존재한다.
  • 메모리 풀(Memory Pool)
    필요한 메모리 공간을 필요한 크기, 개수 만큼 사용자가 직접 지정하여 미리 할당받아 놓고 필요할 때마다 사용하고 반납하는 기법메모리 풀 없이 동적할당과 해제를 반복하면 메모리의 랜덤한(실제로는 알고리즘에 의한) 위치에 할당과 해제가 반복되면서 단편화를 일으킬 수 있겠지만 미리 공간을 할당해놓고 가져다 쓰고 반납하기 때문에 할당과 해제로 인한 외부 단편화가 발생하지 않는다.또한 필요한 크기만큼 할당을 해놓기 때문에 내부 단편화 또한 생기지 않는다.하지만 메모리 단편화로 인한 메모리 낭비량보다 메모리 풀을 만들었지만 쓰지 않았을 때 메모리 양이 커질 경우 사용하지 않아야 한다.메모리의 할당, 해제가 잦은 경우에 메모리 풀을 쓰면 효과적이다.미리 할당해놓고 사용하지 않는 순간에도 계속 할당해놓으므로 메모리 누수가 있는 방식이다.