November 22, 2010 at 8:26 am
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 !!!
November 22, 2010 at 8:32 am
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
November 22, 2010 at 8:33 am
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.
November 22, 2010 at 8:50 am
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
November 22, 2010 at 8:57 am
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.
November 22, 2010 at 9:06 am
yes i am doing on testing database...
ok , i will try with page_count.
shoud i consider both or only Page_count ?
November 22, 2010 at 9:15 am
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.
November 22, 2010 at 9:23 am
thanks
i will try this out...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply