Workaround for forwarded record counter in SQL Server affecting IO performance _mssql

First, Introduction

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

Second, the principle of storage

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

Figure 1. Forwarded record indicating

This is due to data update, only in the original position left pointer to the new data page storage location line, is called the forwarded record.

Iii. How does the forwarded record affect IO performance?

So why does the forwarded record cause performance problems, since it's a mechanism for improving performance? The original purpose of the forwarded record is to update the heap table so that the change in the storage location on the heap table does not update the nonclustered index at the same time overhead. But for lookups, whether a table scan exists on a heap table, or a bookmark lookup, the extra IO overhead is multiplied, as shown in the following example.

Begin TRAN While
@index < 100000 
  begin 
    inserts into dbo. Heaptest
        (ID, col1)
    VALUES (@index, NULL)
    SET @index = @index + 1

  end
COMMIT

Code Listing 1. New heap table and insert 100,000 piece of data

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

Figure 2. Heap Table Space occupancy

The table is updated to allow the original row to grow and produce a forwarded record, at which point the storage of the heap table can be seen. As shown in Figure 3.

Fig. 3. Forwarded record of producing 8w+

At this point we noticed that although the data only accounted for 590 pages, but there was a 8w+ forwarded record, if we scanned the table, we would see that although only 590 pages were needed, the 8w+ logic IO would greatly increase the overhead pressure on Io, in addition to the forwarded The record page and the original page are often not physically contiguous, so there is also a challenge to IOPS. As shown in Figure 4.

Figure 4. Additional IO overhead not to be generated

The above query is reflected in the performance counter, and is rendered as the result shown in Figure 5.

Figure 5. Forwarded record counter growth

Iv. How to Solve

Seeing the forwarded record counter indicates that there is a heap table in the database and that there should be a clustered index on all the tables in the OLTP system. Therefore, you can resolve the problem by adding a clustered index to the table.

Typically, only tables that are read-only are set to a heap table, but if you see a forwarded Reocord, there is a read on the heap table, then finding the heap table and finding a suitable maintenance window time creating a heap table is an ideal choice.

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