SQLSERVER索引碎片整理脚本代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】 SQLSERVER索引碎片整理脚本

作者:Dezai.CN / 发布于2011/7/11/ 694

<div>--------------------------------------------------------------------------------
-- ensure a use databse statement has been executed first.
--------------------------------------------------------------------------------
use DBName
set nocount on
declare @objectid int
,@indexid int
,@partitioncount bigint
,@schemaname sysname
,@objectname sysname
,@indexname sysname
,@partitionnum bigint
,@partitions bigint
,@frag float
,@command varchar(1000)
,@db_id int
set @db_id = db_id()
print @db_id
select objectid = object_id
,indexid = index_id
,partitionnum = partition_number
,frag = avg_fragmentation_in_percent
into #work_to_do
from sys.dm_db_index_physical_stats(@db_id,null,null,null,'LIMITED')
where avg_fragmentation_in_percent > 5.0 and index_id > 0
-- declare the cursor for the list of partitions to be processed.
declare partitions cursor for
select * from #work_to_do
-- Open the cursor.
open partitions
-- Loop through the partitions.
fetch next from partitions into @objectid, @indexid, @partitionnum, @frag
while @@fetch_status = 0 begin
select @objectname = o.name, @schemaname = s.name
from sys.objects as o 
inner join sys.schemas as s
on s.schema_id = o.schema_id
where o.object_id = @objectid
select @indexname = name
from sys.indexes
where object_id = @objectid and index_id = @indexid
select @partitioncount = count (*)
from sys.partitions
where object_id = @objectid and index_id = @indexid 
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding
if @frag < 30.0 begin
select @command = 'alter index [' + @indexname + '] on ' 
+ @schemaname + '.' + @objectname + ' reorganize'
if @partitioncount > 1
select @command = @command + ' partition=' + convert(char, @partitionnum)
end
if @frag >= 30.0 begin
select @command = 'alter index [' + @indexname +'] on ' 
+ @schemaname + '.' + @objectname + ' rebuild'
if @partitioncount > 1
select @command = @command + ' partition=' + convert(char, @partitionnum)
end
exec (@command)
print 'Executed: ' + @command
fetch next from partitions into @objectid, @indexid, @partitionnum, @frag
end
-- free resource
close partitions
deallocate partitions
drop table #work_to_do</div>

评论列表

本站所提供的代码，版权归原作者所有，若有侵犯作者版权，请与我们联系，我们将立即删除或修改。谢谢!
本站所有代码发布及提供者。

试试其它关键字

　索引碎片整理　

同语言下

可能有用的

Dezai.CN贡献的其它代码(4037)