Fragmentation on Heap

Do you think a Heap would have a Page fragmentation? As long as a heap is not logically ordered, do you think a heap would undergo a page fragmentation???

Fragmentation happens when logical ordering is different from the physical ordering. A heap will not have any orders on its data.However Heaps are with "Forwarding Pointers"
Which would be really a mess in terms for the heaps' performance. When we modify a row and the row modified does not fit in the same page, a forwarding pointer will create moving the modified row to a new page and leaving a pointer to the old location which is called forwarding record. Forwarding record which points to the new location of the record is called forwarded record. This is for a better performance because all the non-clustered index on the heap do not have to be altered to the new location as long as there are forwarding records in the old location.

If we have more number of forwarding pointers, the read operation would go back and forth to get the rows in a heap. This would create a performance issues in the case of heaps.

How do we get rid of this?
Method A:
We can create a clustered index and drop the index would remove the forwarding pointers by creating a clustered index. However the removal of cluster index will retain the structure too. However there are some limitations/performance issues with this method:
1. Clustering a large heap is costly opeartion as it has to do SORTing.
2. Dropping the index has to update to reflect the Free space on each page.
Method B:
In SQL server 2008, it introduces a concept to rebuild the table. Rebuilding the table actually does a compression of pages in a heap(need to specify the compression option).
Alter table rebuild may cause rebuild of non-clustered indexes created on the heap.Bacause of the possibility to change the RIDs. Even though the non-clustered indexes are getting rebuilt, if we want to enforce the compression, we need to explicity compress the indexes using alter index with compression option.


--How to find out the forwarding pointers?
SELECT object_name(object_id) as Object, page_count,
avg_page_space_used_in_percent, forwarded_record_count
FROM sys.dm_db_index_physical_stats (DB_ID('DB_Name'), object_id ('Table_Name'), null, null, 'DETAILED')
Where IsNull(forwarded_record_count,0)<>0 and page_count>0;


Note: DBCC CLEANTABLE Will never clean up the forward pointers on a heap even though it reclaims the spce.