January 5, 2012 at 10:36 am
The CLIENTS table has just one key (primary) clustered on clientCode (int data type)
We use this table for SELECT query only. If we have to insert clients ( we do so 3 or 4 times a year) we insert in another table on another server and the data is replicated over to this CLIENTS table.
The fillfactor of this CLIENTS table was 0 and the frgmentation was 50%
Since this table is used for SELECT I rebuild the index with fillfactor 100 however now the fragmettion is up to 77%
I was thinking with fillfactor=100 the fragmentation should have been negligible.
Thanks
January 5, 2012 at 10:52 am
how many row???
what size in kbytes?
January 5, 2012 at 11:01 am
rfr.ferrari (1/5/2012)
how many row???what size in kbytes?
total number of rows - 361
I don't know how to find the kb.
January 5, 2012 at 11:08 am
Guras (1/5/2012)
total number of rows - 361I don't know how to find the kb.
361 rows???
the size is in property of table - storage!!!
January 5, 2012 at 11:10 am
Guras (1/5/2012)
rfr.ferrari (1/5/2012)
how many row???what size in kbytes?
total number of rows - 361
I don't know how to find the kb.
With 361 rows, you do not need to worry about index fragmentation.
Jared
CE - Microsoft
January 5, 2012 at 1:05 pm
If the size is less than 8 pages, it's going to be stored on mixed extents and defragging is going to be an utter waste of time. You might even see fragmentation go up on occasion.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 5, 2012 at 1:55 pm
how many time to execute the select command in table?
January 5, 2012 at 2:05 pm
rfr.ferrari (1/5/2012)
how many time to execute the select command in table?
It takes about a second to return all the 361 rows.
January 5, 2012 at 3:14 pm
Why are you concerned here... Are you experiencing performance problems or are you just digging?
Jared
CE - Microsoft
January 6, 2012 at 4:07 am
I dont think you should worry on this fragmentation.
Don’t do anything if the index has < 1000 pages
January 6, 2012 at 4:23 am
Well actually I'd be concerned if it took 1 whole second to return so little data.
But I'll go on a limb and assume it's much less than 1 and let it slide for now 😉
January 6, 2012 at 5:09 am
I hope this link can help you to understand the topic.
http://sqlblog.com/blogs/kalen_delaney/archive/2008/02/28/fragmentation-revisited.aspx
January 6, 2012 at 7:00 am
Ninja's_RGR'us (1/6/2012)
Well actually I'd be concerned if it took 1 whole second to return so little data.But I'll go on a limb and assume it's much less than 1 and let it slide for now 😉
Let me re-phrase this...the rows are returned at the blink of an eye 🙂 so definitely I am letting it slide by.
January 6, 2012 at 7:00 am
SQLKnowItAll (1/5/2012)
Why are you concerned here... Are you experiencing performance problems or are you just digging?
Just digging...
January 6, 2012 at 7:44 am
Guras (1/6/2012)
SQLKnowItAll (1/5/2012)
Why are you concerned here... Are you experiencing performance problems or are you just digging?Just digging...
Fragmentation being displayed as a percentage of say anywhere under 100 pages is meaningless. Rebuilding the index will not fix this. Think of it this way, if I have a puzzle with only 5 pieces is it really that hard to put together? Even with the pieces out of order you can tell what the image is. As the number of pieces increase the percentage of 100, 90, 80, whatever, means that more pieces are out of order. That's when fragmentation matters. Some articles even say that unless the index is 1000 pages, fragmentation does not matter. Take a look at this article, it may help you:
http://sqlblog.com/blogs/kalen_delaney/archive/2008/02/28/fragmentation-revisited.aspx
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply