A hidden IO performance killer-forwarded record in SQL Server

In the original: SQL Server A hidden IO performance killer-forwarded record

Brief introduction

Recently noticed a high counter in a customer (forwarded records/sec), accompanied by intermittent disk waiting queue fluctuations. This article shares what is the forwarded record and discusses in principle why the forwarded record creates additional IO.

Storage principle

In SQL Server, when data is stored as a heap, the data is unordered, and pointers to all nonclustered indexes hold rids pointing to the physical address. When a variable-length column in a data row grows so that the original page cannot hold the data row, the data is moved to the new page and a pointer to the new page is left in the original location because the pointers to all nonclustered indexes do not change when an update to the record occurs. As shown in 1.

Figure 1. Forwarded record signal

This is because the data update, only in the original location left a pointer to the new data page storage location row, is called the forwarded Record.

How does the forwarded record affect IO performance?

So why does the forwarded record cause performance problems if it is to improve the mechanism of performance? The original purpose of the forwarded record was to update the heap tables with the overhead of changing the storage location on the heap table without updating the nonclustered indexes at the same time. But for a lookup, whether a table scan exists on a heap table or a bookmark lookup, it multiplies the additional IO overhead, as shown in the following example.

CREATE TABLEDbo. Heaptest (IDINT, col1VARCHAR(800))

DECLARE@Index INT
SET@Index= 0
BEGIN TRAN
 while@Index< 100000
    BEGIN 
INSERT intoDbo. Heaptest
(ID, col1)
        VALUES( @Index,NULL)
        SET@Index= @Index+ 1

    END
COMMIT

Code Listing 1. Create a new heap table and insert 100,000 data

Create a test table from code Listing 1 and loop through the 100,000 data. Here we look at the number of pages that the heap table occupies, as shown in 2.

Figure 2: Heap Table space consumption

At this point, the table is updated to allow the original row to grow, producing a forwarded Record, and then look at the storage of the heap table. As shown in 3.

Figure 3. Forwarded record producing 8w+

At this point we notice that although the data only accounts for 590 pages, there is a 8w+ forwarded record, and if we scan the table, we will see that while only 590 pages are needed, the 8w+ logic IO greatly increases the overhead pressure on Io, in addition to the forwarded The record page and the original page are often not physically contiguous, so there are challenges to IOPS as well. As shown in 4.

Figure 4: Additional IO overhead that should not be generated

When the above query is reflected in the performance counter, the result is rendered as shown in 5.

Figure 5. Forwarded record counter growth

How to Solve

Seeing the forwarded record counter indicates that there are heap tables in the database and that all tables should have clustered indexes on the OLTP system. You can therefore resolve the problem by increasing the clustered index on the table.

Generally speaking, only write-only table is set to the heap table is more appropriate, but if you see the existence of forwarded Reocord, it indicates that there is a read operation on the heap table, then find the heap table, find a suitable maintenance window time to create the heap table is the ideal choice.

If you cannot create a clustered index for other reasons, you can perform a table rebuild on the heap table.