A hidden IO performance killer in forwarded Record:sql server

Brief 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.

Storage principle

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.

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/

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.

Createtabledbo.heaptest (id INT, col1 VARCHAR ())
    
declare@indexint
set@index= 0
Begintran while
@ index< 100000 
    BEGIN
        INSERT  intodbo.heaptest
                (ID, col1)
        VALUES  (@index, NULL)
        set@index= @index + 1

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+