文章摘要
这篇文章描述了一个用于数据库优化的T-SQL存储过程`sp_rebuild_index`，其主要目的是自动检测并处理索引的逻辑碎片问题。该过程通过以下步骤操作：
1. **设置参数**：接受一个整数`Rebuild_Fragmentation_Percent`，默认值为5%，表示当索引的逻辑碎片百分比超过该值时，触发索引重建操作。
2. **检查对象类型**：仅对`master`、`model`、`msdb`和`tempdb`等系统数据库进行操作，避免对用户数据或临时数据表进行优化。
3. **判断逻辑碎片**：通过检查数据库的物理统计信息，判断是否存在逻辑碎片。如果逻辑碎片百分比不超过阈值，则不会执行索引重建操作。
4. **打印优化信息**：在操作开始前，打印相关优化信息，包括数据库名称和优化命令。
5. **批量处理索引重建**：使用`Cursors`进行批量操作，逐个重建满足条件的索引。每次重建后，打印相关操作日志。
6. **执行操作**：通过`exec`关键字执行修改索引的T-SQL语句。
该过程旨在优化数据库性能，提高查询效率，适用于需要自动化的索引维护和逻辑碎片检测的场景。
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[sp_rebuild_index]
(
@Rebuild_Fragmentation_Percent smallint=5 — 当逻辑碎片百分比 > 5% 重新生成索引
)
as
begin
–对系统数据库不作重新组织索引和重新生成索引
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,null,null) a where a.index_id>0 and a.avg_fragmentation_in_percent > @Rebuild_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))+’ rebuild;’ as [sql]
,N’重新生成索引：’ +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,null,null,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 > @Rebuild_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
print @sql
exec(@sql)
print @str
fetch next from cur_x into @sql,@str
end
close cur_x
deallocate cur_x
end