INDEX 조각화( Rebuild , Reorganize )

PAGE SPLIT

---

INSET , UPDATE 동작이 발생 시 여유 공간의 부족으로 페이지 변화가 일어나는 것

• 저번에 올린 부분과 같이 PAGE = SQL Server 기본 I/O 단위 / 1Page = 8KB = 8016byte / 8page = 1Extent

CREATE TABLE PageSplitTest
(
    --int: 4byte / --nchar(1000) 2,000byte
    Id            int         not null
    , TextChar    nchar(1000) not null
)
GO
 
--클러스터형 인덱스 생성
CREATE CLUSTERED INDEX CL_ID ON PageSplitTest (Id)
GO

--하나의 row당 4 + 2000 = 2,004byte
--1Page(8KB) = 8192byte 
--4개의 row를 입력하면 8,016byte를 차지하기 때문에 그 다음 row는 같은 페이지에 들어갈 공간이 없게 된다.
INSERT INTO PageSplitTest VALUES(1, '고진감래')
INSERT INTO PageSplitTest VALUES(2, '전화위복')
INSERT INTO PageSplitTest VALUES(3, '승승장구')
INSERT INTO PageSplitTest VALUES(4, '각곡유목')

-- before add data
select  db_name() 
		, object_name(object_id) 
		, case index_id when 0 then 'heap' 
			when 1 then 'cluster_index' 
			else 'noncluster'  end
		, is_allocated
		, allocated_page_file_id
		, allocated_page_page_id
		, page_type_desc
		, page_level
		, previous_page_page_id
		, next_page_page_id
from sys.dm_db_database_page_allocations(db_id(),object_id('PageSplitTest'),1,null,'DETAILED')

-- page추가를 위해 data 입력
INSERT INTO PageSplitTest VALUES(5, '대기만성')
 
 -- after add data
select  db_name() 
		, object_name(object_id) 
		, case index_id when 0 then 'heap' 
			when 1 then 'cluster_index' 
			else 'noncluster'  end
		, is_allocated
		, allocated_page_file_id
		, allocated_page_page_id
		, page_type_desc
		, page_level
		, previous_page_page_id
		, next_page_page_id
from sys.dm_db_database_page_allocations(db_id(),object_id('PageSplitTest'),1,null,'DETAILED')
order by page_level desc , page_type_desc desc

select *  from sys.dm_db_database_page_allocations(db_id(),object_id('PageSplitTest'),1,null,'DETAILED')

Index Fragmentation(조각화)

---

DISK 상에 PAGE가 연속하지 않고, 떨어져 있는 상태(연관된 PAGE 정보가 하나의 EXTENT 가 아닌 경우 )
INSERT , UPDATE , DELETE 로 인하여 데이터들이 비연속적인 PAGE 로 분산 -> I/O 증가 -> 성능 저하

 

Index Reorganize

기존에 사용되던 PAGE 정보를 순서대로 구성

• Resource 사용이 적다
• Object-level-locks 발생하지 않는다
• 작업 중 Query 및 Update 가 가능하다.

ALTER INDEX [IndexName] ON [dbo].[TableName] REORGANIZE WITH ( LOB_COMPACTION = ON )

# LOB_COMPACTION = ON 옵션은 LOB 데이터 타입(대용량 데이터)에 대한 압축 작업을 진행한다는 의미

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

Index Rebuild

Index 삭제 -> 재생성

• Full scan하여 모든 row data 확인 및 통계 update -> 성능 향상
• 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가 다른 디스크에 위치해 있는 경우 인덱스 리빌드 시간 단축 가능​

 

Page Density(밀도)

---

페이지 별로 row data가 얼마나 차지 하는지를 나타내는 지표

• 밀도가 100%인 한 페이지가 새 Row를 수용하기 위해서는 두 Page로 분할되는데, 이렇게 새롭게 만들어지는 두 Page의 밀도는 약 50%
• 동일한 양의 Row Data 가 있더라도 밀도가 낮으면 -> Page 증가 -> cache memory 요구량 증가 -> 성능 저하
• 쿼리 옵티마이저는 쿼리 플랜을 컴파일할 때, 쿼리 수행에 필요한 데이터를 Read 하는 데에 필요한 I/O 비용을 고려하게 된다. 페이지 밀도가 낮아지면 I/O 비용이 높아지므로 이는 쿼리 플랜에까지 영향을 미쳐, 불필요하게 동일한 쿼리에 대해 비효율적인 쿼리 플랜이 생성될 수 있음
• 많은 Workload에서 페이지 밀도를 높이는 것이 인덱스 조각화를 줄이는 것보다 성능에 더 긍정적
• 채우기 비율을 100% 또는 0% 이외의 값으로는 설정하지 않는 것이 바람직

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, -- 평균 Page 밀도
       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;

 

 

참고

https://bebeya.tistory.com/entry/MSSQL-%EC%9D%B8%EB%8D%B1%EC%8A%A4-%EB%8B%A8%ED%8E%B8%ED%99%94-%ED%99%95%EC%9D%B8-%EB%A6%AC%EB%B9%8C%EB%93%9C-%EC%A7%84%ED%96%89-100-%ED%9A%A8%EA%B3%BC

[MSSQL] 인덱스 단편화 확인 & 리빌드 진행 100% 효과

https://datalibrary.tistory.com/128

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

https://zetastring.tistory.com/120

MSSQL 인덱스 조각화 확인 및 해결책(DBCC SHOWCONTIG, ALTER INDEX REBUILD)

https://co-no.tistory.com/16