[MSSQL] Index Rebuild 스크립트

MSSQL을 사용하다 보면 잦은 데이터 수정/삭제로 인하여 Index fragmentation이 발생하여 성능이 저하될 수 있다. 이때는 Index Rebuild 작업을 통해 성능을 개선할 수 있다.

다음은 Index Rebuild를 위한 Procedure 이다.

USE [Database name]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SP_INDEX_MAINTENANCE_ENTERPRISE]

AS

DECLARE @RowThreshold INT
DECLARE @PercentThreshold DECIMAL(38,10)

/***********************************************************************************************************************************
*************SET PARAMETERS*********************************************************************************************************/

SET @RowThreshold      = 1000   --Threshold for the number of rows.  In the number of rows in the index is less that the threshold 
                        --then it will skip it completely.
SET @PercentThreshold   = 50   --FOR THIS SCRIPT 50 = 50%
                        --Fragmentation threshold percentage.  If greater than or equal to this amount them REBUILD the Index
                        --If less than this amount REORGANIZE the index

/************SET PARAMETERS*********************************************************************************************************
************************************************************************************************************************************/

SET NOCOUNT ON

--INDEXES THAT CAN BE REBUILT ONLINE

DECLARE @tablename SYSNAME;
DECLARE @index_name SYSNAME;
DECLARE @percentfrag DECIMAL(38,10);
DECLARE @command VARCHAR(8000);
DECLARE @command2 VARCHAR(8000);
DECLARE Index_Maint CURSOR FOR

SELECT    o.name, i.name, f.avg_fragmentation_in_percent
FROM    SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(), NULL, NULL , NULL, 'LIMITED') f
JOIN sys.objects o on o.object_id = f.object_id
JOIN sys.indexes i on i.object_id = f.object_id and i.index_id = f.index_depth 
WHERE    f.index_id > 0
AND NOT EXISTS ( --eliminate indexes that can not be rebuilt ONLINE
    SELECT    1 
    FROM    sys.index_columns AS ic
    INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id 
            AND ((c.system_type_id IN (34,35,99,241)) -- image, text, ntext, xml
                OR (c.system_type_id IN (167,231,165) -- varchar, nvarchar, varbinary
            AND max_length = -1))
    WHERE    ic.object_id = I.object_id
    AND        ic.index_id = I.index_id
    )

OPEN Index_Maint

FETCH NEXT FROM Index_Maint INTO 
     @tablename, @index_name, @percentfrag

While @@FETCH_STATUS = 0
BEGIN

   IF @percentfrag >= @PercentThreshold
      BEGIN
         SELECT @command2 = 'ALTER INDEX ' + '[' + @index_name + ']' + ' ON ' + '[' + @tablename + ']' + ' REBUILD WITH(MAXDOP=0, ONLINE=ON)'; --Take advantage of parallelism and ONLINE INDEX REBUILDS
         PRINT 'Execute SQL: ' + @command2;
         SET STATISTICS TIME ON;
         BEGIN TRY
            EXEC (@command2);
         END TRY
         BEGIN CATCH
            PRINT 'Error SQL: ' + @command2
         END CATCH
         SET STATISTICS TIME OFF; 
      END; 
   ELSE
      BEGIN
         SELECT @command = 'ALTER INDEX ' + '[' + @index_name + ']' + ' ON ' + '[' + @tablename + ']' + ' REORGANIZE';
         PRINT 'Execute SQL: ' + @command;
         SET STATISTICS TIME ON;
         BEGIN TRY
            EXEC (@command);
         END TRY
         BEGIN CATCH
            PRINT 'Error SQL: ' + @command
         END CATCH
         SET STATISTICS TIME OFF; 
      END;         

FETCH NEXT FROM Index_Maint INTO 
     @tablename, @index_name, @percentfrag
END 

CLOSE Index_Maint
DEALLOCATE Index_Maint

--INDEXES THAT CANNOT BE REBUILT ONLINE

DECLARE @tablename2 SYSNAME;
DECLARE @index_name2 SYSNAME;
DECLARE @percentfrag2 DECIMAL(38,10);
DECLARE @command3 VARCHAR(8000);
DECLARE @command2b VARCHAR(8000);
DECLARE Index_Maint2 CURSOR FOR

SELECT
o.name,
i.name,
f.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') f
JOIN sys.objects o on o.object_id = f.object_id
JOIN sys.indexes i on i.object_id = f.object_id and i.index_id = f.index_depth 
WHERE f.index_id > 0
AND EXISTS (                     --Select only indexes that CANNOT be rebuilt ONLINE
  SELECT 1 FROM sys.index_columns AS ic
  INNER JOIN sys.columns AS c
  ON ic.object_id = c.object_id
  AND ic.column_id = c.column_id
  AND ((c.system_type_id IN (34,35,99,241)) -- image, text, ntext, xml
   OR (c.system_type_id IN (167,231,165) -- varchar, nvarchar, varbinary
       AND max_length = -1))
  WHERE ic.object_id = I.object_id
  AND ic.index_id = I.index_id
)

OPEN Index_Maint2

FETCH NEXT FROM Index_Maint2 INTO 
     @tablename2, @index_name2, @percentfrag2

While @@FETCH_STATUS = 0
BEGIN

   IF @percentfrag2 >= @PercentThreshold
      BEGIN
         SELECT @command2b = 'ALTER INDEX ' + '[' + @index_name2 + ']' + ' ON ' + '[' + @tablename2 + ']' + ' REBUILD WITH(MAXDOP=0)'; --Take advantage of parallelism and ONLINE INDEX REBUILDS
         PRINT 'Execute SQL: ' + @command2b;
         SET STATISTICS TIME ON;
         BEGIN TRY
            EXEC (@command2b);
         END TRY
         BEGIN CATCH
            PRINT 'Error SQL: ' + @command2b
         END CATCH
         SET STATISTICS TIME OFF; 
      END; 
   ELSE
      BEGIN
         SELECT @command3 = 'ALTER INDEX ' + '[' + @index_name2 + ']' + ' ON ' + '[' + @tablename2 + ']' + ' REORGANIZE';
         PRINT 'Execute SQL: ' + @command3;
         SET STATISTICS TIME ON;
         BEGIN TRY
            EXEC (@command3);
         END TRY
         BEGIN CATCH
            PRINT 'Error SQL: ' +  @command3
         END CATCH
         SET STATISTICS TIME OFF; 
      END;         

FETCH NEXT FROM Index_Maint2 INTO 
     @tablename2, @index_name2, @percentfrag2
END 

CLOSE Index_Maint2
DEALLOCATE Index_Maint2

생성된 Procedure는 다음의 스크립트를 통해 수행한다.

SET STATISTICS TIME OFF; 

USE [Database name]
GO

-- Set parallelism to 0
EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE WITH OVERRIDE;  
GO  
EXEC sp_configure 'max degree of parallelism', 0;  
GO  
RECONFIGURE WITH OVERRIDE;  
GO

-- Run Index rebuild
DECLARE   @return_value int
EXEC      @return_value = [dbo].[SP_INDEX_MAINTENANCE_ENTERPRISE];
SELECT   'Return Value' = @return_value
GO

-- Set parallelism to 1
EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE WITH OVERRIDE;  
GO  
EXEC sp_configure 'max degree of parallelism', 1;  
GO  
RECONFIGURE WITH OVERRIDE;  
GO