April 5, 2011 at 8:19 am
CirquedeSQLeil (4/5/2011)
You can rebuild the NC but fragmentation will persist. A clustered index is required to defrag the NCs.
Jason, I must confess I don't understand. What's the difference between rebuilding and defragmenting? Do you have any references on this?
The way I understand it, if you rebuild a non clustered index, it will be physically sorted in the order of the index key, with no empty space in it above that provided for in the fill factor. It's true that performance of the index may not be optimal because of the fragmentation (if that's the right word) of the rows in the underlying table, but that doesn't mean that the non clustered index is fragmented - does it?
John
April 5, 2011 at 9:10 am
John Mitchell-245523 (4/5/2011)
CirquedeSQLeil (4/5/2011)
You can rebuild the NC but fragmentation will persist. A clustered index is required to defrag the NCs.Jason, I must confess I don't understand. What's the difference between rebuilding and defragmenting? Do you have any references on this?
The way I understand it, if you rebuild a non clustered index, it will be physically sorted in the order of the index key, with no empty space in it above that provided for in the fill factor. It's true that performance of the index may not be optimal because of the fragmentation (if that's the right word) of the rows in the underlying table, but that doesn't mean that the non clustered index is fragmented - does it?
John
Incomplete thought - apologies. You can run the rebuild process, but it doesn't actually defrag the index (heap table). The heap will remain fragmented. You can verify this by finding a fragmented index on your system rebuild it and recheck the fragmentation of the index and table (must be on a table that does not have a CI).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 5, 2011 at 9:25 am
Okay, let me add a little confusion here. There are really 2 types of fragmentation that can occur in a heap - internal fragmentation and external fragmentation. The internal fragmentation is definately caused by updates / deletes, leaving splits, forwarding pointers, and empty rows an a page. But external fragmentation will probably occur over time because of the allocation of new extents to the table to store the new records. That cannot be fixed in a heap as far as I can figure out. The extents will end up physically dispersed across the physical disk, causing longer I/O times.
Does that sound correct?
April 5, 2011 at 9:52 am
Growing your table and eventually growing the data file(s) on the disk subsystem can lead to external fragmentation and cannot be repaired from within SQL server at all. To fix disk fragmentation you would need to stop sql services and try to defrag the disk system. Doing so could be a very long process and may not be feasible. That is part of the reason for growing your data files out to an appropriate size with plenty of empty space - so data file growths will not occur for quite some time.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 5, 2011 at 9:59 am
OK, I found a table with only one index, which is non-clustered. The table has 400000 rows and the fragmenation of the index was 57.9%. I rebuilt the index and the fragmentation went to 0. However, the fragmentation of the heap appears to have gone up - it's now 98%. I didn't make a note of what it was before, but I don't think it was that high! I'm not quite sure what the percent framgmentation means in the context of a heap. That'll be my next reading project, I suppose.
John
April 5, 2011 at 10:03 am
Excellent. I re-read my last post and must confess that it must be too early ;-). Index should table - obviously as your evidence shows.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 5, 2011 at 11:42 am
Rich-403221 (4/5/2011)
Okay, let me add a little confusion here. There are really 2 types of fragmentation that can occur in a heap - internal fragmentation and external fragmentation. The internal fragmentation is definately caused by updates / deletes, leaving splits, forwarding pointers, and empty rows an a page. But external fragmentation will probably occur over time because of the allocation of new extents to the table to store the new records. That cannot be fixed in a heap as far as I can figure out. The extents will end up physically dispersed across the physical disk, causing longer I/O times.Does that sound correct?
That's basically my understanding and one of the problems with a heap table.
----------------------------------------------------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
April 5, 2011 at 12:48 pm
John Mitchell-245523 (4/5/2011)
If your table only has inserts, with no deletes or updates, then you are not likely to experience a lot of fragmentation. So no, a heap is not fragmented by default, but will become fragmented if it undergoes operations that cause page splits in the middle of a page or leave gaps at random or arbitrary places in the pages.
Heaps don't get page splits.
New rows go onto any page that has space. Updates that exceed the available space result in the row being moved and a forwarding pointer being left behind.
If by fragmentation the OP means logical fragmentation, then a heap cannot be fragmented. Logical fragmentation is a measure of how physical and logical order differ, and a heap has no logical order.
A heap can suffer from extent fragmentation, but not logical fragmentation. Only indexes can have logical fragmentation (cluster and noncluster)
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 5, 2011 at 12:50 pm
CirquedeSQLeil (4/5/2011)
John Mitchell-245523 (4/5/2011)
CirquedeSQLeil (4/5/2011)
a non-clustered index cannot be defragged without the presence of a clustered index.Jason, are you sure about this? My understanding is that you can rebuild the non clustered index, but that this will not have any effect on the actual rows of the table - in other words it won't get rid of the gaps caused by page splits where updates and deletes have occurred.
You can rebuild the NC but fragmentation will persist. A clustered index is required to defrag the NCs.
You can rebuild a NC index no matter what the base table is.
Rebuilding the NC index will remove fragmentation in the NC index. It won't affect the heap any more than rebuilding a NC index will remove fragmentation in the cluster.
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 5, 2011 at 1:00 pm
GilaMonster (4/5/2011)
CirquedeSQLeil (4/5/2011)
John Mitchell-245523 (4/5/2011)
CirquedeSQLeil (4/5/2011)
a non-clustered index cannot be defragged without the presence of a clustered index.Jason, are you sure about this? My understanding is that you can rebuild the non clustered index, but that this will not have any effect on the actual rows of the table - in other words it won't get rid of the gaps caused by page splits where updates and deletes have occurred.
You can rebuild the NC but fragmentation will persist. A clustered index is required to defrag the NCs.
You can rebuild a NC index no matter what the base table is.
Rebuilding the NC index will remove fragmentation in the NC index. It won't affect the heap any more than rebuilding a NC index will remove fragmentation in the cluster.
As always - Gail sets it straight. 😎
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply