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

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:

1. Row Header = 4 bytes
2. Number of Columns = 2 bytes
3. NULL Bitmap = 1 byte
4. Version Info^** = 14 bytes (optional, see footnote)
5. Total Per Row Overhead (excluding Slot Array) = 7 bytes minimum, or 21 bytes if version info is present
6. Total Actual Row Size = 263 minimum (256 data + 7 overhead), or 277 bytes (256 data + 21 overhead) if version info is present
7. 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.

Your data rows are not 256 bytes. Each one is more like 263 bytes. A data row of purely fixed-length data types has additional overhead due to the structure of a data row in SQL Server. Take a look at this site and read about how a data row is made up. http://aboutsqlserver.com/2013/10/15/sql-server-storage-engine-data-pages-and-data-rows/

So in your example, you have a data row that has 256bytes, add 2 bytes for status bits, 2 bytes for number of columns, 2 bytes for data length, and another 1 or so for null bitmap. That is 263 * 30 = 7,890bytes. Add another 263 and you are over the 8kb page limit which would force another page to be created.

The actual structure of the data page is quite complex. While it is generally stated that 8060 bytes per page are available for user data, there is some additional overhead not counted anywhere which results in this behaviour.

However, you might have noticed that SQL Server actually gives you a hint that the 31st row will not fit into the page. For the next row to fit onto the same page, the avg_page_space_used_in_percent value should be below 100 % - (100 / 31) = 96.774194, and in your case it's way above that.

P.S. I believe I saw a detailed, down to the byte explanation of the data page structure in one of the "SQL Server Internals" books by Kalen Delaney, but it was almost 10 years ago so please excuse me for not remembering any more details. Besides, page structure tends to change from version to version.