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