What are Forwarded Records in SQL Server? – Interview Question of the Week #145

October 22, 2017

Question: What are Forwarded Records in SQL Server?

Answer: It is interesting to see this question keeps on coming up in the interview questions. Even though this is very old concept, I hardly see new DBAs know about this subject.

Let us see the answer the question about the forwarded records.

What are Forwarded Records in SQL Server? - Interview Question of the Week #145 forwardedrecords-800x199

Forwarded Records

Forwarded Records are the data row in a heap which have moved from the original page to the new page. A heap table is a table without a clustered index. When a user updates a column with a larger size data, it is possible that particular field now can’t fit on the same data page leaving a forwarded record of that data.

When SQL Server scans the heap to read the necessary data, they often encounter forwarding record pointers which usually exist on different page leading SQL Server to read more pages than required while dealing with the heap. This is the reason, often performance of the heap has been very bad.

Identify Forwarded Records

Here is a DMV, which you can use to identify forwarded records.

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(), DEFAULT, DEFAULT, DEFAULT, 'DETAILED')
WHERE forwarded_record_count is NOT NULL;

Fix for Forwarded Records

Method 1: Adding A Clustered Index

This is a best possible fix and one should create a clustered index on their heap table. However, this requires a design level conversation and not every organization is ready for this fix.

Method 2: Rebuild The Heap

This is a temporary fix and if you can’t implement Method 1, you must implement this method.