Shulou(Shulou.com)05/31 Report--

The Forwarded Record counter in SQL Server affects the performance of IO. In view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

I. brief introduction

Recently, I noticed a high counter (Forwarded Records/Sec) from a customer, accompanied by intermittent fluctuations in disk waiting queues. This article shares what forwarded record is and explains in principle why Forwarded record causes extra IO.

Second, storage principle

In SQL Server, when the data is stored as a heap, the data is unordered, and the pointers to all nonclustered indexes hold the RID to the physical address. When the variable length column in the data row grows so that the original page cannot hold the data row, the data will move to the new page and leave a pointer to the new page in the original position, because the pointer of all nonclustered indexes will not change when updates to the Record occur.

Due to the update of the data, only the pointer is left in the original location to point to the row where the new data page is stored, which is called Forwarded Record.

3. How does Forwarded Record affect IO performance?

So since Forwarded Record is an existing mechanism to improve performance, why does it cause performance problems? The original intention of Forwarded Record is that when updating the heap table, the change of the storage location on the heap table will not cause overhead to update the nonclustered index at the same time. However, for lookup, whether there is a table scan on the heap table or used for bookmark lookup, it will multiply the additional IO overhead. Let's take a look at an example.

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

Listing 1. Create a new heap table and insert 100000 pieces of data

Create a test table through listing 1 and loop into 100000 data. At this point, let's look at the number of pages occupied by the heap table.

At this point, the table is updated to grow the original rows and generate Forwarded Record. At this point, let's look at the storage of the heap table.

At this time, we noticed that although the data accounts for only 590 pages, there is 8W + forwarded record. If we scan the table, we will see that although it is only 590 pages, we need 8W + logical IO, which greatly increases the overhead pressure on IO. In addition, because the forwarded record page is often not physically continuous with the original page, it is also a challenge to IOPS.

The above query is reflected in the performance counter.

How to solve the problem

Seeing the Forwarded Record counter indicates that there are heap tables in the database, and in the OLTP system, all tables should have clustered indexes. Therefore, this problem can be solved by adding a clustered index to the table.

Generally speaking, it is appropriate to set only the write-only but unread table to the heap table, but if you see the existence of Forwarded Reocord, it means that there is a read operation on the heap table, so finding the heap table and finding an appropriate maintenance window time to create the heap table is an ideal choice.

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

This is the answer to the problem that the Forwarded Record counter in SQL Server affects the performance of IO. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.