Sql-server – 512 Bytes are not being used from SQL Server’s 8 KByte data page

data-pagesindexsql servert-sql

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, first 8 KB page is for Root IAM page, the second one is for leaf data storage page which is 8KB with occupation of ~ 7.5 KB, now when I insert a new row with 256 Byte:

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

it is not stored in the same page although it have a space of 256 byte (7680 b + 256 = 7936 which is still smaller than 8KB), a new data page is created, but that new row could be fit on the same old page, why does SQL Server create a new page when it could save space and searching time buy inserting it in the existing page?

Note: the same thing is happening in heap index.

Best Answer

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.