[MSSQL] 인덱스 유지 및 관리, 최적화

본 포스팅에서는 쿼리 성능 향상 및 리소스 소비 감소를 위한 인덱스 최적화에 대해서 알아볼 것이다.

DB에서 테이블에 인덱스를 생성하고 나면, 생성한다고 끝나는 것이 아닌, 주기적인 관리가 필요하며 이러한 관리가 제대로 되지 않으면 인덱스를 쓰지 않는것만도 못하게 된다. 따라서 어떤 식으로 관리해야 되는지에 대해 알아보자!

---

우선 인덱스 관리의 필요성을 알아보기 위해, 인덱스 조각화(Index Fragmentation)에 대해 알아보자!

 

인덱스 조각화(Index Fragmentation)란?

: 디스크 상에 Page들이 연속적으로 위치해 있지 않고(서로 연관된 데이터들이 한 Extent로 묶이지 않는), 공간을 두고 떨어져 있는 현상을 말한다. 인덱스를 처음 생성했을 때에는 연속해서 서로 연관된 데이터들끼리 연속된 Page들로 구성되어 있지만, 데이터의 변경(INSERT, UPDATE, DELETE)이 일어나다 보면, 나중에는 데이터들이 비연속적인 Page들에 분산되게 되어, 해당 데이터를 이용할 때 Disk I/O가 더 많이 발생하게 되고 이에 따라 DB의 성능이 떨어지게 된다.

마이크로소프트 공식 문서에서는 다음과 같이 설명한다.

• B-Tree(Rowstore에 해당) 인덱스에서, 조각화는 인덱스의 키 값을 기반으로 인덱스 내의 논리적 순서가 인덱스 Page의 물리적 순서와 일치하지 않는 Page가 인덱스에 있는 경우 발생한다.
• DB 엔진에서는 기본적으로 데이터에 INSERT, UPDATE, DELETE 작업이 수행될 때마다 인덱스를 자동으로 변경한다. 예를 들면, 테이블에 Row를 추가하면 Rowstore 인덱스의 기존 Page가 분할되어 새 Row를 삽입할 공간이 생길 수 있는데, 이러한 수정 작업이 거듭되면 시간이 흐름에 따라 인덱스의 데이터가 조각화되어 DB 내에 흩어지게 되는 것이다.
• 스토리지 Subsystem이 제공하는 순차적 I/O의 성능이 DBMS의 무작위 I/O 성능보다 더 나은 경우가 되면, 인덱스 조각화로 인해 성능이 이미 저하되었음을 의미한다. 이는 조각화된 인덱스로 인해 데이터를 읽을 때 무작위 I/O가 더 많이 필요하다는 것을 의미하기 때문이다. 

---

다음은 물리적인 저장 위치인 Page들에 대해서 실제 우리의 데이터들이 얼마나 서로 멀리 떨어져 있는가를 알아볼 수 있는 '페이지 밀도' 라는 개념에 대해 알아보자!

 

페이지 밀도(Page Density)란?

: DB에서의 각 Page는 가변적인 Row 수를 가진다. Row들이 한 Page의 모든 공간을 차지하는 경우 페이지 밀도는 100%이다. Page가 데이터들 없이 비어있는 경우는 페이지 밀도가 0% 이다. 밀도가 100%인 한 페이지가 새 Row를 수용하기 위해서는 두 Page로 분할되는데, 이렇게 새롭게 만들어지는 두 Page의 밀도는 약 50%가 된다.

• 페이지 밀도가 낮은 경우, 동일한 양의 데이터(Row)를 저장하려면 Page가 더 많이 필요하게 된다. 더 많은 Page가 필요하게 된다는 것은, 데이터를 읽고 쓰는 데 더 많은 Page를 왔다 갔다 해야 되므로, 더 많은 디스크 I/O가 필요하며, 데이터를 캐싱하는 데 더 많은 메모리가 필요하다는 것을 의미한다. 따라서 페이지 밀도가 낮다는 것은 DB 성능에 부정적인 영향을 준다는 것을 의미한다.
• DB 엔진이 페이지에 Row를 추가할 때 인덱스의 채우기 비율이 100%가 아닌 값으로 설정되어 있다면, 페이지를 완전히 채우지 않아 낭비하게 되고, 이에 따라 페이지 밀도 역시 낮아진다.
• 쿼리 옵티마이저는 쿼리 플랜을 컴파일할 때, 쿼리 수행에 필요한 데이터를 Read 하는 데에 필요한 I/O 비용을 고려하게 된다. 페이지 밀도가 낮아지면 I/O 비용이 높아지므로 이는 쿼리 플랜에까지 영향을 미쳐, 불필요하게 동일한 쿼리에 대해 비효율적인 쿼리 플랜이 생성될 수 있음을 의미한다.
  
  ※ 팁 : 많은 Workload에서 페이지 밀도를 높이는 것이 인덱스 조각화를 줄이는 것보다 성능에 더 긍정적인 영향을 준다. 페이지 밀도를 불필요하게 낮추지 않으려면, 페이지 분할 수가 많은 인덱스(예: 비연속 GUID 값을 포함하는 선행 Column이 포함된, 수정이 빈번한 인덱스 등)와 같은 특정한 경우를 제외하고는, 채우기 비율을 100% 또는 0% 이외의 값으로는 설정하지 않는 것이 바람직하다.

