SQL SERVER – What is Forwarded Records and How to Fix Them?

During the early year of my career, one of the most popular questions I used to receive was – What is Forwarded Record? I used to find this question very valid as many people yet did not know what is the forwarded record. In most of the cases as time passes by more knowledge is spread amount SQL Server users and the number of questions reduces on a single topic. However, this is something I can’t say is true for Forwarded Records.

I often get questions related to forwarded records and I have previously blogged about it last year over here: What are Forwarded Records in SQL Server? – Interview Question of the Week. Well, in any case, let us understand what has forwarded records.

Forwarded Records

Forwarded Records are the data row in a heap which has 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 the 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.

Script to Create Forwarded Records

In this script first, we will create a forwarded record. It is very simple to create a forwarded record, hence if you have a heap (a table without a clustered index) it has lots of forwarded records.

-- Create a test database
CREATE DATABASE SQLAuthority
GO
USE SQLAuthority
GO
-- Create a sample table
CREATE TABLE HeapTable
(Col1 VARCHAR(100))
GO
-- Insert Sample Values
INSERT INTO HeapTable (Col1)
VALUES ('SmallString')
GO 5000
-- Check Forwarded Record Count
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
GO
-- Update Col1 with Larger Value
UPDATE HeapTable
SET Col1 = 'This is very long string'
GO
-- Check Forwarded Record Count
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
GO

When you check the forwarded record for the first time, it will only show you 0 (Zero) forwarded records. However, when you run the same script again you will see a very high number. On my machine, I noticed the numbers as high as 2269. Additionally, the page count also increased from 16 to 31, which is almost double. In a normal case, we may be not sure if that value is correct or not. However, if there is very high value for forwarded record count, the page count is indeed higher page count than an actually required page.

Fix Forwarded Records

You can remove the forwarded record by running following alter rebuild command and check once again how many forwarded records are there.

-- Rebuild the table
ALTER TABLE HeapTable REBUILD
GO
-- Check Forwarded Record Count
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
GO
-- Clean up
USE master
GO
DROP DATABASE SQLAuthority
GO

You can clearly see in the resultset the forwarded records are set to Zero (0) and the actually required page is reduced from 31 to 23. The difference of the pages to store is about 9 pages and that difference was created by a high number of forwarded record. This can be a big issue when your table is huge.

I strongly suggest creating a clustered index on the HEAP tables as it mitigates many other performance problems compared to a heap, however that is subject to another blog post.

Reference: Pinal Dave (https://blog.sqlauthority.com)