SQL Server 整理索引

索引page碎片这个无法避免，碎片过多，会带来一些性能问题，一般建议碎片率大于30%最好重建索引，小于30%进行索引重组。

重建重组索引脚本：

declare r_table cursor

for select name from sys.tables  

declare @t varchar(50)
open r_table
fetch next from r_table into @t
while @@fetch_status=0
begin
declare r_Index cursor
for select t.name,i.name,s.avg_fragmentation_in_percent from sys.tables t
join sys.indexes i on i.object_id=t.object_id
join sys.dm_db_index_physical_stats(db_id(),object_id(@T),null,null,'limited') s

on s.object_id=i.object_id and s.index_id=i.index_id  

declare @TName varchar(50),@IName varchar(50),@avg int,@str varchar(500)
open r_index
fetch next from r_index into @TName,@Iname,@avg
while @@fetch_status=0
begin
if @avg>=30 --大于30,重建索引
begin

set @str='alter index '+rtrim(@Iname)+' on dbo.'+quotename(rtrim(@tname))+' rebuild'  

end
else --小于30,重组索引
begin

set @str='alter index '+rtrim(@Iname)+' on dbo.'+quotename(rtrim(@tname))+' reorganize'  

end
exec (@str)
fetch next from r_index into @TName,@Iname,@avg
end
close r_index
deallocate r_index
fetch next from r_table into @t
end
close r_table
deallocate r_table
set nocount off