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.

How to get Page ID for row(s) in a table in SQL Server

There can be situations like in my previous post
Attempt to fetch logical page # in database failed
when you need to know to which page belong row(s)
in a table.

Here a query that can help you:

SELECT TOP 100 plc.*, c.*

FROM  dbo.Customers as c (nolock)
      CROSS APPLY sys.fn_physLocCracker (%%physloc%%) As plc
WHERE c.CustomerID=XXXX
GO

It gives you the database file id, page id within the file,
and slot number on the page:

There is also "sys.fn_physlocFormatter" function that simply
formats the output of the "fn_physLocCracker" function.

SELECT TOP 100 plc.*,
                   sys.fn_physlocFormatter(%%physloc%%) as [file_id:page_id:slot_id],g.GameID
FROM  dbo.Games as g (nolock)
             CROSS APPLY sys.fn_physLocCracker (%%physloc%%) As plc
WHERE g.gameid=1





Keep in mind that the function "fn_physLocCracker"
is undocumented and exists only in SQL Server 2008 and above.