프로그램을 운영하다보면 특정 조회 조건에서 이상하리만큼 속도 저하가 발생하는 경우들이 간혹 있다. 서버 메모리나 네트워크 등 지목할만한 원인이 없고, 조회되는 테이블조차도 특이사항이 없는데도 말이다. 이럴 때 인덱스 단편화 조회 및 인덱스 리빌드를 확인 및 진행해주면 원활하게 운영되는 경우들이 있다. 그래서 오늘은 인덱스 리빌드를 확인하고자 한다.
단편화 ( 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
주기적으로 스케줄러를 걸어서 새벽에 인덱스 리빌드를 처리하는데, 프로그램 속도 저하를 어느정도 개선하는 것으로 확인된다.
그래도 무엇보다 서버 하드웨어가 최고인 게 제일 좋은 것 같긴하다...
'데이터베이스[DB] > MSSQL' 카테고리의 다른 글
[MS-SQL] 테이블별 용량확인 (0) | 2025.01.18 |
---|---|
[MS-SQL] TOP, WITH TIES, ROW_NUMBER (0) | 2025.01.17 |
[MS-SQL] IIF, CASE ( DECODE, CASE ) 조건문 (0) | 2025.01.15 |
[MS-SQL] 테이블생성, 컬럼생성, 컬럼수정, 컬럼삭제, 테이블명변경 ( Create table, Alter table, Modify, Rename ) (0) | 2025.01.14 |
[MS-SQL] 테이블-컬럼 정보 조회 (0) | 2025.01.01 |
[MS-SQL] FOR JSON PATH - Json형식변 (0) | 2024.10.22 |
[MS-SQL] NEWID() - 랜덤함수 (0) | 2024.06.25 |
[MSSQL] REPLACE - 문자열 치환 함수 (0) | 2024.06.24 |