MSSQL Index(인덱스) 조각화 확인 및 rebuild 방법

Index는 성능 향상을 위해서 테이블에 index를 생성했다고 끝나는 게 아니라 주기적으로 최적화 작업을 해줘야 합니다.
즉, 주기적인 관리를 하지 않는 경우 다시 성능 저하가 발생하거나 사용하지 않는 index가 저장공간만 차지합니다.

 

인덱스 조각화(Index Fragmentation)?

• 처음 인덱스를 생성했을 때는 연속해서 서로 연관된 데이터들끼리 Page로 구성되어 있지만, 논리적 순서의 index Page의 물리적 순서와 일치하지 않는 Page가 index에 있는 경우 발생합니다.
• database에서는 테이블에 INSERT, UPDATE, DELETE 작업이 수행될 때마다 인덱스를 자동으로 변경합니다.
• 그러나 테이블에 Row를 추가하면 Rowstore 인덱스의 기존 Page가 분할되어 새 Row를 삽입할 공간이 생길 수 있는데, 이러한 수정 작업이 반복되면서 인덱스의 데이터가 조각화되어 database 내에 index Page의 물리적 순서가 흩어지게 됩니다.
• index Page에 분산되게 되면, 해당 데이터를 검색할 때 Disk I/O가 더 많이 발생하게 되고 이에 따라 database의 성능이 떨어지게 됩니다.

 

rowstore Index, column store Index 특징

인덱스 조각화는 rowstore와 columnstore 나눠서 확인할 수 있습니다.
rowstore 인덱스는 작은 범위의 데이터 검색에 적합하고, columnstore 인덱스는 대용량 데이터 처리와 분석에 유용합니다.

rowstore Index 특징.

• 테이블의 행 단위 데이터를 물리적으로 저장합니다. 
• 주로 작은 범위의 데이터를 검색하는 쿼리에 적합합니다.
• 행 기반 스토리지를 사용하므로 특정 값을 검색할 때 빠릅니다.
• 데이터를 행 단위로 저장하므로 압축 효과가 작습니다.
• ALL 지정되면 테이블의 모든 인덱스가 삭제되고 단일 작업에서 다시 작성됩니다. 

columnstore Index 특징.

• 열 기반 데이터 스토리지를 사용합니다. 
• 데이터를 열 단위로 압축하여 저장하므로 대용량 데이터를 효율적으로 처리하며, 분석 쿼리 최적화와 실시간 집계에 적합합니다.
• 데이터를 열 단위로 압축하여 저장하므로 저장공간을 절약하고 빠른 쿼리 성능을 제공합니다.

 

인덱스 조각화(Index Fragmentation) 확인 방법

rowstore Index 확인 방법.

• avg_fragmentation_in_percent: 논리적 조각화(인덱스에서 순서가 잘못된 Page) 정도를 표시합니다. 
• avg_page_space_used_in_percent: 평균 Page 밀도를 표시합니다.
• total_rows: 행 그룹에 물리적으로 저장된 행 수를 나타내는데, 압축된 행 그룹의 경우 삭제된 것으로 표시된 행도 포함합니다.
• deleted_rows: 삭제되도록 표시된 압축된 행 그룹에 물리적으로 저장된 행의 수를 표시합니다. 
• 소스 코드로 확인.

-- Transact-SQL을 사용하여 rowstore 인덱스의 조각화 및 페이지 밀도 확인
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;

 

columnstore Index 확인 방법.

• columnstore 인덱스 방식의 조각화 정도는 전체 행 대비 삭제된 행의 비율로 정의되며 백분율로 표시된다.
• sys.dm_db_column_stroe_row_group_physical_stats() 메서드를 통해 확인할 수 있습니다.
• 소스 코드로 확인.

-- Transact-SQL을 사용하여 columnstore 인덱스의 조각화 확인
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;

 

 

Index rebuild 방법

인덱스 다시 구성 방법.

AurumGuide_Fragmentation 테이블의 IX_AurumGuide_Fragmentation_01 인덱스를 다시 구성합니다.

 ALTER INDEX IX_AurumGuide_Fragmentation_01
    ON dbo.AurumGuide_Fragmentation
    REORGANIZE;

 

테이블의 모든 인덱스 다시 구성 방법.

데이터베이스의 테이블 dbo.AurumGuide_Fragmentation 모든 인덱스를 다시 구성합니다.

ALTER INDEX ALL ON dbo.AurumGuide_Fragmentation
   REORGANIZE;

 

인덱스 다시 작성 방법.

AurumGuide_Fragmentation 테이블의 단일 인덱스를 다시 작성합니다.

ALTER INDEX PK_AurumGuide_Fragmentation_ID ON dbo.AurumGuide_Fragmentation
REBUILD
;

테이블의 모든 인덱스 다시 작성 방법.

ALTER INDEX ALL ON dbo.AurumGuide_Fragmentation
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON)
;