{"id":809,"date":"2009-08-10T10:53:00","date_gmt":"2009-08-10T10:53:00","guid":{"rendered":"\/blogs\/paul\/post\/Forwarding-and-forwarded-records-and-the-back-pointer-size.aspx"},"modified":"2013-01-01T19:05:28","modified_gmt":"2013-01-02T03:05:28","slug":"forwarding-and-forwarded-records-and-the-back-pointer-size","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/forwarding-and-forwarded-records-and-the-back-pointer-size\/","title":{"rendered":"Forwarding and forwarded records, and the back-pointer size"},"content":{"rendered":"

\nThis is a question that comes up every so often, most recently this morning while teaching a private class (and Kimberly's teaching now): how large is the forwarded record back-pointer? (And I haven't posted anything geeky for a while…) <\/font>\n<\/p>\n

\nIn 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<\/em> record is left in the original location. The forwarding record points to the new location of the record, which is known as a forwarded<\/em> 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. <\/font>\n<\/p>\n

\n

\n\tAs an aside, when a query uses a nonclustered index to satisfy a query, but needs more columns from the table record to fill the result set column list, it must go to the actual data record to retrieve the extra columns. It does this by using the table record locator that is stored in the nonclustered index record. If the table is a heap, the record locator is the physical location of the data record in the heap. <\/font>\n\t<\/p>\n

\n\tIf the table is a clustered index (remember that a table can be organized as a heap OR a clustered index, not both), the record locator is the set of cluster keys of the data record. Both of these record locators are guaranteed to be unique. For a heap record locator, the record lookup (commonly called a bookmark lookup<\/em>) goes directly to the physical location of the record. For a clustered index record locator, the record lookup uses the cluster keys to navigate down through the clustered index to the leaf level. <\/font>\n\t<\/p>\n

\n\tIf a forwarding record occurs in a heap, when the record locator points to that location, the Storage Engine gets there and says Oh, the record isn't really here – it's over there!<\/em> And then it has to do another (potentially physical) I\/O to get to the page with the forwarded record on. This can result in a heap being less efficient that an equivalent clustered index. <\/font>\n\t<\/p>\n

\n\tThere has been lots of discussion about whether it's better to have a heap or a clustered index – generally we recommend a clustered index, but there are special cases where a heap may be fine. This post isn't about that and I won't get drawn into a Comments argument about it. Go bug Kimberly :-) <\/font>\n\t<\/p>\n<\/blockquote>\n

\nBack to the point of the post. So the record has moved to a new location and there's a small record left in the original location which helps bookmark lookups. <\/font>\n<\/p>\n

\n

\n\tOh yeah, one more aside. The Storage Engine, when scanning the heap, will not process the forwarded record UNLESS it has reached it by following the forwarding record. This prevents race conditions where the record could be processed twice – if the forwarding operation occurs during a large table scan. <\/font>\n\t<\/p>\n<\/blockquote>\n

\nOk, 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? <\/font>\n<\/p>\n

\nThe 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. <\/font>\n<\/p>\n

\nThe question becomes- how big is the forwarding record back-pointer? It's not dumped out by DBCC PAGE<\/font> (sorry, when I rewrote DBCC PAGE<\/font> for SQL 2005 I forgot to put that in there). Let's work it out using a script. <\/font>\n<\/p>\n

\nFirst off I'm going to create a database and table to play with. <\/font>\n<\/p>\n

\n

\n\tCREATE DATABASE DBMaint2008;
\n\tGO
\n\tUSE DBMaint2008;
\n\tGO <\/font>\n\t<\/p>\n

\n\tCREATE TABLE DbccPageTest (intCol1  INT IDENTITY,  intCol2  INT, vcharCol VARCHAR (8000),  lobCol  VARCHAR (MAX));
\n\tGO <\/font>\n\t<\/p>\n

\n\tINSERT INTO DbccPageTest VALUES (1, REPLICATE ('Row1', 600), REPLICATE ('Row1Lobs', 1000));
\n\tINSERT INTO DbccPageTest VALUES (2, REPLICATE ('Row2', 600), REPLICATE ('Row2Lobs', 1000));
\n\tGO<\/font> <\/font>\n\t<\/p>\n<\/blockquote>\n

\nUsing DBCC IND ('DBMaint2008', 'DbccPageTest', -1) <\/font>, I find that the data page is page ID (1:154). If I dump out that page using DBCC PAGE<\/font>, I can see both of the records fully contained on the page. <\/font>\n<\/p>\n

\nNow I'm going to update the second row to make it 8000+ bytes – forcing it to move to a new page. <\/font>\n<\/p>\n

\n

\n\tUPDATE DbccPageTest SET vcharCol = REPLICATE ('LongRow2', 1000) WHERE intCol2 = 2;
\n\tGO<\/font> <\/font>\n\t<\/p>\n<\/blockquote>\n

\nAnd now looking at page (1:154) with DBCC PAGE<\/font> again, I can see that the second row has been replaced with: <\/font>\n<\/p>\n

\n

\n\tSlot 1 Offset 0x137a Length 9 <\/font>\n\t<\/p>\n

\n\tRecord Type = FORWARDING_STUB        Record Attributes =
\n\tMemory Dump @0x66F4D37A <\/font>\n\t<\/p>\n

\n\t00000000:   049d0000 00010000 00†††††††††††††††††………
\n\tForwarding to  =  file 1 page 157 slot 0<\/font> <\/font>\n\t<\/p>\n<\/blockquote>\n

\nThe record has been replaced with a forwarding record – pointing to the new location of the record on page (1:157). <\/font>\n<\/p>\n

\nNow DBCC PAGE<\/font> doesn't dump out the back-pointer in the forwarded record – so how can we tell how large it is? I'm going to see how large the record is, and then create a clustered index on the table. This will remove the back-pointer from the record without changing anything else about the record. The difference in size will be the back-pointer size. <\/font>\n<\/p>\n

\nDoing DBCC PAGE<\/font> on page (1:157) I get (partial results): <\/font>\n<\/p>\n

\n

\n\tSlot 0 Offset 0x60 Length 8057 <\/font>\n\t<\/p>\n

\n\tRecord Type = FORWARDED_RECORD       Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS<\/font> <\/font>\n\t<\/p>\n<\/blockquote>\n

\nNow creating a clustered index and then dumping the page with the second row on it (left as an exercise for the reader :-)): <\/font>\n<\/p>\n

\n

\n\tCREATE CLUSTERED INDEX Dbcc_CL ON DbccPageTest (intCol1);
\n\tGO<\/p>\n

\t<figure out which page to look at><\/font>\n\t<\/p>\n

\n\tDBCC PAGE ('DBMaint2008', 1, 169, 3);
\n\tGO<\/font>\n\t<\/p>\n

\n\t<partial results>
\n\tSlot 0 Offset 0x60 Length 8047<\/font>\n\t<\/p>\n

\n\tRecord Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS<\/font>
\n\t<\/font>\n\t<\/p>\n<\/blockquote>\n

\n[Edit 062210 – added a more in-depth explanation] <\/font>\n<\/p>\n

\nThe difference in size is 10 bytes, which is the size of the back-pointer. This isn't entirely obvious, but here's the explanation for the size change:<\/font>\n<\/p>\n