Mark S. Rasmussen
Jun 07

A forwarded record occurs whenever a record in a heap increases in size and it no longer fits on the page. Instead of causing a page split, as would happen had the table not been a heap, the record is moved onto another with enough free space, or onto a newly allocated page. Forwarded records can wreak havoc to your performance due to fragmentation, but I’ll leave not cover that here as many other more skilled people have already done so.

Test setup

As a test table we’ll use a simple table with three wide records, taking up almost a full page of data.

-- Create test table
CREATE TABLE ForwardedRecordTest
	ID int identity,
	Data varchar(8000)

-- Insert dummy data
	ForwardedRecordTest (Data)
	(REPLICATE('a', 2000)),
	(REPLICATE('b', 2000)),
	(REPLICATE('c', 2000))

Firing up DBCC IND shows us a single IAM page tracking a single data page:

DBCC IND (Test, ForwardedRecordTest, -1)

Now, to force a forwarded record, we’ll update one of the columns so it’ll no longer fit on the page with the other records:

	Data = REPLICATE('b', 5000)
	Data = REPLICATE('b', 2000)

Invoking DBCC IND again confirms that a new page has been allocated to our table:


By using DBCC PAGE we can take a look at the forwarded recorded, or at least what’s left of it on the original page 114:

DBCC PAGE (Test, 1, 114, 3)

Identifying a forwarding stub is done by looking at the first status byte of the record. Specifically, bits 1-3 will tell us the record type:

Type = (RecordType)((Convert.ToByte(bits[1]) << 2) + (Convert.ToByte(bits[2]) << 1) + Convert.ToByte(bits[3]));

With Type being one of the valid SQL Server record types:

public enum RecordType : byte
	Primary = 0,
	Forwarded = 1,
	ForwardingStub = 2,
	Index = 3,
	BlobFragment = 4,
	GhostIndex = 5,
	GhostData = 6,
	GhostVersion = 7

While other record types will have two status bytes, a forwarding stub only has a single status byte. Thus, if we identify the record to be a forwarding stub, we know that the next 8 bytes will be a page pointer.

Parsing the forwarding stub

Once we know the format, parsing a forwarding stub record is straight forward.

The first byte has a value of 0x04, or 0b100 in binary. Looking at bits 1-3 we get 0b10 or decimal 2 – which matches RecordType.ForwardingStub.

Looking at the next 8 bytes we have 76000000 0001 0000. I’ve divided them into three groups – in order they contain the page ID, the file ID and finally the slot number.

76000000 byte swapped = 0x76 = 118 in decimal.

0001 byte swapped = 0x01 = 1 in decimal.

0000 byte swapped = 0x00 = 0 in decimal.

Thus we have the position of the forwarded record: (1:118:0).

Mark S. Rasmussen
I'm the CTO at iPaper where I cuddle with databases, mold code and maintain the overall technical & team responsibility. I'm an avid speaker at user groups & conferences. I love life, motorcycles, photography and all things technical. Say hi on Twitter, write me an email or look me up on LinkedIn.