The Forwarded Record counter in SQL Server affects the IO performance.

The Forwarded Record counter in SQL Server affects the IO performance.

I. Introduction 

Recently, a customer noticed a very high counter (Forwarded Records/Sec), accompanied by intermittent fluctuations in the disk wait queue. In this article, we will share what is forwarded record and discuss in principle why Forwarded record causes additional IO.

Ii. Storage Principle

In SQL Server, when data is stored in the form of heap, the data is unordered, and all non-clustered index pointers are stored in the RID pointing to the physical address. When the variable-length column growth in the Data row makes the original page unable to accommodate the data row, the data will be moved to the new page, and a pointer pointing to the new page will be left in the original position, the reason for doing so is that when a Record is updated, all non-clustered index pointers do not need to be changed. 1.

Figure 1. Forwarded Record Diagram

In this case, only the pointer is left in the original position to point to the row where the new data page is stored, which is called Forwarded Record.

Iii. How does Forwarded Record affect IO performance?

Since Forwarded Record is a mechanism for improving performance, why does it cause performance problems? The original intention of Forwarded Record is to update the heap table without updating non-clustered indexes at the same time. However, for searching, whether a heap table has a table scan or a bookmark query, it will multiply the additional IO overhead. Let's take a look at the example below.

BEGIN TRANWHILE @index < 100000   BEGIN     INSERT INTO dbo.HeapTest        ( id, col1 )    VALUES ( @index, NULL )    SET @index = @index + 1  ENDCOMMIT

Code List 1. Create a heap table and insert 0.1 million pieces of data

Create a test table through code listing 1 and insert 0.1 million data cyclically. In this case, we can see the number of pages occupied by the heap table, as shown in figure 2.

Figure 2. Occupied heap tablespace

In this case, the table is updated to increase the original row and generate a Forwarded Record. Now let's look at the storage of the heap table. 3.

Figure 3. Generate + forwarded record

At this point, we noticed that although the data only accounts for 590 pages, there are 8 million + forwarded record. If we scan the table, we will see that although there are only 590 pages, however, more than logic IO is required, which greatly increases the overhead of IO. In addition, because the forwarded record page and the original page are often not physically consecutive, there are also challenges to IOPS. 4.

Figure 4. Additional IO overhead that should not be generated

The result shown in Figure 5 is displayed when the above query is reflected in the performance counter.

Figure 5. Forwarded Record counter Growth

4. Solutions

The Forwarded Record counter indicates that there are heap tables in the database. In the OLTP system, clustered indexes should exist on all tables. Therefore, you can add clustered indexes to the table to solve this problem.

Generally speaking, it is more appropriate to set only write-not-read tables as heap tables. However, if Forwarded Reocord exists, it indicates that the heap table has read operations. Find the heap table, it is ideal to create a heap table at an appropriate maintenance window.

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