SQL Server Row Internals

Row Storage

A Row is stored on a Page which is 8192 Bytes, and this is broken down in two key areas when considering physical storage. Firstly on all pages there is a 96 Byte Header. In addition to this we also need to store the Row structure as well as its location in the remainder of the Page.

The location of a Row is stored at the end of the Page working backwards using 2 Bytes per Row. These 2 Byte pairs are generally called the Slot Array. I have seen a few articles/explanations of the Slot Array listing this as 36 Bytes which is incorrect, unless of course you are storing 18 Rows.

Now as the Page Header is always 96 Bytes, if you were to create a table and insert a Row into it; then the earliest point that it could be placed is immediately after the Page Header.

This location immediately after the Page Header would mean that the final 2 Bytes of the Page (Byte 8190 and Byte 8191) would be 60 00 which when reversed becomes 0x60 or 96 as an integer.

If another Row is added then Byte 8188 and Byte 8189 would be used to store the location of this second Row. The order of these 2 Byte pairs within the Slot Array is determined by how they are indexed, not necessarily how they are stored on the Page. That is to say a Row stored towards the front of the Page may or may not appear towards the beginning of the Slot Array which grows from the end of the Page towards the start.

Here is a representation of how a row is stored internally within SQL server using the standard structure.

Desc Bytes
Page Header 96
Status Byte A 1
Status Byte B 1
Null Bitmap Offset 2
Fixed Length Columns X
Column Count 2
Null Bitmap(At least 1 Byte) 1(+1 per 8 Columns)
Variable Length Column Count 2
Variable Length Offset Array 2 per column*
Uniqueifier 4 (if needed)
Variable Length Columns X
Row Version Contains timestamp + pointer to original record 14 bytes
Slot Array 2 bytes per Record/Row/Slot

Now you will see that the fixed length columns are stored towards the start of the structure and then the variable length columns. *I have highlighted that the variable length column array is 2 Bytes per column, however if a column is null then the entry in the Slot Array is not needed unless it has a non null column to its right.

[Location A][Location B][Location C] AAA,BBB,CCC
[Location A][Location B][Location C] AAA,NULL,CCC
[Location A][Location B][Location C] AAA,BBB,NULL

In the first 2 examples all 3 location pointers are needed, however the 3rd example does not require the 3rd location pointer. This raises the interesting point that column order does matter, and that you should keep the variable length columns that have a high probability to  remain NULL after variable lengths columns that are not.

Smallest Row Size

The smallest row size using the above mentioned format would be for a CHAR(1)
+1 Status Byte A
+1 Status Byte B
+2 Null Bitmap Offset
+1 Char(1)
+2 Column Count
+1 Null Bitmap (This is always stored regardless)

This would work out as 8 Bytes for the Row plus 2 Bytes per the Row location in the Slot Array. Now it is worth noting however that this is not the smallest Row size as internally this would be padded by 1 additional Byte. If you store a CHAR(2) then no padding will take place. The reason this happens is because of an optimization which is best explained as follows:

The reason that the data record is 9 bytes is that for heaps we need to guarantee that the RID that is used to uniquely identify a row in a heap is never changed. To do this, SQL Server needs to be able to store at least a forwarding pointer on a page. A forwarding pointer is 9 bytes (1 byte header, and 8 byte RID of the actual record.

For B-Trees, the 9 byte limit is used as well. The reason here is that SQL Server quickly likes to convert a B-Tree to a heap without having to touch all the individual records. This is only possible if we can guarantee that the records are at least 9 bytes long.

Marcel van der Holst – MSFT

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/4e35ba31-748b-4a3b-947d-b56006b47ab5

So although the Row is 8 Bytes it is stored using 9 Bytes. The smallest optimally stored Row therefore is a fixed data type of 2 Bytes.

Most Rows per Page

On that note then if we say that we have a 9 Byte Row Structure + 2 Bytes for the Slot Array to store in the 8096 Bytes (8192-96 Page Header) we can work out the most Rows per Page as follows:

8096/11 = 736 Rows

Acknowledgement

This post builds on information gathered from research, but the factual knowledge was gathered from Paul Randall blog posts.

http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-page/

http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-record/

6 thoughts on “SQL Server Row Internals

  1. Hello Jon,

    If a slot array has a 2 byte pointer for each data row in a page and a page accommodates 8060 bytes of data so that would leave 36 bytes in a page.
    Does that mean that the slot array can only cover 18 records on the page ?

    • The slot array is not limited to 36 bytes. It grows 2 bytes per row from the end of the page. The most rows per page for a 9 byte row is 736, therefore the slot array would have grown to 736×2 = 1472.

      • Thank you very much for your reply.But still I am a bit perplexed.
        So incase if my row data is 1 byte that would mean 8096 rows can be accommodated on the page but the to track these 8096 rows the slot array would need 8096 * 2 =16192 bytes ? That number is twice the page size.

        • The 8096 bytes stores the data and the slot array. The critical point is that the row data 9 bytes + slot array 2 bytes = 11 bytes per row. 8096/(9+2)=736 rows. The row data + slot array will never be larger than 8096. 9 is min row size.

Leave a Reply

Your email address will not be published. Required fields are marked *