1

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?

4

1 Answer 1

2

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.

1
  • Thanks. So, in essence a forwarded record creates fragmentation. I'm not surprised that data changes are the cause of fragmentation (and now I know how), I'm surprised A) that I've got heaps with so much change (i.e. fragmentation), and B) that SQL cares about forwarded records. I thought for a heap, if a record didn't fit, SQL would just move it and not care since order is irrelevant. And, if another record fit later, it could just reclaim the old space. But that's not how it works though. Thanks.
    – John
    Aug 30, 2016 at 4:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.