有关重新组织和重新生成索引sp_RefreshIndex的介绍

发布时间：2023-07-27 14:25:50 所属栏目：MsSql教程来源：

导读：开始：

--------------------------------------------------------------------------------

在上周，客户反映一个系统问题，当处理大量数据的时候，出现网络超时。后来，我们跟踪测试，发现是由于索引碎片

开始：

--------------------------------------------------------------------------------

在上周，客户反映一个系统问题，当处理大量数据的时候，出现网络超时。后来，我们跟踪测试，发现是由于索引碎片多而引起的网络超时。

解决方法，自然是重新组织和重新生成索引。在这里，我写了一个存储过程sp_RefreshIndex来实现。

存储过程sp_RefreshIndex:

代码如下:

use master

go

if object_id('sp_RefreshIndex') Is not null

Drop Proc sp_RefreshIndex

Go

create proc sp_RefreshIndex

(

@Reorganize_Fragmentation_Percent smallint = 5 -- 当逻辑碎片百分比 > 5% 重新组织索引

,@Rebuild_Fragmentation_Percent smallint = 30 -- 当逻辑碎片百分比 > 30% 重新生成索引

)

as

begin

/* 调用方法：

.针对当前实例所有数据库： exec sys.sp_MSforeachdb 'use ?;exec sp_RefreshIndex'

.针对当前数据库： exec sp_RefreshIndex

*/ --对系统数据库不作重新组织索引和重新生成索引

if (db_name() in ('master','model','msdb','tempdb')) return; --如果逻辑碎片（索引中的无序页）的百分比 <= 5% ，就不作重新组织索引和重新生成索引

if not exists(select 1 from sys.dm_db_index_physical_stats(db_id(),null,null) a where a.index_id>0 and a.avg_fragmentation_in_percent > @Reorganize_Fragmentation_Percent) return

print replicate('-',60)+char(13)+char(10)+replicate(' ',14)+N'对数据库 '+quotename(db_name())+N' 进行索引优化'+replicate(' ',20)+char(13)+char(10) declare @sql nvarchar(2000),@str nvarchar(2000) declare cur_x cursor for

select 'alter index '+quotename(a.name)+' on '+quotename(object_schema_name(a.object_id))+'.'+quotename(object_name(a.object_id))+case when b.avg_fragmentation_in_percent<=@Rebuild_Fragmentation_Percent then ' reorganize;'else ' rebuild;'end as [sql]

,case when b.avg_fragmentation_in_percent<=@Rebuild_Fragmentation_Percent then N'重新组织索引：' else N'重新生成索引：'end +quotename(object_schema_name(a.object_id))+'.'+quotename(object_name(a.object_id))+'.'+quotename(a.name) as [str]

from sys.indexes a

inner join sys.dm_db_index_physical_stats(db_id(),null) b on b.object_id=a.object_id

and b.index_id=a.index_id

where a.index_id>0

and b.avg_fragmentation_in_percent > @Reorganize_Fragmentation_Percent

order by object_name(a.object_id),a.index_id open cur_x

fetch next from cur_x into @sql,@str while (@@fetch_status = 0)

begin exec(@sql)

print @str

fetch next from cur_x into @sql,@str end

close cur_x

deallocate cur_x end

go

exec sp_ms_marksystemobject 'sp_RefreshIndex'

go

调用方法：

代码如下:

use master

go

exec sys.sp_MSforeachdb 'use ?;exec sp_RefreshIndex'

go

修改@Reorganize_Fragmentation_Percent 和 @Rebuild_Fragmentation_Percent 值。

存储过程 sp_RefreshIndex 已在下面的环境测试通过：

sql Server 2005 (SP4)/2008/2008R2/2012

（编辑：聊城站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!