Forwarding Records on Heap Tables

In today’s blog posting I want to talk about a very specific performance problem that can occur on heap tables: Forwarding Records. First of all we have to clarify what a heap table is: a heap table is just a table without a Clustered Index. It is very fast for inserting new records, and very slow when you are reading records. Reading records can introduce random I/O on your storage subsystem, and sometimes you can also encounter Forwarding Records, which will further slow down your read performance.

Why Forwarding Records?

SQL Server uses Forwarding Records when a record in a heap table has to be moved to a different physical location. Imagine you have a table with a variable length column. In the first step you insert records into the heap table, and you are not storing any data in the variable length column:

-- Create a table to demonstrate forwarding records
CREATE TABLE HeapTable
(
	Col1 INT IDENTITY(1, 1),
	Col2 CHAR(2000),
	Col3 VARCHAR(1000)
)
GO

-- Insert 4 records - those will fit into one page
INSERT INTO HeapTable VALUES
(
	REPLICATE('1', 2000),
	''
),
(
	REPLICATE('2', 2000),
	''
),
(
	REPLICATE('3', 2000),
	''
),
(
	REPLICATE('4', 2000),
	''
)
GO

Imagine now what happens when you run an UPDATE statement against the variable length column. In that case SQL Server may have to expand the record, and because of the larger record size, other records must be moved away from the same data page.

-- Let's update the table and expand each row of the table
UPDATE HeapTable
SET Col3 = REPLICATE('5', 1000)
GO

In that case, SQL Server leaves a so-called Forwarding Record at the original location that points to the new location where the record is finally stored.

SQL Server has to use this approach to avoid having to update all of the Non-Clustered Indexes on the same table. As you might know, when you create a Non-Clustered Index on a heap table, at the leaf level the Non-Clustered Index points to the physical position where the record data is stored. Without Forwarding Records, all these pointers would have to be changed, and that would slow down your performance tremendously. In the following short flipchart demo, I want to show you how SQL Server introduces Forwarding Records on heap tables.

How to fix Forwarding Records?

To find out if a given heap table consists of Forwarding Records, you can use the Dynamic Management Function (DMF) sys.dm_db_index_physical_stats. When you call that function on a heap table and pass in the DETAILED mode, SQL Server returns the number of Forwarding Records on the table by way of the column forwarded_record_count.

-- Check the forwarding record count through sys.dm_db_index_physical_stats
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('ForwardingRecords'), 
    OBJECT_ID('HeapTable'),
    NULL, 
    NULL, 
    'DETAILED'
)
GO

As you can see, in our case we have two Forwarded Records on that table, out of four records! To get rid of these Forwarding Records you can rebuild your table.

-- Rebuild the heap table to get rid of the Forwarding Records
ALTER TABLE HeapTable REBUILD
GO

Afterwards, when you check the Forwarding Record count again through the DMF sys.dm_db_index_physical_stats, you can see that they are gone. DBAs are always thinking about index fragmentation, index rebuilds, and index reorganize operations. But nobody thinks about Forwarding Records in heap tables. If you work with maintenance scripts it’s always a good idea to check the number of Forwarding Records on heap tables to make sure that you get the best possible performance.

Summary

In this blog posting you have seen how Forwarding Records on heap tables can slow down your reads, because of the additional logical reads that are needed. When I perform SQL Server Health Checks for my customers, I always check for heap tables in combination with Forwarding Records.

And trust me: there are a huge amount of heap tables and even Forwarding Records out in production, and DBAs who are not aware of the side-effects. As a general rule of thumb I always recommend creating Clustered Indexes on tables to get rid of Forwarding Records. Of course there are some extreme scenarios like the Last Page Insert Latch Contention where you can also introduce performance problems with this approach, but for most ordinary databases that approach will work very fine.

Thanks for reading!

-Klaus