Forwarded Records in a Heap

Have you ever noticed a situation when you have a heap table
(a table without a clustered index)
And when you do select * from this table you see that IO reads(logical reads) are much bigger than the total number of pages this table has?

Let`s see an example,
We will create a table, populate it with data and see how many pages it takes.
Afterward, we will do an UPDATE to increase the size of rows in the table and see how many pages it takes.

CREATE TABLE dbo.TestTable

(

ID INT NOT NULL,

FName VARCHAR(100) NOT NULL

)

GO

-- Populate some data
INSERT INTO dbo.TestTable (ID, FName)
VALUES (1,'Dan')
GO 600

-- Check how many pages we have (see page_count field)
SELECT index_type_desc, page_count, avg_page_space_used_in_percent,
avg_record_size_in_bytes,forwarded_record_count
FROM sys.dm_db_index_physical_stats(DB_ID('Test'),OBJECT_ID('TestTable'),NULL,NULL,'detailed')
GO

We can see that the table takes 2 pages

And if we return all data from the table we will see 2 Logical Reads = 2 Pages

SET STATISTICS IO ON

SELECT * FROM dbo.TestTable

Now let`s update the Fname field to increase the size of the rows

UPDATE dbo.TestTable

SET FNAme=FName+FName

After the UPDATE, run the SELECT * and see how many logic reads we have now:

Now it does 66 Logical Reads, but the number of pages that table takes are still 2:

-- Check how many pages we have (see page_count field)
SELECT index_type_desc, page_count, avg_page_space_used_in_percent,
avg_record_size_in_bytes,forwarded_record_count
FROM sys.dm_db_index_physical_stats(DB_ID('Test'),OBJECT_ID('TestTable'),NULL,NULL,'detailed')
GO

So why we are reading 66 pages?

If you look at the 'forwaded_record_count' field you will see that we have 64.

Meaning we have read 2 original pages plus 64.

So, when a row in a heap is increased in size and no longer fits the original page,
SQL server will not simply move the row to a new page it will place a pointer

to a new page (in the original row).

This is done in order not to update each NC index to point to the new page.

Worth to note, that when you decrease the size of a row and there is still space available in the original page, SQL will move the data to its original page and remove the forwarded pointer.