SQLSERVER索引碎片整理脚本

 

-- ------------------------------------------------------------------------------
-- 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

 

转载于:https://www.cnblogs.com/msyye/archive/2010/11/11/1874997.html