What are forwarded records?

Forwarded records are records that reside on a new page when a heap table (table without clustered index) is updated, and the rows are no longer able to stay on the same page,

which causes a forwarding pointer to point to the new page that the row resides in.

Many forwarded records can cause serious I/O issues, and if this happens a lot, it is advised to create a clustered index on the table.

You can also rebuild the table to remove the forwarded records (high overhead).

 

 

sys.dm_db_index_physical_stats

sys.dm_db_index_physical_stats is the dmv used to find the forwarded records,

but this is a very I/O intensive dmv to query, which is why we will apply filtering in the script.

 

 

The script:

We will use a temp table to apply filtering, and only include the heap tables in our dmv checks.

 

SELECT O.*
INTO #temp
FROM   sys.objects O
inner join sys.tables AS T on o.object_id = T.object_id
INNER JOIN sys.schemas AS S 
ON T.schema_id = S.schema_id 
INNER JOIN sys.indexes AS I 
ON T.object_id = I.object_id 
AND I.type = 0;


SELECT  DB_NAME(db_id()) as DatabaseName,
		t.name as TableName,
		index_id, 
		index_type_desc,
		avg_fragmentation_in_percent,
		page_count,
		record_count, 
		forwarded_record_count 
FROM
#temp t
CROSS APPLY 
sys.dm_db_index_physical_stats
(
   DB_ID()
   ,t.object_id
   ,NULL
   ,NULL
   ,'DETAILED'
)
WHERE forwarded_record_count > 0 and t.is_ms_shipped =0

DROP TABLE #temp

 

 

I hope this script helped you achieve your goal, with the least amount of overhead.