4

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

6
  • 50_PCT_FULL means "1 to 50%" and 80_PCT_FULL means "51 to 80% full" see here
    – i-one
    Apr 30 '20 at 17:53
  • I was just looking at this a few days ago. For similar reasons the insert here only ends up inserting 700 rows on a page rather than 736. Apr 30 '20 at 19:53
  • 5
    Also, I think that meaning of the entire paragraph in that blog post is: if PFS indicates that page is 51-80% full, then SQL Server would not put a new row to the page, if its size exceeds 20%. It seems consistent with the result of your first SELECT.
    – i-one
    Apr 30 '20 at 19:55
  • 2
    And note that the behavior changes when you don't insert row-by-row. Apr 30 '20 at 22:42
  • @DavidBrowne-Microsoft.. Wow...do you mean batch insert , using TABLOCKX.. I am going to test it.. please share reading material if there are any.. Apr 30 '20 at 22:49
6

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.

1
  • Thanks Dmitri and all others commented on the post Apr 30 '20 at 22:18

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.