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

Today, I will talk to you about how to understand Forwarded Record in SQL Server. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

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. As shown in figure 1.

Figure 1.Forwarded Record schematic

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.

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.

CREATE TABLE dbo.HeapTest (id INT, col1 VARCHAR) DECLARE @ index INT SET @ index = 0 BEGIN TRAN WHILE @ index < 100000 BEGIN INSERT INTO dbo.HeapTest (id, col1) VALUES (@ index, NULL) SET @ index = @ index + 1 END COMMIT

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, as shown in figure 2.

Figure 2. Heap table space occupation

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. As shown in figure 3.

Figure 3. Generate 8W + forwarded record

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. As shown in figure 4.

Figure 4. Additional IO overhead that should not be incurred

The above query is reflected in the performance counter and is rendered as shown in figure 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 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.

After reading the above, do you have any further understanding of how to understand Forwarded Record in SQL Server? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.