[MS-SQL] 인덱스 단편화 조회 및 리빌드

 

프로그램을 운영하다보면 특정 조회 조건에서 이상하리만큼 속도 저하가 발생하는 경우들이 간혹 있다. 서버 메모리나 네트워크 등 지목할만한 원인이 없고, 조회되는 테이블조차도 특이사항이 없는데도 말이다. 이럴 때 인덱스 단편화 조회 및 인덱스 리빌드를 확인 및 진행해주면 원활하게 운영되는 경우들이 있다.  그래서 오늘은 인덱스 리빌드를 확인하고자 한다.

 

단편화 ( Fragmentation )

• 기억 장치의 빈 공간 또는 자료가 여러 개의 조각으로 나뉘는 현상을 말한다. 이 현상은 기억장치의 사용 가능한 공간을 줄이거나, 읽기와 쓰기의 수행속도를 늦추는 문제점을 야기한다.

SELECT TOP (50)
  DB_NAME() AS 'Database_Name',
  Object_Name(SYS_DM.OBJECT_ID) AS 'Table_name',
  sys_index.name as 'Index_name',
  Round(avg_fragmentation_IN_Percent,2) AS 'Fragmentation %'
FROM sys.dm_db_index_physical_stats(db_id(), null, null, null, null) AS Sys_dm
Inner join sys.indexes as sys_index
on sys_dm.object_id = sys_index.object_id and sys_dm.index_id = sys_index.index_id
where sys_dm.database_id = db_id()
and sys_index.name is not null
and objectproperty(sys_dm.object_id, 'IsMsShipped') = 0
order by [Fragmentation %] desc;

 

인덱스 단편화 조회쿼리를 이용하여 단편화를 조회한다. Fragmentation % 는 낮을수록 좋다.

인덱스 단편화 조회

 

인덱스 용량, 상세 확인

• 인덱스별 용량이나 상세 확인을 통해 리빌딩을 하기 전에 해당 인덱스가 얼마나 사용되고 있으며, 조각화는 얼마나 일어났는지 등을 확인할 수 있다.

-- 인덱스별 용량 확인
sp_MSindexspace [테이블명]

-- 인덱스 상세 확인
DBCC SHOWCONTIG('테이블명', '인덱스명')

 

테이블에 사용되는 인덱스 용량확인

 

인덱스 상세 확인

 

첫번째 테이블별 인덱스 구성에서 Index ID [2] 번의 인덱스를 인덱스 상세확인을 진행하였다. 인덱스 리빌딩을 처리한 지 얼마되지 않은 시점이라 최적의 상태를 반환하고 있는 것으로 보여진다.

 

인덱스 리빌드 ( Index Rebuild )

• 조각화가 많이 일어난 인덱스를 리빌드 함으로써 검색 최적화 상태가 될 수 있도록 한다.

-- 첫번쨰 방법
dbcc indexdefrag('DB명', '테이블명', '인덱스명')

-- 두번째 방법
alter index all on 테이블명 rebuild with (PAD_INDEX=on, FILLFACTOR=90)

 

첫번째 방법으로는 특정 인덱스가 문제됨을 명확하게 판단하고, 처리를 해야 개선의 효과를 볼 수 있겠지만 업무를 하다보면 연관성이 높아 특정 인덱스만을 처리한다고 개선을 효과가 높지는 않은 것 같다.

이에 두번째 방법을 주로 이용하여 사용빈도가 높은 테이블별 인덱스를 리빌드 함으로써 개선의 효과를 찾아갈 수 있도록 자주 처리를 한다.

 

데이터 베이스 전체 인덱스 리빌드

• 이미 전체적인 테이블의 인덱스 조각화가 많이된 상태라면 사용자가 사용하는 시점을 피해 전체 인덱스 리빌드를 처리하는 것도 개선의 도움이 된다.

USE DB명

DECLARE @i int, @sql varchar(1000)
DECLARE @tablename varchar(1000), @ownerName varchar(1000)

SET @i = 1
SET @ownerName = 'DB명'

DECLARE DB_Cursor CURSOR FOR
 SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER
 BY TABLE_SCHEMA, TABLE_NAME
 OPEN DB_Cursor

 FETCH NEXT FROM DB_Cursor
INTO @ownerName, @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sql = 'ALTER INDEX ALL ON ' + @ownerName + '.' + @tablename + ' REBUILD WITH (PAD_INDEX = ON, FILLFACTOR = 90 ) '
	EXEC (@sql)

	PRINT CONVERT(VARCHAR, @I) + '__' + @ownerName + '.' + @tablename + '..........OK'
	SET @i = @i + 1

	FETCH NEXT FROM DB_Cursor
	INTO @ownerName, @tablename

END

CLOSE DB_Cursor
DEALLOCATE DB_Cursor

 

주기적으로 스케줄러를 걸어서 새벽에 인덱스 리빌드를 처리하는데, 프로그램 속도 저하를 어느정도 개선하는 것으로 확인된다.

 

그래도 무엇보다 서버 하드웨어가 최고인 게 제일 좋은 것 같긴하다...