索引碎片自动整理存储过程

背景：

下面是2014年3月15日的SQL PASS上大神提供的重建索引GuideLine：
When To Rebuild vs. Defrag
•< 10% do nothing
•10% <> 30% defrag/reorganize
•30%+ rebuild
•And don’t do anything if the index has < 1000 pages

----------------------------------------------------------------------

根据此GuideLine，本人制作了自动重建索引的存储过程分享给大家：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

-- =============================================
-- Author:        <AjianGG>
-- Create date:    <2014/03/18>
-- Description:    <Removing Fragmentation>
-- =============================================
CREATE PROCEDURE [dbo].[proc_rebuild_index]
    @ret    INT OUTPUT
AS
SET NOCOUNT ON

BEGIN
    DECLARE @fldDefragFragment INT = 10;
    DECLARE @fldRebuildFragment INT = 30;
    DECLARE @fldMinPageCount INT = 1000;
    DECLARE @fldTable VARCHAR(256);
    DECLARE @fldIndex VARCHAR(256);
    DECLARE @fldPercent INT;
    DECLARE @Sql       VARCHAR(256);

    BEGIN TRY

        SET @ret = -1;
        
        -- 获取索引碎片状况
        DECLARE curIndex CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR
            SELECT 
                 TBL.NAME TABLE_NAME
                ,IDX.NAME INDEX_NAME
                ,AVGP.AVG_FRAGMENTATION_IN_PERCENT
            FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(), NULL,NULL, NULL, 'LIMITED') AS AVGP 
            INNER JOIN SYS.INDEXES AS IDX 
             ON AVGP.OBJECT_ID = IDX.OBJECT_ID 
            AND AVGP.INDEX_ID = IDX.INDEX_ID 
            INNER JOIN SYS.TABLES AS TBL 
             ON AVGP.OBJECT_ID = TBL.OBJECT_ID
            INNER JOIN SYS.DM_DB_PARTITION_STATS PS
             ON AVGP.OBJECT_ID = PS.OBJECT_ID
            AND AVGP.INDEX_ID = PS.INDEX_ID 
            WHERE
                AVGP.INDEX_ID >= 1 
            AND AVGP.AVG_FRAGMENTATION_IN_PERCENT >= @fldDefragFragment
            AND PS.RESERVED_PAGE_COUNT >= @fldMinPageCount;

        -- 打开游标
        OPEN curIndex;

        -- 获取游标
        FETCH NEXT FROM curIndex
        INTO @fldTable,@fldIndex,@fldPercent;

        WHILE @@FETCH_STATUS = 0
            BEGIN
                
                --碎片率大于30，重建索引
                IF @fldPercent >= @fldRebuildFragment
                    BEGIN
                        SET @Sql = 'ALTER INDEX ' + @fldIndex + ' ON ' + @fldTable + ' REBUILD';
                        EXEC(@Sql);
                    END
                ELSE
                --碎片率小于30，重组索引
                    BEGIN
                        SET @Sql = 'ALTER INDEX ' + @fldIndex + ' ON ' + @fldTable + ' REORGANIZE';
                        EXEC(@Sql);
                    END

                -- 获取游标
                FETCH NEXT FROM curIndex
                INTO @fldTable,@fldIndex,@fldPercent;
                
            END
            
        -- 关闭游标
        CLOSE curIndex;
        DEALLOCATE curIndex;
            
        SET @ret = 0;

    END TRY
    BEGIN CATCH

        SET @ret = -1;

        DECLARE @ErrorMessage    nvarchar(4000);
        DECLARE @ErrorSeverity    int;
        DECLARE @ErrorState        int;

        SELECT
              @ErrorMessage = ERROR_MESSAGE()
            , @ErrorSeverity  = ERROR_SEVERITY()
            , @ErrorState = ERROR_STATE();

        RAISERROR( @ErrorMessage
                            , @ErrorSeverity
                            , @ErrorState);
        RETURN;

    END CATCH;
END