SQL Server: Must know – the Forwarded Records in HEAP Table

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am sharing an important note on Forwarded Records in the Heap table of SQL Server. Whenever DBA or Developer is facing performance issue, they are always talking about the table or index fragmentation, but no-one is discussing the Forwarded Records.

What is heap Table

Simple Example to understand Forwarded Records:

You create a heap table, and one of the column types is VARCHAR(200).
You inserted 100000 records and added a small size of the string in VARCHAR(200) like “dbrnd” value which is only VARCHAR(5).

Now next, you require updating the value from “dbrnd” to “Database Research & Development” which is now VARCHAR(31).
Previously, we stored our data based on VARCHAR(5), but while updating we need VARCHAR(31).

Before the update, all data correctly save in data page and for new update no more free space available on the data page. So SQL Server internally moves new data into the new page and assigned a new Relative Identifier (RID).

Now old page contains forwarding pointer to new RID and new records also back pointer to old records.

– Called as Forwarded Records and which is also one type of internal fragmentation.

The biggest drawback of Forwarded Record is, it requires high I/O because SQL Server has to jump between the old data page and new data page for getting data.

Few more following topics, which I will publish from tomorrow:
How to find the Forwarded Records?
How to remove Forwarded Records?