DaveWentzel.com            All Things Data

Forwarded Records

 

Forwarded records basically result in fragmentation and therefore can cause performance degradation.  Forwarded records are only found on heaps (tables without a clustered index, hence unordered).   A forwarded record occurs when a table (permanent or temporary) has variable width cols that are at first populated, then later updated with wider values.  Because of this the row may no longer fit on the data page so a new data page is created.  A pointer is left in the original data page and the new data page indicating the old and new location of the data.  That's a forwarded record and fragmentation.   
 
I recommended very few tables should ever exist without a clustered index, but there may be occassions.  So look out for this.  Be aware of this in large temp tables and during batch manipulation processes. 
 
You can't remove the fragmentation using DBCC DBREINDEX, INDEXDEFRAG, etc because these commands only work on *indexes*.  You really have to build a clustered index (and later remove it if desired), or SELECT INTO a new table to reload the data. 
 
You can however, view the fragmentation using sys.dm_db_index_physical_stats...you are looking for forwarded_record_count.  This count can actually be higher than the number of rows in your table if multiple updates have occurred to the heap. 
 
 

Add new comment