13

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.

migrated from stackoverflow.com Aug 14 '16 at 13:24

This question came from our site for professional and enthusiast programmers.

9

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 link that you provided helped me to better visuals the page structure , I was searching something similar to it but was unable to find it , Thax – Alphas Supremum Aug 14 '16 at 19:31
11

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.

  • 8060 bytes per page are available for user data, to be precise. The OP's data is still below that. – Roger Wolf Aug 14 '16 at 14:25
  • Version info isn't there, otherwise 30 rows would take 8310 bytes. The rest seems to be correct. – Roger Wolf Aug 14 '16 at 15:08
  • @RogerWolf Yes, "version info" is there. And so yes, 30 rows does require 8310 bytes. Which is why those 30 rows did not, in fact, all fit into 1 page as the OP is being lead to believe by whatever test proc the OP is using. But that test proc is wrong. Only 29 rows fit on the page. I have confirmed this (using SQL Server 2012 even). – Solomon Rutzky Aug 14 '16 at 15:20
  • have you tried to run your test on a database which is not RCSI-enabled / tempdb? I was able to reproduce the exact numbers provided by OP. – Roger Wolf Aug 14 '16 at 15:22
  • @RogerWolf The DB I am using is not RCSI-enabled, but it is set to ALLOW_SNAPSHOT_ISOLATION. I also just tried in tempdb and saw that the "version info" is not there, hence 30 rows do fit. I will update to add the new info. – Solomon Rutzky Aug 14 '16 at 15:37
3

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.

  • 1
    No. The uniquifier is only added to duplicate key rows. The first row of each unique key value does not include the extra 4 byte uniquifier. – Solomon Rutzky Aug 14 '16 at 14:38
  • @srutzky, apparently you are right. Never thought that SQL Server would allow variable width keys. This is ugly. Efficient, yes, but ugly. – Roger Wolf Aug 14 '16 at 14:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.