MSSQL - Index Rebuild (인덱스 리빌드) 쿼리

인덱스 리빌딩은 데이터베이스에 많은 데이터가 오랜기간동안 쌓였을 경우 쿼리를 실행하면 속도가 늦어지는 경우가 발생합니다.

실행계획은 분석하여 인텍스를 정확하게 수행이 되더라도 속도 개선이 안될 경우 인덱스를 리빌드하여 다시 수행하면 속도 개선의 효과를 볼 수도 있습니다.

즉, 하드디스크의 조각 모음을 수행하여 컴퓨터 속도를 향상시키는 것처럼 하나의 인덱스의 데이터가 분산된 것을 디스크상에 뭉쳐주는 역할을 하여 속도 개선이 이루어진다.

 

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