April 6, 2009 at 5:19 am
Hi experts,
I have question about fragmentation indexes.
I have table with with Primary key(clustered index).
Fragmentation is 30 percent on nonclustered index, then I run reindex on this index,
after that fragmentation was 70 percent. It is high before fragmentation
It is interesting for me:( why?
Thanks Radek
April 6, 2009 at 6:38 am
How big's the index? How many 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
April 6, 2009 at 6:40 am
radek (4/6/2009)
Hi experts,I have question about fragmentation indexes.
I have table with with Primary key(clustered index).
Fragmentation is 30 percent on nonclustered index, then I run reindex on this index,
after that fragmentation was 70 percent. It is high before fragmentation
It is interesting for me:( why?
Thanks Radek
As Gail suggested what is the size of the table.
If its a small table then fragmentation do exist and we cant really defrag it, its just waste of resources.
April 6, 2009 at 7:06 am
Table is not big, about 20 000 rows with 15 columns, index has 18 pages, and depth=2.
What cause that it cant fragmentation? only few pages?
thanks Radek
April 6, 2009 at 7:08 am
Small indexes often don't rebuild properly. It's nothing to worry about, fragmentation isn't an issue on such small indexes.
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
April 6, 2009 at 7:32 am
radek (4/6/2009)
Table is not big, about 20 000 rows with 15 columns, index has 18 pages, and depth=2.What cause that it cant fragmentation? only few pages?
thanks Radek
But let me ask you what made you to defrag your indexes were you experiencing any performance problems or is it a part of your maintenance plan? Since its a small table it shouldn't
April 6, 2009 at 8:00 am
But let me ask you what made you to defrag your indexes were you experiencing any performance problems or is it a part of your maintenance plan? Since its a small table it shouldn't
Im writing thesis, on University, the topic is Optimization in relational databases...
So its about performance.
When I read and write about indexes I read any introduction from Microsoft that say if fragmentace is higher than 30 percent than it is good to do rebuilt if smaller than reindex..
So I wrote script that contains this condition.
After the run script
any indexes doesnt fragment..
Radek
April 6, 2009 at 8:06 am
radek (4/6/2009)
But let me ask you what made you to defrag your indexes were you experiencing any performance problems or is it a part of your maintenance plan? Since its a small table it shouldn'tIm writing thesis, on University, the topic is Optimization in relational databases...
So its about performance.
When I read and write about indexes I read any introduction from Microsoft that say if fragmentace is higher than 30 percent than it is good to do rebuilt if smaller than reindex..
So I wrote script that contains this condition.
After the run script
any indexes doesnt fragment..
Radek
Good work!!
Yes, thats true as what you read as a rule of thumb if fragmentation 30% you should be rebuilding them but i suppose it depends on your business model.
In your script you should also include something like i think it is if the number of pages >2000 then you should consider rebuilding or defragging them.
April 6, 2009 at 8:14 am
Krishna Potlakayala (4/6/2009)
In your script you should also include something like i think it is if the number of pages >2000 then you should consider rebuilding or defragging them.
1000, and it's a rule of thumb, not a hard absolute value.
The reason is that smaller indexes are often fully in memory and fragmentation only affects reading of an index from disk into memory. Also fragmentation's only a problem for large scans of data and is not a concern when only one or two pages are read from disk.
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
April 6, 2009 at 8:18 am
oh thanks for correcting it Gail !
also this might be useful there's a live webcast today on fragmentation, check this out:
April 6, 2009 at 8:29 am
Ok, thanks I add this condition into procedures.
BTW: Of course I know, if I have small table that it is not good to create index.. but i dont know that 20000 rows is too small :-), and its depands on number of pages of index.
BTW2: I know that SQL server contains tool to create maintenance plan, but I cant find that conditions.. then I write my procedures..
Thank to all for hints
Radek
April 6, 2009 at 8:39 am
radek (4/6/2009)
BTW: Of course I know, if I have small table that it is not good to create index.. but i dont know that 20000 rows is too small 🙂
No, that's absolutely not true. There's no reason not to index small tables and even a couple hundred rows may very well benefit from good indexes. 20000 definitely will benefit from good indexes.
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply