CODEWITHSUNDEEP
sqlsql-server
TomerBu
TomerBu

Reputation: 1503

Input rows to a Page in a Heap table Does not match the page size of 8060 bytes

I'm working through an example in a book (Exam 70-461: Querying Microsoft SQL Server 2012 by Ben Gan) which does the following:

USE tempdb;
GO

CREATE TABLE TestTable
(
id INT NOT NULL,
col1 CHAR(36) NOT NULL,
col2 CHAR(216) NOT NULL --Each row is 256 bytes (int is 4 bytes long)
);

Note that each row should contain 256 bytes as per the column definitions.

The page size is 8kb ->8192 bytes. We need to subtract the Header size of the page which is 96bytes And also the row offsets array which is 36 bytes. which leaves us with 8060 bytes per page.

My expectation is that the page will hold 8060/256 = 31.5 rows.->31 rows.

Let's insert 30 rows like so:

DECLARE @i AS int = 1;
WHILE @i <= 30
BEGIN
    INSERT INTO dbo.TestTable
    (id, col1, col2)
    VALUES
    (@i, 'a', 'b');

    SET @i = @i + 1;
END;

All the data is still contained in a single page according to the following query:

SELECT index_type_desc, page_count,
       record_count,
       avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'tempdb'),
                                    OBJECT_ID(N'dbo.TestTable'),
                                    NULL,
                                    NULL,
                                    'Detailed');

Here is the output:

index_type_desc page_count  record_count    avg_page_space_used_in_percent
HEAP                 1           30                98.1961947121324

Then when I insert the 31 row like so:

INSERT INTO dbo.TestTable
(id, col1, col2)
VALUES
(31, 'a', 'b');

I get this output:

index_type_desc page_count  record_count    avg_page_space_used_in_percent
HEAP                 2           31                50.7227575982209

Why do I get another page if the record should still be stored in the previous Page? What am I missing here in order to analyse that correctly?

Thank You.

Upvotes: 3

Views: 58

Answers (1)

Luaan
Luaan

Reputation: 63772

Each row also has a header. In your case, this should be 8 bytes. 8060 / 264 = ~30.5, so only 30 rows fit on one page :)

All in all, for every row, you need to add:

  • 2 bytes of status bits
  • 2 bytes for fixed-data length
  • The actual fixed data (256 bytes in your case)
  • 2 bytes for null-bitmap header
  • The null-bitmap header (0 bytes in your case)
  • 2 bytes for variable-data column count
  • 2 bytes for each variable length column offset (0 bytes in your case)
  • The actual variable data (0 bytes in your case)

Upvotes: 5

Related Questions