GilaMonster (9/23/2012)
opc.three (9/23/2012)
In a heap, when an existing row is updated and that row no longer fits on the page where it lives a forwarding record is created to hold that row and a pointer to that new record is placed in the old page. If the same record is updated again later and the same process occurs again you now have to visit 3 pages to get to that row. Long chains of forwarded record pointers can affect performance and there is no great way to alleviate them.Just need to correct one point...
There will never be chains of forwarding pointers. If a forwarded row moves again, the original forwarding pointer is updated to point to the new location, there's not a second or third ... level of redirection. Forwarding pointers are bad enough already without multiple levels of forwarding.
Thanks for the pointing that out. It's good to know it doesn't lengthen the chain. All the docs I have read thus far would simply say something to the effect of "new record needed, pointer is added." I just found this post that plainly says that in the beginning but unquivocally confirms what you said later:
Forwarding and forwarded records, and the back-pointer size by Paul Randal
In a heap it is possible to get forwarding and forwarded records. They occur when a record in a heap expands such that it no longer fits on the page it currently resides on. In this case, the record is moved to a new page, and a small forwarding record is left in the original location. The forwarding record 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 nonclustered indexes on the heap do not have to be altered with the new location of the heap record.
The above makes it sound like chains can result in the case of a second split. Then later:
Ok, really back to the point of the post. What happens if the original record grows again and has to move again? Does it leave ANOTHER forwarding record when it moves to the second new location - creating a chain of forwarding records?
The answer is no. The *original* forwarding record is updated with the new location of the forwarded record. This can only be done if the forwarded record points *back* to the forwarding record - which it does.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato