본문 바로가기
IT/MSSQL

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

by 베베야 2021. 3. 11.
728x90

인덱스 단편화 확인과 리빌딩 글에 대한 썸네일 이미지 입니다.
인덱스 리빌딩 썸네일

데이터베이스를 많이 사용하다 보면 테이블 단편화 현상이 발생합니다. 
이 현상은 데이터를 읽고 쓰고 하면서 발생하는 당연한 현상으로 인덱스 리빌드를 진행해 주면 되는데요.
오늘은 MSSQL에서 단편화 현상이 많은 테이블을 찾고 해당 테이블에서 단편화 현상이 심한 인덱스를 찾은 다음
단편화 현상을 제거하여 속도를 높여주는 인덱스 리빌드를 까지진행해 보도록 하겠습니다.

 

 

단편화 테이블 조회하기

1. 인덱스 단편화 현상이 발생한 테이블 TOP 10 조회하기
아래의 쿼리문을 사용해서 단편화가 가장 심한 테이블 TOP 10을 뽑아보도록 하겠습니다.
Fragmentation 필드의 퍼센트가 높을수록 단편화가 심한 테이블입니다.

/* 1.인덱스 단편화 테이블 top 10*/ 
use [데이터베이스 이름]
SELECT TOP 10
            DB_NAME() AS 'DatabaseName'
          , OBJECT_NAME(S.OBJECT_ID) AS 'TableName'
          , I.NAME AS 'IndexName'
          , ROUND(AVG_FRAGMENTATION_IN_PERCENT, 2) AS 'Fragmentation %'
 FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(),NULL,NULL,NULL,NULL) AS S
 INNER JOIN SYS.INDEXES AS I
 ON S.OBJECT_ID = I.OBJECT_ID AND S.INDEX_ID = I.INDEX_ID
 WHERE S.DATABASE_ID = DB_ID('데이터베이스 이름') -- 현재 데이터베이스**
 AND I.NAME IS NOT NULL -- HEAP은 무시
 AND OBJECTPROPERTY(S.OBJECT_ID, 'IsMsShipped') = 0 -- 시스템 개체 무시
 ORDER BY [Fragmentation %] DESC

 

2. 인덱스 단편화 현상이 발생한 테이블 자세히 조회하기
단편화를 조회하는 방법에는 아래의 2가지 방법이 있습니다.
원하는 방법을 선택해서 조회를 해보시기 바랍니다.

--인덱스 단편화 조회 #1
DBCC SHOWCONTIG ('테이블명') WITH TABLERESULTS --테이블명
--Scan Density(logical extent density) 와 Avg Page Density(Avg free bytes/page) 
  확인하여 30~50% under이면 Reorg가 필요 

--인덱스 단편화 조회 #2
SELECT  
        DB_NAME(database_id) AS [Database Name], 
        OBJECT_NAME(a.object_id) AS [Table Name], 
        a.index_id, b.name AS [Index Name], a.avg_fragmentation_in_percent
 
FROM sys.dm_db_index_physical_stats (DB_ID('디비명'), OBJECT_ID('테이블명'), NULL, NULL, NULL) AS a
         JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id

WHERE a.database_id = DB_ID('디비명')
-----------------------------------------쿼리 결과---------------------------------------------
--(avg_fragmentation_in_percent 값이 낮을수록 좋음습니다.)
Database Name  Table Name	index_id   Index Name avg_fragmentation_in_percent
디비명			TEST	     1	     	PK_TEST	   99.3562118982552

 

3. 인덱스 용량 체크하기
테이블에 있는 인덱스들의 용량을 확인하는 방법입니다.
리빌딩 실행 전과 후의 용량 비교를 위해 조회하여 메모해두면 나중에 비교가 쉽습니다.

/*인덱스 용량 확인*/
sp_MSindexspace  테이블명
--select * from sys.tables
-------------------------------결과---------------------------------
Index ID	Index Name		Size(KB)	Comments
1			PK_TEST			3616		Size excludes actual data.

 

 

인덱스 리빌딩 실행하기

인덱스 리빌딩(조각모음) 하는 방법에는 2가지 방법이 있습니다.
효율은 첫 번째 방법보다 2번째 방법이 더 좋습니다.

