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

 

内部碎片：
 指 当索引页没有用到最大量时就产生了内部碎片。虽然在一个有频繁数据插入的应用程序里这也许有帮助，然而设置一个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')