SQL Server パーティション構成したDBで断片化の状態によってインデックスを再構成または再構築する方法（再構築はオンラインで実施）

既存の断片化対応から改善

SQL ServerをEnterprise Editionに変えたのでオンラインオプションをつけた。
パーティション構成にすると指定オプションが増える。

作業

• 「メンタナンス プラン」でスケジュール構成
  • T-SQLステートメントの実行タスク

USE target-db
EXEC up_UpdateIndex

• up_UpdateIndex

ALTER PROCEDURE [dbo].[up_UpdateIndex]
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @cnt int, @i int, @sql nvarchar(2000), @isOnline tinyint, @tar varchar(2000)
    DECLARE @SchemaName nvarchar(128), @ObjectName nvarchar(128), @IndexName nvarchar(128)
        , @avg_fragmentation_in_percent float

    --パーティション構成テーブルはパーティション番号を指定するため
    SET @tar = '[table_1],[table_2],[table_3],......'

    DECLARE @tbl table(
        ID int identity(1,1)
        , SchemaName nvarchar(128)
        , ObjectName nvarchar(128)
        , IndexName nvarchar(128)
        , avg_fragmentation_in_percent float
    );

    INSERT INTO @tbl(SchemaName, ObjectName, IndexName, avg_fragmentation_in_percent)
    SELECT QUOTENAME(S.name) AS SchemaName, QUOTENAME(O.name) AS ObjectName,
        QUOTENAME(I.name) AS IndexName, PS.avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') AS PS
        INNER JOIN sys.objects AS O ON PS.object_id = O.object_id
        INNER JOIN sys.schemas AS S ON O.schema_id = S.schema_id
        INNER JOIN sys.indexes AS I ON PS.object_id = I.object_id AND PS.index_id =  I.index_id
    WHERE avg_fragmentation_in_percent >= 1.0 AND PS.index_id > 0;

    SELECT @cnt = Count(*) FROM @tbl;

    SET @i = 1;

    WHILE @i <= @cnt
    BEGIN
        SET @isOnline = 0

        SELECT @SchemaName = SchemaName, @ObjectName = ObjectName
            , @IndexName = IndexName, @avg_fragmentation_in_percent =  avg_fragmentation_in_percent
        FROM @tbl
        WHERE ID = @i;

        IF @avg_fragmentation_in_percent < 30.0
        BEGIN
            SET @sql = N'ALTER INDEX ' + @IndexName + N' ON ' + @SchemaName + N'.' +  @ObjectName + N' REORGANIZE';
        END
        ELSE
        BEGIN
            --断片化が30%以上だと再構築をオンラインで実施
            SET @sql = N'ALTER INDEX ' + @IndexName + N' ON ' + @SchemaName + N'.' +  @ObjectName + N' REBUILD';

            set @isOnline = 1
        END

        --この部分はパーティション構成ではなければ無視
        IF (SELECT CHARINDEX(@ObjectName, @tar)) > 0
        BEGIN
            SET @Sql += N' PARTITION = 1'
        END

        IF @isOnline = 1
        BEGIN
            SET @Sql += N' WITH(ONLINE = ON)'
        END

        EXEC (@Sql);
        SET @i = @i + 1;
    END
END

参考