My script for index rebuid n reorganize

Question

Post reply

My script for index rebuid n reorganize

fawwad

SSC Eights!

Points: 965
More actions
November 22, 2010 at 8:26 am

#231436

Hi All,
I create script for index rebuild and reorganize for my dummy database. this is as follows
DECLARE @command nvarchar(4000);
declare @indexname varchar (200)
declare @objectname varchar (200)
declare @objectid int
declare @indexid int
declare @frag int
Declare indx cursor for
SELECT t.name as Table_Name, i.name as index_Name, ips.object_id AS objectid, ips.index_id AS indexid, ips.avg_fragmentation_in_percent AS frag
from sys.dm_db_index_physical_stats (15, Null,Null, NULL, 'LIMITED') ips inner join sys.tables t
on t.object_id = ips.object_id
inner join sys.indexes i on i.index_id = ips.index_id and i.object_id = t.object_id
WHERE ips.avg_fragmentation_in_percent > 10.0 AND ips.index_id > 0;
open indx
while (1 = 1)
begin
fetch next from indx
into @objectname,@indexname,@objectid,@indexid,@frag
IF @@FETCH_STATUS < 0 BREAK;
if (@frag > 40 )
begin
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + 'dbo' + N'.' + @objectname + N' REBUILD';
EXEC (@command);
PRINT N'Executed: ' + @command;
end
else
begin
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + 'dbo' + N'.' + @objectname + N' REORGANIZE';
EXEC (@command);
PRINT N'Executed: ' + @command;
end
end
close indx
deallocate indx
now, the problem here that i dont understand is this when i am rebuilding or reorganizing indexes. the fragmentation doesnt go to ZERO. it change from 50% to 33% and if i run it again it changes back to 50% !!!
i dont understand this pattern, please tell me is there anything wrong in my script or i am missing some other concept in index Rebuild or Reorganize !!!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Why write your own when there are so many good and well-tested scripts around. http://www.sqlfool.com for starters.

My guess, those are really small indexes (<24 pages)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

You're not filtering on the page count. Fragmentation is not really an issue untill you hit at least 1000 pages in the index.

So if you defrag or reorg a very small index, it's likely that nothing will change at all.

fawwad SSC Eights! Points: 965 More actions · Answer 3

@gilamonster

buddy , i am trying to create my own to have better understading.

i will do mistakes and one day, this will be good enough to apply on live production db.

thanks for the link too, i will have a look at it.

@Ninja :

thanks for pointing me out here, this is what i want i was looking for from you guys...

ok it means i should filter this on Fragment_count or page_count ??

or a combination of above one with avg_fragmentation_in_per ?

please comment

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

page_count.

If you have the time to learn then go for it... make sure you test on a test machine rather than prod server.

Any errors there can cause serious issues.

fawwad SSC Eights! Points: 965 More actions · Answer 5

yes i am doing on testing database...

ok , i will try with page_count.

shoud i consider both or only Page_count ?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

You're on the right track... 10% fragmentation minimum, reorg @ 29% or less rebuild at 30% and make sure to have at least 1000 pages in there.

Not much else for a basic script.

fawwad SSC Eights! Points: 965 More actions · Answer 7

fawwad

SSC Eights!

Points: 965

November 22, 2010 at 9:23 am

#1253826

thanks

i will try this out...