How to fix Forwarded Records in a Heap

A heap is a table without a clustered index. Forwarded records are records in a heap that have grown too large for the page that it currently resides on. These types of records can only be found in heaps because tables with a clustered index keep the data sorted based on the clustered index. If a heap row gets updated and grows too big for it to stay on the same page, SQL Server will move it to a new page slot and leave a pointer at its original spot. Therefore, looking for a row will be more expensive afterwards. A lookup of the data row in a heap is no longer a direct access using the page and slot address. Instead of getting the data row the server might have to follow a forward pointer first.
You can temporarily fix forwarded records in heaps by rebuilding heaps. Note that rebuilding heaps was added to SQL Server starting with SQL Server 2008. You can’t rebuild heaps in SQL Server 2005 or lower. But creating a clustered index on it can avoid forward records permanently.
Below sql script list out all heaps in current database:
SELECT SCH.name + '.' + TBL.name AS TableName
FROM sys.tables AS TBL
     INNER JOIN sys.schemas AS SCH
         ON TBL.schema_id = SCH.schema_id
     INNER JOIN sys.indexes AS IDX
         ON TBL.object_id = IDX.object_id
            AND IDX.type = 0 -- = Heap
ORDER BY TableName;
You can then check the forwarded_record_count by using DMF sys.dm_db_index_physical_stats one by one table:
SELECT OBJECT_NAME(object_id) AS table_name, forwarded_record_count, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('<tableName>'), DEFAULT, DEFAULT, 'DETAILED');