MSSQL 인덱스 리빌드

데이터베이스가 오래되면, 인덱스에도 조각화(Fragmentation)가 되고,
어떨때는 쿼리플랜으로 보면, 인덱스가 선택되지 않는 경우도 있고,
이래저래 데이터베이스가 좀 느려졌다 싶을때,

 

가끔 인덱스를 REBUILD 해주면, 속도가 빨라지곤한다.
데이터 양에 따라 시간이 많이 걸리기 때문에,
사용자가 없는 새벽이나 유휴시간에 작업해야 한다.

 

데이터베이스 안에 많은 양의 테이블이 있기 때문에 일일이, 테이블을 지정해서 해주면,
노가다가 되기 때문에 자동화해서, 한번의 실행으로 모든 테이블의 인덱스를 REBUILD 해주는 방법이다.

 

1.
데이터베이스 안에 모든 테이블 목록을 가져오는 쿼리다.

SELECT '[' + table_catalog + '].[' + table_schema + '].[' + table_name + ']' as tableName
FROM DB명.INFORMATION_SCHEMA.TABLES
WHERE table_type = 'BASE TABLE' AND table_name not like 'sys%'

 

2.
이를 커서를 이용해서, 모든 테이블에 대해 아래의 인덱스 리빌드 쿼리를 실행할 것이다.

ALTER INDEX ALL ON [DB명].[dbo].[TABLE명] REBUILD WITH ( fillfactor = 90)

 

 

 

아래는 전체 풀 소스 이며, 커서를 통해, 모든 테이블을 처리하며,
동적쿼리문을 작성해서 실행한다.

 

DECLARE @db_nm  varchar(500) -- DB명
DECLARE @table_nm varchar(1000) -- 테이블명
DECLARE @cmd  nvarchar(4000) -- 동적쿼리
DECLARE @FillFactor int  -- 인덱스 채우기비율

SET @FillFactor = 90   -- 인덱스의 90%를 채운다.

SET @db_nm = 'nv_blog'

-- DB에서 테이블목록을 불러서 커서에 저장 (sys로 시작하는 테이블은 제외)
-- 테이블명 구조 : [DB명].[dbo].[TABLE명]
SET @cmd = 'DECLARE curTB cursor for SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + table_name + '']'' as tableName
FROM ' + @db_nm + '.INFORMATION_SCHEMA.TABLES
WHERE table_type = ''BASE TABLE'' AND table_name not like ''sys%''';

EXEC (@cmd)

-- TABLE 커서 오픈
OPEN curTB

FETCH NEXT FROM curTB INTO @table_nm
WHILE @@fetch_status = 0
begin
 -- 테이블 별 인덱스를 리빌드함
 SET @cmd = 'ALTER INDEX ALL ON ' + @table_nm + ' REBUILD WITH ( fillfactor = ' + convert(varchar(3), @FillFactor ) + ')';

 print 'Work - ' + @cmd

 EXEC (@cmd)

 print 'Done - ' + @cmd

 FETCH NEXT FROM curTB INTO @table_nm
end

-- TABLE 커서 종료
CLOSE curTB
DEALLOCATE curTB

 

 

 

 

 

PS. 이 쿼리는 MS-SQL 2012 이후 버전에서 실행되었습니다.