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

本文参考

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

本文需要对“索引”和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

 

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