SQL索引碎片的产生，处理过程。

本文参考

https://www.cnblogs.com/CareySon/archive/2011/12/22/2297568.html

https://www.jb51.net/softjc/126055.html

https://docs.microsoft.com/zh-cn/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql?view=sql-server-ver15

本文需要对“索引”和MSSQL中数据的“存储方式”有一定了解。

软件经常在使用一段时间过后会无缘无故卡顿，这是因为在数据库(MSSQL)频繁的插入和更新的操作过程中会产生分页，在分页的过程中产生碎片导致的。所以，对于碎片需要定时的处理。基本上所有的办法都是基于对索引的重建和整理，只是方式不同。

1. 删除索引并重建
2. 使用DROP_EXISTING语句重建索引
3. 使用ALTER INDEX REBUILD语句重建索引
4. 使用ALTER INDEX REORGANIZE

以上方式各有优缺点，下面存储过程主要使用3，4

先看一个整理碎片的存储过程,然后采用作业的方式定时执行。

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

下面直观的看一下碎片产生的过程

--创建测试表
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

--再次插入数据后查看页存储情况
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

 

 明显的逻辑读取减少了。从而提高了性能