Return to Answer

Also as mentioned by Paul White in the comments of this answer, there are situations where heaps may not properly deallocate empty pages. Basically, if you find your workloads against a heap table is not primarily insert-driven or you find forwarded records are occurring often, you will want to look into clustering your table with a clustered index.

Also, there are situations where heaps may not properly deallocate empty pages. Basically, if you find your workloads against a heap table is not primarily insert-driven or you find forwarded records are occurring often, you will want to look into clustering your table with a clustered index.

This is actual fragmentation that does matter as it's introducing additional reads during a table scan, so depending on how many forwarded records you have in your heap, this could represent a significant chunk of I/O that you need to perform whenever you read the heap into memory.

As you can see, fragmentation in both tables in actually pretty high, but I'm not going to waste my maintenance time rebuilding either table as there are not enough forwarded records to be concerned about it yet. My reasoning here is because a heap is only ever brought into memory by a table scan operation or a RID lookup (in the event that a nonclustered index defined on the heap is non-covering). When the table scan is performed, the entire table is loaded into memory, at which point fragmentation doesn't matter nearly as much. I want to avoid table scans in general if possible, but when they occur, I want them as efficient as possible, so reducing forwarded pointers directly helps with that. RID lookups are also affected by forward pointers, so even seek operations against a NCI with a RID lookup is subject to extra I/O if it runs into a forwarded record. When I hit a certain threshold of forwarded pointers within my heap, I will then rebuild the table. That threshold changes based on the table in question, but my general rule is once the heap has 10% or more forwarded record pages in comparison to total pages, I'm looking to rebuild the heap. Most of the heaps in this database don't suffer from forwarded records, so I don't have to worry about it too much.  If you find forwarded records are occurring often though, you will want to look into clustering your table with a clustered index.

This is actual fragmentation that does matter as it's introducing additional reads, so depending on how many forwarded records you have in your heap, this could represent a significant chunk of I/O that you need to perform whenever you read the heap into memory.

As you can see, fragmentation in both tables in actually pretty high, but I'm not going to waste my maintenance time rebuilding either table as there are not enough forwarded records to be concerned about it yet. My reasoning here is that a heap is only ever brought into memory by a table scan operation or a RID lookup (in the event that a nonclustered index defined on the heap is non-covering). When the table scan is performed, the entire table is loaded into memory, at which point fragmentation doesn't matter nearly as much. I want to avoid table scans in general if possible, but when they occur, I want them as efficient as possible, so reducing forwarded pointers directly helps with that. RID lookups are also affected by forward pointers, so even seek operations against a NCI with a RID lookup is subject to extra I/O if it runs into a forwarded record. When I hit a certain threshold of forwarded pointers within my heap, I will then rebuild the table. That threshold changes based on the table in question, but my general rule is once the heap has 10% or more forwarded record pages in comparison to total pages, I'm looking to rebuild the heap. Most of the heaps in this database don't suffer from forwarded records, so I don't have to worry about it too much, but that's not necessarily true for your situation.

Also as mentioned by Paul White in the comments of this answer, there are situations where heaps may not properly deallocate empty pages. Basically, if you find your workloads against a heap table is not primarily insert-driven or you find forwarded records are occurring often, you will want to look into clustering your table with a clustered index.

As you can see, fragmentation in both tables in actually pretty high, but I'm not going to waste my maintenance time rebuilding either table as there are not enough forwarded records to be concerned about it yet. My reasoning here is because a heap is only ever brought into memory by a table scan operation or a RID lookup in the event that a nonclustered index defined on the heap is non-covering. I don't believe RID lookups are affected by forwarded pointers so I'm not concerned about those, so it's really table scans that are problematic. When the table scan is performed, the entire table is loaded into memory, at which point fragmentation doesn't matter nearly as much. I want to avoid table scans in general if possible, but when they occur, I want to make them efficient as possible, so reducing forwarded pointers directly helps with that. When I hit a certain threshold of forwarded pointers within my heap, I will then rebuild the table. That threshold changes based on the table in question, but my general rule is once the heap has 10% or more forwarded record pages in comparison to total pages, I'm looking to rebuild the heap. Most of the heaps in this database don't suffer from forwarded records, so I don't have to worry about it too much. If you find forwarded records are occurring often though, you may want to look into clustering your table with a clustered index.

As you can see, fragmentation in both tables in actually pretty high, but I'm not going to waste my maintenance time rebuilding either table as there are not enough forwarded records to be concerned about it yet. My reasoning here is because a heap is only ever brought into memory by a table scan operation or a RID lookup (in the event that a nonclustered index defined on the heap is non-covering). When the table scan is performed, the entire table is loaded into memory, at which point fragmentation doesn't matter nearly as much. I want to avoid table scans in general if possible, but when they occur, I want them as efficient as possible, so reducing forwarded pointers directly helps with that. RID lookups are also affected by forward pointers, so even seek operations against a NCI with a RID lookup is subject to extra I/O if it runs into a forwarded record. When I hit a certain threshold of forwarded pointers within my heap, I will then rebuild the table. That threshold changes based on the table in question, but my general rule is once the heap has 10% or more forwarded record pages in comparison to total pages, I'm looking to rebuild the heap. Most of the heaps in this database don't suffer from forwarded records, so I don't have to worry about it too much. If you find forwarded records are occurring often though, you will want to look into clustering your table with a clustered index.