What is forwarding pointer in SQL Server? How it is helpful in performance optimization?
Forwarding pointer: when you update the row in the heap table, SQL Server tries to accommodate it on the same page. If there is no free space available, SQL Server moves the new version of the row to another page and replaces the old row with a special 16-byte row called a forwarding pointer.
The forwarding record/pointer points to the new location of the record, which is known as a forwarded record. This is done as a performance optimization so that all the non-clustered indexes on the heap do not have to be altered with the new location of the heap record.
When the size of the forwarded row is reduced by another update and the data page with forwarding pointer has enough space to accommodate the updated version of the row, SQL Server may move it back to original data page and remove the forwarding pointer row. Nevertheless, the only reliable way to get rid of all of the forwarding pointers is by rebuilding the heap table. You can do that by using an ALTER TABLE REBUILD statement or by creating and dropping a clustered index on the table.
The forwarding record/pointer points to the new location of the record, which is known as a forwarded record. This is done as a performance optimization so that all the non-clustered indexes on the heap do not have to be altered with the new location of the heap record.
When the size of the forwarded row is reduced by another update and the data page with forwarding pointer has enough space to accommodate the updated version of the row, SQL Server may move it back to original data page and remove the forwarding pointer row. Nevertheless, the only reliable way to get rid of all of the forwarding pointers is by rebuilding the heap table. You can do that by using an ALTER TABLE REBUILD statement or by creating and dropping a clustered index on the table.
Comments