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

背景：

下面是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 ONGOSET QUOTED_IDENTIFIER OFFGO-- =============================================-- Author:        <AjianGG>-- Create date:    <2014/03/18>-- Description:    <Removing Fragmentation>-- =============================================CREATE PROCEDURE [dbo].[proc_rebuild_index]    @ret    INT OUTPUTASSET NOCOUNT ONBEGIN    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