---

인덱스 조각화 및 페이지 밀도 측정

 

"그렇다면 위에서 알아본 인덱스 조각화가 얼만큼 진행되었는지, 페이지 밀도가 어느정도인지는 어떻게 알 수 있을까?!"

 

인덱스 조각화는 인덱스 방식에 따라 조회하는 방식이 다르다. 인덱스 방식은 rowstore와 columnstore로 나뉘는데, 대부분의 인덱스 방식은 rowstore이다.

 

rowstore방식의 인덱스 조각화 여부 확인 방법은 sys.dm_db_index_physical_stats() 메소드를 활용하여 조회한다.
SQL문 예시는 다음과 같다.

SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       ips.avg_fragmentation_in_percent,
       ips.avg_page_space_used_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc
FROM sys.dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i 
ON ips.object_id = i.object_id
   AND
   ips.index_id = i.index_id
ORDER BY page_count DESC;

여기서 avg_fragmentation_in_percent는 논리적 조각화(인덱스에서 순서가 잘못된 Page) 정도를 나타내고, 
avg_page_space_used_in_percent는 평균 Page 밀도를 나타낸다.

 

columnstore 인덱스 방식의 조각화 정도는 전체 행 대비 삭제된 행의 비율로 정의되며 백분율료 표시된다.
이는 sys.dm_db_column_stroe_row_group_physical_stats() 메소드를 통해 확인할 수 있으며,
이 메소드 반환 결과 column으로 total_rows, deleted_rows 가 있다.

total_rows는 행 그룹에 물리적으로 저장된 행 수를 나타내는데, 압축된 행 그룹의 경우 삭제된 것으로 표시된 행도 포함된다.
deleted_rows는 삭제되도록 표시된 압축된 행 그룹에 물리적으로 저장된 행의 수를 나타낸다. 델타 저장소에 있는 행 그룹의 경우 0이다.

 

압축된 행 그룹의 인덱스 조각화 정도는 아래 수식을 사용하여 계산된다.

100.0*(ISNULL(deleted_rows,0))/NULLIF(total_rows,0)

columnstore 인덱스 방식의 조각화 여부 확인은 아래 SQL문으로 한다.

