Forwarding Records on Heap Tables

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

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:

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.

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.

Forwarding Records in SQL Server

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.

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.

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.

Like or share this blog posting to get the source code.

Thanks for reading!

-Klaus

5 Comments

  • Mark Stacey

    Some interesting points. My personal recommendation, when working with heaps, is to never update or delete, instead use partitions, and when either of those occurs, write out and back the updated partition. This is of course only for data warehouses 🙂

  • Nice explanation Klaus, thanks.

  • Vinh Phan

    Thank Klaus, your lessons are very useful to me. Hope that you will post frequently

  • sateesh

    Very Good explanation Klaus,nice to know forwarded records column in DMV

It`s your turn

Your email address will not be published. Required fields are marked *

SQLpassion

Copyright © 2015 by SQLpassion · Klaus Aschenbrenner · Imprint · Offerings · Academy · Contact · Go to Top