512 Bytes are not being used from SQL Server's 8 KByte data page

I have create the following table:

CREATE TABLE dbo.TestStructure
(
    id INT NOT NULL,
    filler1 CHAR(36) NOT NULL,
    filler2 CHAR(216) NOT NULL
);

and then created a clustered index:

CREATE CLUSTERED INDEX idx_cl_id 
ON dbo.TestStructure(id);

Next I populated it with 30 rows each size is 256 byte (based on table declaration):

DECLARE @i AS int = 0;

WHILE @i < 30
BEGIN
    SET @i = @i + 1;

    INSERT INTO dbo.TestStructure (id, filler1, filler2)
    VALUES (@i, 'a', 'b');
END;

Now based on information I read in "Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012 (Itzik Ben-Gan)" book:


SQL Server internally organizes data in a data file in pages. A page
is an 8 KB unit and belongs to a single object; for example, to a
table or an index. A page is the smallest unit of reading and writing.
Pages are further organized into extents. An extent consists of eight
consecutive pages. Pages from an extent can belong to a single object
or to multiple objects. If the pages belong to multiple objects, then
the extent is called a mixed extent; if the pages belong to a single
object, then the extent is called a uniform extent. SQL Server stores
the first eight pages of an object in mixed extents. When an object
exceeds eight pages, SQL Server allocates additional uniform extents
for this object. With this organization, small objects waste less
space and big objects are less fragmented.

So here I have the first mixed extent 8KB page, populated with 7680 bytes (I have inserted 30 times 256 byte size row, so 30 * 256 = 7680), to check the size I have run size check proc - it returns the following result

index_type_desc: CLUSTERED INDEX
index_depth: 1
index_level: 0 
page_count: 1 
record_count: 30 
avg_page_space_used_in_percent: 98.1961947121324
name : TestStructure        
rows : 30   
reserved :  16 KB
data : 8 KB 
index_size : 8 KB       
unused :    0 KB

So 16 KB are reserved for the table, fi

While it is true that SQL Server uses 8k (8192 bytes) data pages to store 1 or more rows, each data page has some overhead (96 bytes), and each row has some overhead (at least 9 bytes). The 8192 bytes is not purely data.

For a more detailed examination of how this works, please see my answer to the following DBA.SE question:

SUM of DATALENGTHs not matching table size from sys.allocation_units

Using the information in that linked answer, we can get a clearer picture of the actual row size:

• Row Header = 4 bytes

• Number of Columns = 2 bytes

• NULL Bitmap = 1 byte

• Version Info** = 14 bytes (optional, see footnote)

• Total Per Row Overhead (excluding Slot Array) = 7 bytes minimum, or 21 bytes if version info is present

• Total Actual Row Size = 263 minimum (256 data + 7 overhead), or 277 bytes (256 data + 21 overhead) if version info is present

• Adding in the Slot Array, the total space taken per row is actually either 265 bytes (without version info) or 279 bytes (with version info).

Using DBCC PAGE confirms my calculation by showing: Record Size 263 (for tempdb), and Record Size 277 (for a database that is set to ALLOW_SNAPSHOT_ISOLATION ON).

Now, with 30 rows, that is:

-
WITHOUT version info

30 * 263 would give us 7890 bytes. Then add in the 96 bytes of page header for 7986 bytes used. Finally, add in the 60 bytes (2 per row) of the slot array for a total of 8046 bytes used on the page, and 146 remaining. Using DBCC PAGE confirms my calculation by showing:

• m_slotCnt 30 (i.e. number of rows)

• m_freeCnt 146 (i.e. number of bytes left on the page)

• m_freeData 7986 (i.e. data + page header -- 7890 + 96 -- slot array is not factored into the "used" bytes calculation)

-
WITH version info

30 277 bytes for a total of 8310 bytes. But 8310 is over 8192, and that didn't even account for the 96 byte page header nor the 2 byte per row slot array (30 2 = 60 bytes) which should give us only 8036 usable bytes for the rows.

BUT, what about 29 rows? That would give us 8033 bytes of data (29 277) + 96 bytes for page header + 58 bytes for slot array (29 2) equaling 8187 bytes. And that would leave the page with 5 bytes remaining (8192 - 8187; unusable, of course). Using DBCC PAGE confirms my calculation by showing:

• m_slotCnt 29 (i.e. number of rows)

• m_freeCnt 5 (i.e. number of bytes left on the page)

• m_freeData 8129 (i.e. data + page header -- 8033 + 96 -- slot array is not factored into the "used" bytes calculation)

Regarding Heaps

Heaps fill data pages slightly differently. They maintain a very rough estimate of the amount of space left on the page. When looking at the DBCC output, look at the row for: PAGE HEADER: Allocation Status PFS (1:1). You will see the VALUE showing something along the lines of 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL (when I looked at the Clustered table) or 0x64 MIXED_EXT ALLOCATED 100_PCT_FULL when looking at the Heap table. This is evaluated per Transaction, so doing individual inserts such as the test being performed here could show different results between Clustered and Heap tables. Doing a single DML operation for all 30 rows, however, will fill the heap as expected.

However, none of these details regarding Heaps directly affect this particular test since both versions of the table fit 30 rows with only 146 bytes remaining. That isn't enough space for another row, regardless of Clustered or Heap.

Please keep in mind that this test is rather simple. Calculating the actual size of a row can get very complicated depending on various factors, such as: SPARSE, Data Compression, LOB data, etc.

To see the details of the data page, use the following query:

DECLARE @PageID INT,
        @FileID INT,
        @DatabaseID SMALLINT = DB_ID();

SELECT  @FileID = alloc.[allocated_page_file_id],
        @PageID = alloc.[allocated_page_page_id]
FROM    sys.dm_db_database_page_allocations(@DatabaseID,
                            OBJECT_ID(N'dbo.TestStructure'), 1, NULL, 'DETAILED') alloc
WHERE   alloc.[previous_page_page_id] IS NULL -- first data page
AND     alloc.[page_type] = 1; -- DATA_PAGE

DBCC PAGE(@DatabaseID, @FileID, @PageID, 3) WITH TABLERESULTS;

** The 14-byte "version info" value will be present if your database is set to either ALLOW_SNAPSHOT_ISOLATION ON or READ_COMMITTED_SNAPSHOT ON.

StackExchange Database Administrators Q#146750, answer score: 11