Friday, January 2, 2009

Automatic index rebuild and reorganizing

----------------------------------------------------
-- Automatic index rebuild and reorganizing
--
-- Reorganizes indexes with fragmentation between 10 and 30%
-- Rebuilds indexes with more than 30% fragmentation
--
-- Written by perb@episerver
----------------------------------------------------

declare @sql nvarchar(1000)
declare @indexname nvarchar(255)
declare @tablename nvarchar(255)
declare @fragmentation float
declare @msg nvarchar(512)
declare @dbid int
declare @indexCount int

set nocount on

set @dbid=db_id()
set @indexCount = 0

declare c CURSOR FOR
Select 'ALTER INDEX ' + i.name + ' ON ' + object_name(d.object_id) + CASE WHEN avg_fragmentation_in_percent>30 THEN ' REBUILD' ELSE ' REORGANIZE' END as [sql],
convert(decimal(5,2), avg_fragmentation_in_percent) fragmentation,object_name(d.object_id),i.name
from sys.dm_db_index_physical_stats( @dbid,null, -1, null, 'SAMPLED') d -- or 'DETAILED'
inner join sys.indexes i on i.object_id=d.object_id and i.index_id=d.index_id
where d.avg_fragmentation_in_percent > 10
order by avg_fragmentation_in_percent desc

select N'See "Messages" tab for progress!' as Info
raiserror (N'Reading index fragmentation..',0,1) WITH NOWAIT
raiserror (N' ',0,1) WITH NOWAIT

open c

fetch next from c INTO @sql,@fragmentation,@tablename,@indexname

while @@FETCH_STATUS = 0
begin
SET @msg = N'Found fragmented index..'
raiserror (@msg,0,1) WITH NOWAIT
SET @msg = N' Name: ' + @indexname
raiserror (@msg,0,1) WITH NOWAIT
SET @msg = N' Table: ' + @tablename
raiserror (@msg,0,1) WITH NOWAIT
SET @msg = N' Fragmentation: ' + cast(@fragmentation as nvarchar) + '%s'
raiserror (@msg,0,1,'%') WITH NOWAIT

exec sp_executesql @sql
set @indexCount = @indexCount + 1

SET @msg = N' Defrag done!'
raiserror (@msg,0,1) WITH NOWAIT
SET @msg = N' '
raiserror (@msg,0,1) WITH NOWAIT

fetch next from c INTO @sql,@fragmentation,@tablename,@indexname
end

close c
deallocate c

SET @msg = N'--------------------------------'
raiserror (@msg,0,1) WITH NOWAIT
SET @msg = N'Found and defragged ' + cast(@indexCount as nvarchar) + N' index(es)'
raiserror (@msg,0,1) WITH NOWAIT

No comments:

Post a Comment