Forwarding records on a heap table

Today's article I want to talk about the performance issues that are unique to the heap table: forwarding Records (Forwarding Records). First we need to clarify what is a heap table: a heap table is a table that does not have a clustered index definition. It is very fast to insert new records, but it is very slow when you read the data. Reading data introduces random access on your storage subsystem, and sometimes when you encounter a forwarding record, it can further reduce your read performance.

Why is there a forwarding record?

When records in the heap table need to be moved to a different physical location, SQL Server uses a forward record. Suppose you have a table with a variable length column, first you insert some records in the heap table, and this time you don't store any data in the variable length column:

1 --Create A table to demonstrate forwarding records2 CREATE TABLEheaptable3 (4Col1INT IDENTITY(1,1),5Col2CHAR( -),6Col3VARCHAR( +)7 )8 GO9 Ten --Insert 4 records-those'll fit into one page One INSERT  intoHeaptableVALUES A ( -     REPLICATE('1', -), -     "' the ), - ( -     REPLICATE('2', -), -     "' + ), - ( +     REPLICATE('3', -), A     "' at ), - ( -     REPLICATE('4', -), -     "' - ) - GO

Imagine what happens when you execute an UPDATE statement in a variable-length column? In that case, SQL Server might need to extend this record because the record size is longer, and the other records must be removed from the same data page.

1 -- Let's update the table and expand each row of the table 2 UPDATE heaptable 3 SET = REPLICATE ('5') 4 GO

In that case, SQL Server leaves the original location known as a forward record, which points to the new location that records the final store.

SQL Server needs to use this method to avoid updating all nonclustered indexes on the same table. You may know that when you create a nonclustered index on a heap table, the nonclustered index points to the physical location of the record data store at the leaf layer. Without a scratch record, all of these pointers have to be changed, which can drastically degrade your performance.

How do I fix a forwarding record?

In order to find out if the table contains forwarding records, you can use DMF sys.dm_db_index_physical_stats. When you call this function on the heap table and pass in detailed mode, SQL Server tells you the number of forwarded records on the table through the forwarded_record_count column.

1 --Check The forwarding record count through Sys.dm_db_index_physical_stats2 SELECT3 Index_type_desc,4 Page_count,5 Avg_page_space_used_in_percent,6 Avg_record_size_in_bytes,7 Forwarded_record_count8  fromsys.dm_db_index_physical_stats9 (Ten     db_id('allocationdb'),  One     object_id('heaptable'), A     NULL,  -     NULL,  -     'detailed' the ) - GO

As you can see, there are 2 forwarding records on the table, outside of the 4 records. To get rid of these forwarding records, you can rebuild the table.

1 -- Rebuild The heap table to get rid of the Forwarding Records 2 ALTER TABLE heaptable REBUILD 3 GO

Run the query again and you will find that the forwarding record has disappeared.

1 --Check The forwarding record count through Sys.dm_db_index_physical_stats2 SELECT3 Index_type_desc,4 Page_count,5 Avg_page_space_used_in_percent,6 Avg_record_size_in_bytes,7 Forwarded_record_count8  fromsys.dm_db_index_physical_stats9 (Ten     db_id('allocationdb'),  One     object_id('heaptable'), A     NULL,  -     NULL,  -     'detailed' the ) - GO

The DBA always considers index fragmentation, index rebuilds, and index re-organization operations. But no one thought about the forwarding record in the pile table. This is a good idea if you maintain your database, often checking the number of forwarding records on the heap table, and ensuring that you always have the best performance.

Summary

In this article you see how the forwarding record on the heap table is, how it reduces the record read because additional logical reads are required. When I check the heap table, I always look at the number of forwarded records when I am checking the database for health.

Trust me: There will be a huge number of heap tables in the database, and there will be a lot of forwarding records in the production system, but DBAs are not aware of this side effect. As a first experience, I often recommend establishing a clustered index on a table to avoid forwarding records. Of course, in some specific scenarios, such as the last page Insert lock competition (Latch contention), here you can use the heap table to avoid this problem, but in most cases, it is very useful to build a clustered index on the table.

Forwarding records on a heap table