Sql-server – Heap table insert

sql serversql-server-2019

I am trying to understand the behaviour of SQL Server when it INSERT record into a Heap table. According to about sql Server blog post, paragraph 2 "SQL Server would not put a new row to the page if its size exceeds 20 percent (8,060 bytes * 0.2 = 1,612 bytes) of the page size."

If I understand the statement correctly then if the table had a record of size 2025 byte the second record should go on another page. However, my test is not agreeing with that

SETUP SCRIPT

Environment: SQL 2019 x64 Developer edition.

create table dbo.Heap(id INT, Val varchar(8000) not null);
GO
insert into dbo.heap(ID, val)  values (1,replicate('*',2010));
insert into dbo.heap(ID, val)  values (2,replicate('*',2010));
insert into dbo.heap(ID, val)  values (3,REPLICATE('*',2010));
insert into dbo.heap(ID, val)  values (4,REPLICATE('*',2010));

And run following code

SELECT PARSENAME(REPLACE(REPLACE(REPLACE(CAST(sys.fn_PhysLocFormatter (%%physloc%%) AS varchar),')',''),'(',''),':','.'),2)PageNo,
sys.fn_PhysLocFormatter (%%physloc%%) AS [Location],* FROM heap ORDER BY [ID]

The above SELECT returns the following result, which doesn't agree with 20% page size statement.

enter image description here

I tried reviewing the DBCC PAGE result after insert but the DBCC PAGE result doesn't add up the size of the inserted record.

SETUP Script

DROP TABLE IF EXISTS heap
go
create table dbo.Heap(id INT, Val varchar(8000) not null);
GO
insert into dbo.heap(ID, val)  values (1,replicate('*',2010));
go
dbcc traceon(3604);
dbcc page
(
    'demodb' -- Database name
    ,1 -- File ID
    ,416 -- Page ID
    ,1 -- Output mode: 3 - display page header and row details
);

The records size was only 25% of the data page however PFS shows 50% full why?
enter image description here
Issue the second insert and review the PAGE

insert into dbo.heap(ID, val)  values (2,replicate('*',2010));
dbcc page
(
    'demodb' -- Database name
    ,1 -- File ID
    ,416 -- Page ID
    ,1 -- Output mode: 3 - display page header and row details
);

enter image description here

Best Answer

Let the person who wrote this blog post clarify :)

Your quote is a bit out of context. The full paragraph is:

For example, if a data page stores 4,100 bytes of data, and, as result, has 3,960 bytes of free space available, PFS would indicate that the page is 51-80 percent full. SQL Server would not put a new row to the page if its size exceeds 20 percent (8,060 bytes * 0.2 = 1,612 bytes) of the page size.

Basically, it means that if you have 80_PCT_FULL flag on the page, SQL Server would expect that page may be up to 80 percent populated and have only 1,612 bytes available.

Your tests confirmed that:

  1. First INSERT came to the page 416. The page had been marked as 50_PCT_FULL, which means it may have up to 4,030 bytes populated.
  2. Second INSERT of 2,025 bytes fit into the same page. However, it pushed the usage over 50% and page had been marked as 80_PCT_FULL, which means it may be up to 6,448 bytes populated and only 1,612 bytes were guaranteed free.
  3. Third insert went to page 417 because you tried to insert more than 1,612 bytes

Hope this clarifies the situation.

Related Question