索引碎片判断及整理、自动维护清理索引碎片 雨点打透心脏的1/2处 2022-07-03 14:56 200阅读 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')
还没有评论，来说两句吧...