What Other Information Is Stored in The Page Header

A SQL Server database page is defined as to be 8192 bytes in size.
There is some header information which is said to be 96 bytes in size.

And if you have ever tried to create a table with more than 8053 bytes of column definitions then you will hit the error message:

Creating or altering table 'Generated_Data_GUID' failed because the 
minimum row size would be 8061, including 7 bytes of internal overhead. 
This exceeds the maximum allowable table row size of 8060 bytes.

Following is an example table DDL:

CREATE TABLE [dbo].[Generated_Data_GUID](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [GUID] [uniqueidentifier] NOT NULL,
    [SEQGUID] [uniqueidentifier] NOT NULL,
    [Data1] [char](4000) NULL,
    [Data2] [char](4000) NULL,
    [Data3] [char](9) NULL,
    [EntryDate] [datetime2](7) NULL
) ON [PRIMARY]

With the above DDL if I change the column definition for column Data3 to be char(10), then I will hit the error message.

The byte sizes for each column type are as follows:

int               :  4 bytes
uniqueidentifiere : 16 bytes
char(n)           :  n bytes
datetime2(n)      :  6 bytes if n  4

If we do some simple maths, then we end up with the following calculation:

Page Size         : 8192 bytes
                   -----------
Header            :   96 bytes - 
Internal Overhead :    7 bytes - 
Max Size          : 8053 bytes - 
                   -----------
Missing Data      :   36 bytes
                   ===========

Question

What do these 36 bytes contain?

Reference Material

• Pages and Extents Architecture Guide (Microsoft Docs)

• Inside the Storage Engine: Anatomy of a page (SQLSkills.com)

Paul Randal actually answers this exact question in the comments on the blog post you linked to:

The 8060 bytes is the maximum size of one record, not the amount of data space on the page – 8096 bytes.

For a maximum-sized record of 8060 bytes, add two bytes for the slot array entry, 10 bytes for a possible heap forwarded-record backpointer, 14-bytes for a possible versioning tag, and that’s 26 bytes used. The other 10 bytes are for possible future use.

If there are more than one record on the page, all 8096 bytes of data space can be used.

So, in answer to the question in the body of your post:

What do these 36 bytes contain?

The "extra" 36 bytes in a page are used as follows:

• 10 bytes reserved for heap forward-record backpointer

• 14 bytes reserved for versioning tag pointing to the version store in tempdb

• 12 bytes available for the slot array

• in the case you outlined of one large record, there are 10 bytes of "wasted" space here. There is room for 5 more 2-byte slot array entries

Just to confirm that the table defined in the question is, in fact, 8060 bytes wide, let's have a full repro.

First we'll set up the database and the table, and insert one row into it. I'm adding a clustered index because heaps are the worst.

USE master;
GO

CREATE DATABASE PageJunk;
GO

USE PageJunk;
GO

CREATE TABLE [dbo].[Generated_Data_GUID](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [GUID] [uniqueidentifier] NOT NULL,
    [SEQGUID] [uniqueidentifier] NOT NULL,
    [Data1] [char](4000) NULL,
    [Data2] [char](4000) NULL,
    [Data3] [char](9) NULL,
    [EntryDate] [datetime2](7) NULL
) ON [PRIMARY];
GO

CREATE CLUSTERED INDEX PK_Generated_Data_GUID 
    ON Generated_Data_GUID (ID);
GO

INSERT INTO [dbo].[Generated_Data_GUID]
    ([GUID], SEQGUID, Data1, Data2, Data3, EntryDate)
VALUES
    (NEWID(), NEWID(), REPLICATE('1', 4000), REPLICATE('2', 4000), REPLICATE('3', 9), '2018-01-01');
GO

We can see all the pages allocated the index by running this DBCC command:

DBCC IND ('PageJunk', 'Generated_Data_GUID', 1);
GO

The one with a page type of 1 is the index page (page ID 336). We can dump all kinds of information about that page with this other DBCC command:

DBCC TRACEON (3604); -- needed for the next one to work
GO

DBCC PAGE (PageJunk, 1, 336, 3);
GO

Here are some important snippets from the output of that command. From the HEADER section:

m_freeCnt = 34

This means there are 34 bytes of free space on the page. That's the 36 you outlined in your original post, minus the 2 bytes for the slot array entry. Speaking of which:

m_slotCnt = 1

This means there is only one record on this page.

Now, down in the record section:

Slot 0 Offset 0x60 Length 8060

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP    Record Size = 8060

This indicates that the single record stored on this page is 8060 bytes (which is the sum of all the data type storage sizes plus the 7 bytes per-record overhead).

So we do have a fully sized, 8060 byte record on this page. However, we can still squeeze an additional 34 bytes onto this page if we try harder.

For instance, I could create a table that's 2015 bytes wide. Each row would then take up 2015 + 7 (internal overhead) + 2 (slot array) = 2024 bytes in the page. So four rows should add up to 8096 bytes, exactly filling the space left over after the 96 byte header. Let's try it in the same database:

CREATE TABLE [dbo].[QuarterPage](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [GUID] [uniqueidentifier] NOT NULL,
    [SEQGUID] [uniqueidentifier] NOT NULL,
    [Data1] [char](981) NULL,
    [Data2] [char](981) NULL,
    [Data3] [char](9) NULL,
    [EntryDate] [datetime2](7) NULL
) ON [PRIMARY];
GO

CREATE CLUSTERED INDEX PK_QuarterPage
    ON QuarterPage (ID);
GO

INSERT INTO [dbo].[QuarterPage]
    ([GUID], SEQGUID, Data1, Data2, Data3, EntryDate)
VALUES
    (NEWID(), NEWID(), REPLICATE('1', 981), REPLICATE('2', 981), REPLICATE('3', 9), '2018-01-01');
GO 4

Now we find our page, and there is only one as expected:

DBCC IND ('PageJunk', 'QuarterPage', 1);
GO

So now we want to get info on page 352:

DBCC PAGE (PageJunk, 1, 352, 3);
GO

And here's the good stuff:

m_slotCnt = 4
m_freeCnt = 0

No free space! This page is slap full with our 4 rows.

StackExchange Database Administrators Q#211762, answer score: 11