欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2023-08-31 00:03:16
本文参考 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/rel ......

本文参考

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')

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

--然后做更新操作后,继续查看页存储情况。

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

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

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

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

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

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

 

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