索引碎片判断及整理、自动维护清理索引碎片

雨点打透心脏的1/2处 2022-07-03 14:56 228阅读 0赞

内部碎片：
     指 当索引页没有用到最大量时就产生了内部碎片。虽然在一个有频繁数据插入的应用程序里这也许有帮助，然而设置一个fill factor（填充因子）会在索引页上留下空间，服务器内部碎片会导致索引尺寸增加，从而在返回需要的数据时要执行额外的读操作。这些额外的读操作会降低查询的性能
     
    外部碎片;
     指 表中的数据被修改会产生碎片。当插入或更新表中数据时，表的对应聚簇索引和受影响的聚簇索引被修改。如果对索引的修改不能容纳到同一个页面中，就可能导致索引叶子页面分割。这样就会有一个新的叶子页面被添加，该页面包含原来页面中的部分信息（通常是一半），并且维持索引键中的逻辑顺序。但是该页面通常不是与原来页面相邻的。这就产生了逻辑关键字顺序和文件中的物理顺序不一致。
     
     ---外部碎片导致磁盘上的索引页面不连续,新的叶子页面和原始叶子页面离的很远,物理顺序和逻辑顺序不同.
    
    select b.name,c.name as indexname, a.index_type_desc,a.index_depth,a.avg_fragmentation_in_percent,
      a.avg_page_space_used_in_percent,a.fragment_count ,a.avg_fragment_size_in_pages,a.page_count 
      from sys.dm_db_index_physical_stats(DB_ID(N'MYTEST'),NULL,NULL,NULL,'DETAILED') as a
      inner join sys.objects as b
      on a.object_id =b.object_id 
      inner join sys.indexes as c on a.object_id=c.object_id and a.index_id=c.index_id
    order by a.avg_fragmentation_in_percent desc,a.object_id asc
    
    
    --如果 avg_fragmentation_in_percent 的%比大于30（可以自定义%比值） 则
    ALTER INDEX INDEX_NAME ON TABLE_NAME REBUILD WITH(ONLINE=ON) ---联机不锁定表来重新创建索引，以减小索引碎片
    --如果小于30 则
    ALTER INDEX INDEX_NAME ON TABLE_NAME REORGANIZE --脱机重新创建索引，以减小索引碎片
    --避免重新创建聚簇索引时表上的非聚簇索引重建两次
    CREATE CLUSTERED INDEX INDEX_NAME ON TABLE_NAME(COLUMN1) WITH(DROP_EXISTING=ON) --该方法有可能引起阻塞和索引的消失。
    
    
    --显示指定的表或视图的数据和索引的碎片信息
    DBCC SHOWCONTIG
    
    DBCC SHOWCONTIG 
    [ ( 
        { table_name | table_id | view_name | view_id } 
        [ , index_name | index_id ] 
    ) ] 
        [ WITH 
            { 
             [ , [ ALL_INDEXES ] ] 
             [ , [ TABLERESULTS ] ] 
             [ , [ FAST ] ]
             [ , [ ALL_LEVELS ] ] 
             [ NO_INFOMSGS ]
             }
        ]
    
    --table_name | table_id | view_name | view_id
    要检查碎片信息的表或视图。如果未指定，则检查当前数据库中的所有表和索引视图。若要获得表或视图 ID，请使用 OBJECT_ID 函数。
    
    index_name | index_id
    要检查碎片信息的索引。如果未指定，则该语句将处理指定表或视图的基本索引。若要获取索引 ID，请使用 sys.indexes 目录视图。
    
    WITH
    指定有关 DBCC 语句返回的信息类型的选项。
    
    FAST
    指定是否要对索引执行快速扫描和输出最少信息。快速扫描不读取索引的叶级或数据级页。
    
    ALL_INDEXES
    显示指定表和视图的所有索引的结果，即使指定了特定索引也是如此。
    
    TABLERESULTS
    将结果显示为含附加信息的行集。
    
    ALL_LEVELS
    仅为保持向后兼容性而保留。即使指定了 ALL_LEVELS，也只对索引叶级或表数据级进行处理。
    
    NO_INFOMSGS
    取消严重级别从 0 到 10 的所有信息性消息。
    
    --分析：
    扫描页数：
     如果知道行的近似尺寸 和索引里的行数，即可估算出索引里的页数。如果扫描的页数明显大于估算的页数则存在内部碎片。
    扫描的扩展盘区数：
     扫描页数除以8（8K为一页）得到一个最高值。如果DBCC 扫描返回的数 高，则说明存在外部碎片。
    扩展盘区开关数：
     该数应该等于扫描扩展盘区数 -1 。如果高则存在外部碎片。
    每个扩展盘区上的平均页数;
     扫描页数 除以 被扫描扩展盘区数.一般为8 .如果小于 8则存在外部碎片。
    扫描密度：
     即 最佳值 ：实际值 的比率.如果这个比率值过低说明存在外部碎片.这个值最好接近100%.越低越有问题.
    逻辑扫描碎片：
     即 无序页的百分比.最好将该值控制在10%以内.高了说明有外部碎片.
    扩展盘区扫描碎片：
     即 无序扩展盘区在扫描索引叶级别页中所占的百分比.如果百分比 高说明存在外部碎片。
    平均页密度：
     每页上的平均可用字节数的百分比的相反数。低的百分比说明 有内部碎片。
    每页上的平均可用字节数：
     指所扫描的页上的平均可用字节数。越高说明存在 内部碎片。
    
    ---///
     
    
     ----将其放到作业中定时执行即可（最好在生产服务器空闲的时候执行）。仅供参考。
    
     
    
    create procedure pr_auto_indexdefrag
    as
    set nocount on
    
    begin
    
     declare @Db_name nvarchar(256)
       ,@SchemaName nvarchar(256)
       ,@TableName Nvarchar(256)
       ,@IndexName Nvarchar(512)
       ,@PctFrag decimal
       ,@Defrag nvarchar(max)
    
     
    
    if exists(select 1 from sys.objects where object_id =object_id(N'#tmp')) Drop table #tmp;
    create table #tmp(dbname nvarchar(256),tablename nvarchar(256),indexname nvarchar(256),schemaname nvarchar(256),avgfragment decimal)
    
    exec sp_MSforeachdb 'insert into #tmp(dbname,tablename,indexname,schemaname,avgfragment)
    select ''?'' dbname,c.name,b.name,e.name,a.avg_fragmentation_in_percent 
    from ?.sys.dm_db_index_physical_stats(DB_ID(''?''),NULL,NULL,NULL,''SAMPLED'') as a
        join ?.sys.indexes as b on a.object_id=b.object_id and a.index_id=b.index_id 
        join ?.sys.tables as c on a.object_id=c.object_id 
        join sys.databases as d on a.database_id=d.database_id
        join ?.sys.schemas as e on c.schema_id=e.schema_id
        where a.avg_fragmentation_in_percent >20
         and c.type=''U'' and a.page_count>8
         and d.name like ''XXXX%'''
         
     declare frg_cur cursor for
      select * from #tmp
    
        
        open frg_cur
      fetch next from  frg_cur into @Db_name,@TableName,@IndexName,@SchemaName,@PctFrag
      while @@FETCH_STATUS=0
       begin
        if @PctFrag between 20.0 and 40.0
         begin
          set @Defrag=N' ALTER INDEX '+@IndexName+' ON'+@Db_name+'.'+@SchemaName+'.'+ @TableName +' REORGANIZE'--重新组织索引页不删除索引
          EXEC SP_EXECUTESQL @Defrag
         end
         else if @PctFrag>40.0
         begin
          SET @Defrag=N' ALTER INDEX '+@IndexName+' ON'+@Db_name+'.'+@SchemaName+'.'+ @TableName +' REBUILD WITH (ONLINE = ON )'--联机重建索引。即不锁定表重新创建索引
          EXEC SP_EXECUTESQL @Defrag
         end
         fetch next from  frg_cur into @Db_name,@TableName,@IndexName,@SchemaName,@PctFrag
       end
       close frg_cur
       deallocate frg_curend
    
    end
    
    set nocount off

---由于表中含有 text、ntext、image、varchar(max)、nvarchar(max)、varbinary(max)、xml 或大型 CLR 类型的列 必须脱机处理。 --可以对pr_auto_indexdefrag 进行改造。则可以这样： CREATE   procedure [dbo].[pr_auto_indexdefrag_offline] as begin set nocount on  declare @Db_name nvarchar(256)    ,@SchemaName nvarchar(256)    ,@TableName Nvarchar(256)    ,@IndexName Nvarchar(512)    ,@PctFrag decimal    ,@Defrag nvarchar(max)     if exists(select 1 from sys.objects where object_id =object_id(N'#tmp')) Drop table #tmp;  if exists(select 1 from sys.objects where object_id =object_id(N'#tmp_sub')) Drop table #tmp_sub;   create table #tmp_sub(database_id int,dbname nvarchar(32),tablename nvarchar(128),index_type_desc nvarchar(128)) create table #tmp(database_id int,dbname nvarchar(256),tablename nvarchar(256),indexname nvarchar(256),type_desc nvarchar(128),schemaname nvarchar(256),avgfragment decimal) ------找出  text、ntext、image、varchar(max)、nvarchar(max)、varbinary(max)、xml 或大型 CLR 类型的列 exec sp_MSforeachdb 'insert into #tmp_sub(database_id,dbname,tablename,index_type_desc) select distinct c.database_id,''?'' dbname,b.name,''CLUSTERED''  from    ?.sys.dm_db_index_physical_stats(DB_ID(''?''),NULL,NULL,NULL,''SAMPLED'') as a  join ?.sys.tables as b on a.object_id=b.object_id  join sys.databases as c on a.database_id=c.database_id  join ?.sys.all_columns d on d.object_id =a.object_id  join ?.sys.sysobjects e on d.object_id=e.id and e.xtype=''U''  join ?.sys.types f on d.user_type_id=f.user_type_id where  b.type_desc=''USER_TABLE'' and b.is_ms_shipped=0 and (d.max_length =-1  OR (f.name in (''image'',''text'',''ntext'',''xml'',''varbinary'',''binary'')))'   ----找出 所有库中的索引 exec sp_MSforeachdb 'insert into #tmp(database_id,dbname,tablename,indexname,type_desc,schemaname,avgfragment) select  distinct d.database_id,''?'' dbname,c.name,b.name,b.type_desc,e.name,a.avg_fragmentation_in_percent from ?.sys.dm_db_index_physical_stats(DB_ID(''?''),NULL,NULL,NULL,''SAMPLED'') as a     join ?.sys.indexes as b on a.object_id=b.object_id and a.index_id=b.index_id     join ?.sys.tables as c on a.object_id=c.object_id     join sys.databases as d on a.database_id=d.database_id     join ?.sys.schemas as e on c.schema_id=e.schema_id     join ?.sys.sysobjects f on c.object_id=f.id     join ?.sys.all_columns g on f.id=g.object_id     join ?.sys.types h on g.user_type_id=h.user_type_id     where a.avg_fragmentation_in_percent >20      and c.type=''U'' and f.xtype=''U''      and c.is_ms_shipped=0 and d.name  like ''%XXXX%'' '           if exists (  select 1 from #tmp where  exists (select 1 from #tmp_sub b where database_id=b.database_id and tablename=b.tablename and type_desc=b.index_type_desc))          begin  declare frg_cur cursor for   select dbname,tablename,indexname,    schemaname,avgfragment     from #tmp     where  exists (select 1 from #tmp_sub b where database_id=b.database_id and tablename=b.tablename and type_desc=b.index_type_desc)       open frg_cur   fetch next from  frg_cur into @Db_name,@TableName,@IndexName,@SchemaName,@PctFrag   while @@FETCH_STATUS=0    begin     if @PctFrag between 20.0 and 40.0      begin       set @Defrag=N' ALTER INDEX '+@IndexName+' ON '+@Db_name+'.'+@SchemaName+'.'+ @TableName +' REORGANIZE'--重新组织索引页不删除索引       EXEC SP_EXECUTESQL @Defrag      end      else if @PctFrag>40.0      begin       SET @Defrag=N' ALTER INDEX '+@IndexName+' ON '+@Db_name+'.'+@SchemaName+'.'+ @TableName +' REBUILD WITH (ONLINE = OFF )'--脱机重建索引。       EXEC SP_EXECUTESQL @Defrag      end      fetch next from  frg_cur into @Db_name,@TableName,@IndexName,@SchemaName,@PctFrag    end    close frg_cur    deallocate frg_cur     end end set nocount off --2、 CREATE   procedure [dbo].[pr_auto_indexdefrag_online] as begin set nocount on  declare @Db_name nvarchar(256)    ,@SchemaName nvarchar(256)    ,@TableName Nvarchar(256)    ,@IndexName Nvarchar(512)    ,@PctFrag decimal    ,@Defrag nvarchar(max)     if exists(select 1 from sys.objects where object_id =object_id(N'#tmp')) Drop table #tmp;  if exists(select 1 from sys.objects where object_id =object_id(N'#tmp_sub')) Drop table #tmp_sub;   create table #tmp_sub(database_id int,dbname nvarchar(32),tablename nvarchar(128),index_type_desc nvarchar(128)) create table #tmp(database_id int,dbname nvarchar(256),tablename nvarchar(256),indexname nvarchar(256),type_desc nvarchar(128),schemaname nvarchar(256),avgfragment decimal) ------找出  text、ntext、image、varchar(max)、nvarchar(max)、varbinary(max)、xml 或大型 CLR 类型的列 exec sp_MSforeachdb 'insert into #tmp_sub(database_id,dbname,tablename,index_type_desc) select distinct c.database_id,''?'' dbname,b.name,''CLUSTERED''  from    ?.sys.dm_db_index_physical_stats(DB_ID(''?''),NULL,NULL,NULL,''SAMPLED'') as a  join ?.sys.tables as b on a.object_id=b.object_id  join sys.databases as c on a.database_id=c.database_id  join ?.sys.all_columns d on d.object_id =a.object_id  join ?.sys.sysobjects e on d.object_id=e.id and e.xtype=''U''  join ?.sys.types f on d.user_type_id=f.user_type_id where  b.type_desc=''USER_TABLE'' and b.is_ms_shipped=0 and (d.max_length =-1  OR (f.name in (''image'',''text'',''ntext'',''xml'',''varbinary'',''binary'')))'   ----找出 所有库中的索引 exec sp_MSforeachdb 'insert into #tmp(database_id,dbname,tablename,indexname,type_desc,schemaname,avgfragment) select  distinct d.database_id,''?'' dbname,c.name,b.name,b.type_desc,e.name,a.avg_fragmentation_in_percent from ?.sys.dm_db_index_physical_stats(DB_ID(''?''),NULL,NULL,NULL,''SAMPLED'') as a     join ?.sys.indexes as b on a.object_id=b.object_id and a.index_id=b.index_id     join ?.sys.tables as c on a.object_id=c.object_id     join sys.databases as d on a.database_id=d.database_id     join ?.sys.schemas as e on c.schema_id=e.schema_id     join ?.sys.sysobjects f on c.object_id=f.id     join ?.sys.all_columns g on f.id=g.object_id     join ?.sys.types h on g.user_type_id=h.user_type_id     where a.avg_fragmentation_in_percent >20      and c.type=''U'' and f.xtype=''U''      and c.is_ms_shipped=0 and d.name  like ''%XXXX%'' '       declare frg_cur cursor for   select dbname,tablename,indexname,    schemaname,avgfragment     from #tmp     where not exists (select 1 from #tmp_sub b where database_id=b.database_id and tablename=b.tablename and type_desc=b.index_type_desc)       open frg_cur   fetch next from  frg_cur into @Db_name,@TableName,@IndexName,@SchemaName,@PctFrag   while @@FETCH_STATUS=0    begin     if @PctFrag between 20.0 and 40.0      begin       set @Defrag=N' ALTER INDEX '+@IndexName+' ON '+@Db_name+'.'+@SchemaName+'.'+ @TableName +' REORGANIZE'--重新组织索引页不删除索引       EXEC SP_EXECUTESQL @Defrag      end      else if @PctFrag>40.0      begin       SET @Defrag=N' ALTER INDEX '+@IndexName+' ON '+@Db_name+'.'+@SchemaName+'.'+ @TableName +' REBUILD WITH (ONLINE = ON )'--联机重建索引。即不锁定表重新创建索引       EXEC SP_EXECUTESQL @Defrag      end      fetch next from  frg_cur into @Db_name,@TableName,@IndexName,@SchemaName,@PctFrag    end    close frg_cur    deallocate frg_cur end set nocount off

-------------索引使用率 select distinct db_name(database_id) as N'数据库名称',        object_name(a.object_id) as N'表名',        b.name N'索引名称',        user_seeks N'用户索引查找次数',        user_scans N'用户索引扫描次数',        last_user_seek N'最后查找时间',        last_user_scan N'最后扫描时间',        rows as N'表中的行数' from sys.dm_db_index_usage_stats a join      sys.indexes b      on a.index_id = b.index_id      and a.object_id = b.object_id      join sysindexes c      on c.id = b.object_id where database_id=db_id('DBName')   ---改成要查看的数据库 and object_name(a.object_id) not like 'sys%' order by user_seeks,user_scans,object_name(a.object_id)   -----判断是否需要更新索引统计信息 select table_Name=sysobjects.Name,     index_Name=sysindexes.Name,     Type=sysobjects.type,     分配索引页=sysindexes.reserved,     使用索引页=sysindexes.used,     叶子层页=sysindexes.Dpages,     非叶子层页=sysindexes.used-sysindexes.Dpages,     rows=sysindexes.rowcnt from sysindexes left outer join sysobjects on sysindexes.id=sysobjects.id where sysindexes.indid>0 and sysindexes.indid<255 and sysindexes.status & 64=0 --注意：若发现非叶子层的页数为负数,最好是运行DBCC UPDATEUSAGE ('dbname','tbname','ixname')来更新一下sysindexes的信息 dbcc updateusage('test','t_goods','idx_baseGoodsID_packetSize')