sql server - How does SQL decide to add new data page -

i trying understand how sql allots new data pages when insert new records table.

i have create sample table called employee in fresh database -

create database testdb go use testdb go create table employee ( employeename char(1000))   go insert employee values ('employee1')  dbcc ind('testdb','dbo.employee',-1) 

after running dbcc, saw 2 pages. 1 iam page (pagetype =10 ) , other data page (pagetype = 1) holds actual data.

later verified actual content of data page using dbcc page

dbcc traceon(3604) dbcc page('testdb',1,298,3) 

i saw how sql calculated 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 go on adding records table in order understand how many records page accommodate , when sql allot new page keeping m_freecnt in mind.

(record size = 1007 & offset bytes = 2 )

added 2nd record -

insert employee values ('employee2') go 

last free count = 7087 i.e. 7087 - 1007 - 2 = 6087 => m_freecnt = 6078

insert employee values ('employee3') go 

last free count = 6078 i.e. 6078 - 1007 - 2 = 5069 => m_freecnt = 5069

insert employee values ('employee4') go 

last free count = 5069 i.e. 5069 - 1007 - 2 = 4060 => m_freecnt = 4060

insert employee values ('employee5') go 

last free count = 4060 i.e. 4060 - 1007 - 2 = 3051 => m_freecnt = 3051

insert employee values ('employee6') go 

last free count = 3051 i.e. 3051 - 1007 - 2 = 2042 => m_freecnt = 2042

insert employee values ('employee7') go 

last free count = 2042 i.e. 2042 - 1007 - 2 = 1033 => m_freecnt = 1033

till works normal. have 1033 bytes left in data page. add 8th record, ideally should not create page because number of bytes free 1033 bytes sufficient enough accommodate 8th record (1009 bytes enough). however, sql create new date page save 8th record.

i inserted 8th record , ran dbcc ind check -

insert employee values ('employee7') go  dbcc ind('testdb','dbo.employee',-1) 

now has created new data page pagenumber = 300.

i did not understand part. whether sql keeps bytes reserved except [header (96) + data part + offset per page 2 bytes] this?

you can try running above queries , let me know if missing out here? or whether shouldn't bother these memory details of sql?

thanks.

just re-phrase answer make clear:
reach 81% of page filled, pfs record page change first 2 bits 11, mean "page 95% full" , sql not allow inserting more records though there plenty of space (more 5% free).

there 2 ways fight problem:
easy: create clustered index - able set fillfactor 100% if needed.
difficult: if have on page more 19% of free space can insert more 1 record using 1 insert statement fill page 100%.
see example:

create table testtable(f0 smallint); go insert testtable (f0)  select top 699 1 sys.messages; go begin tran    insert testtable (f0)    select top 37 2 sys.messages;   select      (select count(*) sys.dm_db_database_page_allocations(db_id(), object_id('testtable'), null, null, 'detailed') page_type = 1) pages,     (select count(*) testtable) "count"; rollback go begin tran    insert testtable (f0) values (3)   go 2   select      (select count(*) sys.dm_db_database_page_allocations(db_id(), object_id('testtable'), null, null, 'detailed') page_type = 1) pages,     (select count(*) testtable) "count"; rollback go drop table testtable go 

after reach 699 records can insert 37 new records @ once or 1 record if insert on record-by-record basis.