How does SQL decide to add new data page

Question

I am trying to understand how SQL allots a new data pages as when we insert some new records into a table.

I have create a sample table called Employee in a fresh database by -

Create Database TestDb
GO
Use TestDb
GO
Create table Employee (
EmployeeName char(1000))   GO
Insert into Employee values ('Employee1')

DBCC IND('TestDb','dbo.Employee',-1)

After running DBCC, I saw 2 pages. One was IAM page (PageType =10 ) and other was Data Page (PageType = 1) which holds actual data.

Later I verified the actual content of data page by using DBCC Page

DBCC TRACEON(3604)
DBCC PAGE('TestDb',1,298,3)

I saw how SQL calculated the m_freecnt bytes count -

= 1007 bytes RecordSize + 96 bytes Header + 2 bytes offset = 1105 Bytes

i.e. 8K Page = 8192 bytes = 8192 - 1105 = 7087 Bytes Free.

Now I go on adding records to this table in order to understand how many records this page will accommodate and when SQL will allot a new page keeping m_FreeCnt in mind.

(Record Size = 1007 & Offset Bytes = 2 )

Added 2nd record -

Insert into Employee values ('Employee2') GO

Last Free Count = 7087 i.e. 7087 - 1007 - 2 = 6087 => m_FreeCnt = 6078

Insert into Employee values ('Employee3') GO

Last Free Count = 6078 i.e. 6078 - 1007 - 2 = 5069 => m_FreeCnt = 5069

Insert into Employee values ('Employee4') GO

Last Free Count = 5069 i.e. 5069 - 1007 - 2 = 4060 => m_FreeCnt = 4060

Insert into Employee values ('Employee5') GO

Last Free Count = 4060 i.e. 4060 - 1007 - 2 = 3051 => m_FreeCnt = 3051

Insert into Employee values ('Employee6') GO

Last Free Count = 3051 i.e. 3051 - 1007 - 2 = 2042 => m_FreeCnt = 2042

Insert into Employee values ('Employee7') GO

Last Free Count = 2042 i.e. 2042 - 1007 - 2 = 1033 => m_FreeCnt = 1033

Till now everything works normal. Now we have 1033 bytes left in this data page. As soon as I add 8th record, ideally it should not create another page because the number of bytes free are 1033 bytes which is sufficient enough to accommodate 8th record (1009 bytes are enough). However, SQL does create a new Date page to save this 8th record.

I inserted 8th record and ran DBCC IND to check this -

Insert into Employee values ('Employee7') GO

DBCC IND('TestDb','dbo.Employee',-1)

Now it has created a new Data Page with PageNumber = 300.

I did not understand this part. Whether SQL keeps some bytes reserved except [header (96) + Data part + offset per page 2 bytes] this?

You can try running above queries and let me know If I am missing out anything here? Or whether we shouldn't bother about these memory details of SQL?

Thanks.

Answer 1

Just re-phrase another answer to make it clear:
As soon as you reach 81% of your page filled, PFS record for that page will change first 2 bits to be 11, which will mean that "Page is 95% full" and SQL Will not allow inserting any more records even though there is plenty of space (more than 5% are free).

There are two ways to fight that problem:
Easy: Create clustered index - then you will be able to set fillfactor as 100% if needed.
Difficult: If you have on your page more than 19% of free space you can insert more than one record using ONE INSERT statement to fill the page 100%.
See example:

CREATE TABLE TestTable(F0 SMALLINT);
GO
INSERT INTO TestTable (F0) 
SELECT TOP 699 1 FROM sys.messages;
GO
BEGIN TRAN 
  INSERT INTO TestTable (F0) 
  SELECT TOP 37 2 FROM sys.messages;
  SELECT 
    (SELECT COUNT(*) FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('TestTable'), NULL, NULL, 'DETAILED') WHERE Page_type = 1) AS Pages,
    (SELECT COUNT(*) FROM TestTable) AS "Count";
ROLLBACK
GO
BEGIN TRAN 
  INSERT INTO TestTable (F0) VALUES (3)
  GO 2
  SELECT 
    (SELECT COUNT(*) FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('TestTable'), NULL, NULL, 'DETAILED') WHERE Page_type = 1) AS Pages,
    (SELECT COUNT(*) FROM TestTable) AS "Count";
ROLLBACK
GO
DROP TABLE TestTable
GO

After you reach 699 records you can insert 37 new records at once or only one record if you insert on record-by-record basis.