Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
When you talk about fragmentation in a heap that has no indexes, there can't be logical order fragmentation, so does fragmentation in this instance only refer to non-contiguous pages? Edit: no.
Is the same thing happening in a case where a heap has an index that has no fragmentation but the heap itself does have fragmentation?
Why does SQL Server create forwarded records at all, instead of just writing to a new page, and backfilling when it makes sense?
Imagine you have a heap and a record needs to be updated which causes it to grow. The new record size cannot fit on the DB page anymore. As a result a 'forwarding' record is created which is a pointer to which DB page the real record is. This is now fragmentation in your heap.
Thus, when you do a bookmark lookup on a heap with indexes on it you will still hit the forwarders which will cause more IO, and your indexes need to store the long RID. Removing that fragmentation based on the table will help reclaim size but almost always the internal storage engine prefers to work with a clustered index.
Background
Just for background there are a few kinds of fragmentation. The heap table itself might not be ordered, but it might have non clustered indexes on it which then need to reference the heap through a physical record ID (RID) which is a combination of file id, row id, and a few others. Basically where the record exists in the database page. This is not a logical RID. A logical RID would be like a unique clustered index and if your clustered index is not unique, it adds a uniqueifier similar to the RID. The DB engine would use this unique "ID" to find the record logically.
This is one reason a heaps row is sometimes wider than a table with a unique clustered index as it adds that info per record in your indexes.