SELECT OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
       OBJECT_NAME(i.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       100.0 * (ISNULL(SUM(rgs.deleted_rows), 0)) / NULLIF(SUM(rgs.total_rows), 0) AS avg_fragmentation_in_percent
FROM sys.indexes AS i
INNER JOIN sys.dm_db_column_store_row_group_physical_stats AS rgs
ON i.object_id = rgs.object_id
   AND
   i.index_id = rgs.index_id
WHERE rgs.state_desc = 'COMPRESSED'
GROUP BY i.object_id, i.index_id, i.name, i.type_desc
ORDER BY schema_name, object_name, index_name, index_type;

---

이제 인덱스 조각화 정도를 알아봤으니, 본격적으로 인덱스 조각화를 해결하는 방법에 대해 알아보자!

 

인덱스 유지 관리 - Reorganization(재구성) / Rebuilding(재생성)

인덱스 유지 관리의 방법으로는 크게 Reorganization과 Rebuilding으로 나뉜다.

 

• 인덱스 Reorganization(재구성)
  - 기존에 사용되던 Page 정보를 순서대로 다시 구성하는 작업
  - Rebuilding보다 리소스가 덜 사용되므로, 이 방법을 기본 인덱스 유지 관리 방법으로 사용하는 게 바람직함.
  - 온라인 작업이기 때문에, 장기간의 object-level locks가 발생하지 않으며 Reorganization 작업 중에 기본 테이블에 대한 쿼리나 업데이트 작업을 계속 진행할 수 있다.
  - 인덱스 재구성 쿼리
  

  ALTER INDEX [IndexName] ON [dbo].[TableName] REORGANIZE WITH ( LOB_COMPACTION = ON )
  
  # LOB_COMPACTION = ON 옵션은 LOB 데이터 타입(대용량 데이터)에 대한 압축 작업을 진행한다는 의미.​

  
  

  # 테이블의 모든 인덱스 재구성
  ALTER INDEX ALL ON [TableName] REORGANIZE;

  
  
  - rowstore 인덱스 방식의 경우, DB 엔진이 테이블 및 뷰에 대한 클러스터 및 비클러스터 인덱스를 조각모음하는데, 이는 리프(Leaf) 노드의 논리적 순서(왼쪽에서 오른쪽)에 맞게끔 리프 수준에서의 Page만 물리적으로 정렬한다. 
  - columnstore 인덱스 방식의 경우, 시간 경과에 따라 데이터 변경이 잦아지면서 델타 저장소가 여러 개의 작은 row 그룹으로 분할될 수 있다. 때문에, 소규모의 압축된 행 그룹의 데이터들을 대규모의 행 그룹으로 결합시키고, 이 과정에서 삭제된 것으로 표시되는 row를 물리적으로 제거한다. 이러한 과정으로 인해 CPU 리소스가 추가로 요구될 수 있으며, 작업 실행 동안 전체 시스템 성능의 저하가 유발될 수 있다.
  
  
• 인덱스 Rebuilding(재생성)
  - 기존의 인덱스를 삭제하고 재생성하는 방법.
  - 인덱스의 모든 Row가 검사되며, 통계도 업데이트(Full-Scan) 되어 최신 상태가 되므로, 기본 샘플링된 통계 업데이트에 비해 DB의 성능이 향상되는 경우도 있다.
  - 일반적으로 일정 수준 이상의 조각화(약 30% 이상)가 발생한 경우에는 인덱스 리빌딩을 진행해야 인덱스 조각화를 해결할 수 있다.
  - 인덱스 유형 및 DB 엔진에 따라 온라인/오프라인으로 나뉘며, 오프라인 인덱스 리빌딩은 온라인 방식에 비해 시간은 덜 걸리지만, 리빌딩 작업 중에 object-level의 Lock을 유발함.
  - 인덱스 리빌딩 쿼리
  

  ALTER INDEX [IndexName] ON [dbo].[TableName] REBUILD PARTITION = ALL
  WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF,
  ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
  
  
  # PAD_INDEX = OFF)
  # Fill Factor 설정(페이지의 어느 정도의 공간을 비워 둘 것인지 결정, 기본 값은 0)은 Leaf 노드에서만 적용이 되는데, 
  # PAD_INDEX = ON으로 설정하는 경우에는 intermediate 노드에도 Fill Factor 설정이 적용.
   
  # STATISTICS_NORECOMPUTE = OFF)
  # 통계 자동 업데이트 비활성화(대용량 테이블의 경우 통계 자동 업데이트 활성화 시켜 두면 성능 저하 발생 가능)
   
  # SORT_IN_TEMPDB = OFF)
  # 인덱스를 생성하거나 리빌드할 때 발생하는 정렬 동작의 중간 결과값을 tempdb에 저장할 것인지 결정
  # 사용하는 데이터베이스와 tempdb가 다른 디스크에 위치해 있는 경우 인덱스 리빌드 시간 단축 가능​

  
  - rowstore 인덱스 방식의 경우, 리빌딩시 인덱스의 모든 level에서 조각화가 제거되며, 지정된 비율 또는 현재 채우기 비율에 따라 Page가 압축됨. 또한 오프라인으로 리빌딩시 비클러스터형 인덱스에서의 불일치를 해결하여 인덱스 손상 복구도 가능함.
  - columnstore 인덱스 방식의 경우, 리빌딩시 조각화가 제거되고, 델타 저장소 row가 columnstore로 옮겨지며, 삭제 표시가 된 행이 물리적으로 삭제됨.

 

 

참고

https://docs.microsoft.com/ko-kr/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15 

성능을 향상시키고 리소스 사용률이 감소되도록 인덱스를 최적으로 유지 관리 - SQL Server

https://sqltalk.tistory.com/6

인덱스 조각화

https://lovedb.tistory.com/130

[SQL Server] 가장 조각이 많이 난 인덱스 TOP 10

 

https://laigo.kr/646

인덱스 Fragmentation(조각화) 상태 확인

https://datalibrary.tistory.com/128

[SQL Server] 인덱스 재구성 vs 인덱스 리빌드 차이 한 눈에 이해하기