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)
2 Comments. Leave new
what happens in case of clustered index ???
There are no forwarded records in that case.