--DB 테이블 리스트 : select * from information_schema.tables
--아래 2개의 방법은 락걸린 테이블은 건너뛰기 때문이 업무시가능하지만 급한상황이 아니면 업무 끝나고작업 하는것을 추천합니다.
--완전하게 하기위해서는 비 업무시간 디비접속 막고 인덱스 재생성을 실행 :(dbcc dbreindex(TABLE명, '', 100)

--인덱스 조각모음 실행 방법 #1
1.번 방법 : dbcc indexdefrag('DB명', 'dbo.TEST','PK_TEST') --dbcc indexdefrag(db이름, "소유자.테이블이름",인덱스이름)
-------------------------------------------
Pages Scanned	Pages Moved	Pages Removed
37441			28045		10005

--인덱스 조각모음 실행 방법 #2
alter index all on TEST rebuild with (PAD_INDEX=on, FILLFACTOR=90) -- 효율은 이게 더 좋음

 

2. 통계 업데이트 확인
정상적으로 인덱스 리빌딩 작업을 마치고 나면 통계 업데이트가 정상적으로 업데이트되어서 반영되었는지 확인해주세요.

--인덱스 리빌드 후 통계업데이트 확인
DBCC SHOW_STATISTICS (테이블명,인덱스명) --DBCC SHOW_STATISTICS ( 테이블_이름 , 인덱스_이름 ) 통계 업데이가 정상적으로 되었는지 Updated 확인
---------------------------------------------------
Name	Updated				Rows		Rows	Sampled
PK_TEST	03 10 2021  5:03PM	1816160		1816160

 


3. DBCC 명령어를 이용한 조회
DBCC 명령어를 이용해서 아래와 같이 간편하게 조회도 가능합니다.

DBCC SHOWCONTIG (테이블명)  -- DBCC SHOWCONTIG (테이블_이름) 단편화률 확인
------------------------------------------------------------------------------
DBCC SHOWCONTIG이(가) '테이블명' 테이블을 검색하는 중...
테이블: '테이블명'(2069582411); 인덱스 ID: 1, 데이터베이스 ID: 156
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 28378
- 검색한 익스텐트 ..............................: 3548
- 익스텐트 스위치..............................: 3547
- 익스텐트당 평균 페이지 수........................: 8.0
- 검색 밀도[최적:실제].......: 100.00% [3548:3548]
- 논리 검색 조각화 상태 ..................: 0.00%
- 익스텐트 검색 조각화 상태 ...................: 30.72%
- 페이지당 사용 가능한 평균 바이트 수.....................: 736.1
- 평균 페이지 밀도(전체).....................: 90.91%
DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.

DBCC SHOWCONTIG (테이블명,인덱스명) -- DBCC SHOWCONTIG ( 테이블_이름 , 인덱스_이름 ) 단편화률 확인
------------------------------------------------------------------------------------------------------------
DBCC SHOWCONTIG이(가) '테이블명' 테이블을 검색하는 중...
테이블: '테이블명'(2069582411); 인덱스 ID: 1, 데이터베이스 ID: 156
TABLE 수준 검색을 수행했습니다.
- 검색한 페이지................................: 28378
- 검색한 익스텐트 ..............................: 3548
- 익스텐트 스위치..............................: 3547
- 익스텐트당 평균 페이지 수........................: 8.0
- 검색 밀도[최적:실제].......: 100.00% [3548:3548]
- 논리 검색 조각화 상태 ..................: 0.00%
- 익스텐트 검색 조각화 상태 ...................: 30.72%
- 페이지당 사용 가능한 평균 바이트 수.....................: 736.1
- 평균 페이지 밀도(전체).....................: 90.91%
DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.

 

 

모든 테이블 인덱스 리빌딩

디비 테이블을 하나하나 리빌딩 작업을 하려면 시간이 오라 걸리기 때문에 커서를 사용해서 한 번에 작업하는 방법입니다. (위의 과정을 따라서 진행해 보시고 이해가 끝나고 나면 진행하는 것을 추천드립니다.)

--해당 DB 전체 인덱스 리빌딩(조각모음)
-----------------------------------------------------------------------------------------------------------------------------------
USE 디비명

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

SET @i = 1

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

 

이상으로 인덱스 단편화가 일어난 테이블을 확인하는 방법과 
느려진 조회 속도를 상승시켜주는 인덱스 리빌딩 작업에 대한 포스팅을 마치겠습니다.

반응형
그리드형

댓글