SQL索引碎片的产生，处理过程。「建议收藏」

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);
    declare @DBID  int;
    BEGIN TRY
        SET @ret = -1;
        set @DBID = db_id();
        -- 获取索引碎片状况
        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(@DBID, 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

代码100分

代码100分--创建测试表
if object_id("test") is not null 
  drop table test
go
create table test
(
  col1 int, 
  col2 char(985),
  col3 varchar(10)
)
Go
--创建聚焦索引
create CLUSTERED index cix on test(col1);
go
--插入数据
declare @var int 
set @var=100
while (@var<900) 
begin
  insert into test(col1, col2, col3) 
  values (@var, "xxx", "")
  set @var=@var+100
end;
--查看页存储情况
select page_count, avg_page_space_used_in_percent, record_count,
       avg_record_size_in_bytes, avg_fragmentation_in_percent, fragment_count,
       * from [master].sys.dm_db_index_physical_stats(db_id(), OBJECT_ID("test"), null, null, "sampled")

update test set col3="更新测试" where col1=100

代码100分--再次插入数据后查看页存储情况
declare @var int 
set @var=100
while (@var<900) 
begin
  insert into test(col1, col2, col3) 
  values (@var, "插入测试", "")
  set @var=@var+100
end;

--下面看下对碎片整理之前和之后的IO
set statistics io on 
select * from test
alter index cix on test rebuild
select * from test 
set statistics io off