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

发布时间：2024-12-27 17:58

如果可能，尽量在原地处理，避免移动碎片引发危险 #生活常识# #家庭维修技巧# #玻璃打碎处理#

内部碎片：

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

ALTER INDEX INDEX_NAME ON TABLE_NAME REBUILD WITH(ONLINE=ON)

ALTER INDEX INDEX_NAME ON TABLE_NAME REORGANIZE

CREATE CLUSTERED INDEX INDEX_NAME ON TABLE_NAME(COLUMN1) WITH(DROP_EXISTING=ON)

DBCC SHOWCONTIG

[ (

{ table_name | table_id | view_name | view_id }

[ , index_name | index_id ]

) ]

[ WITH

{

[ , [ ALL_INDEXES ] ]

[ , [ TABLERESULTS ] ]

[ , [ FAST ] ]

[ , [ ALL_LEVELS ] ]

[ NO_INFOMSGS ]

}

]

要检查碎片信息的表或视图。如果未指定，则检查当前数据库中的所有表和索引视图。若要获得表或视图 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

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

asbegin

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)

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

endset nocount off--2、CREATE procedure [dbo].[pr_auto_indexdefrag_online]

asbegin

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_curendset nocount off

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('test','t_goods','idx_baseGoodsID_packetSize')

网址：索引碎片判断及整理、自动维护清理索引碎片 https://www.yuejiaxmz.com/news/view/585880

上一篇: CHIEF 车仆燃油宝清洗积碳

下一篇: 10日全国空气质量最差10城市浙

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

相关内容

随便看看

最新动态分享

热点动态分享

专题

推荐动态分享