How SQL Decides to Add a New Data Page

Question

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

I created a sample Employee table in a new database using

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 starting DBCC, I saw 2 pages. One of them is the IAM page (PageType = 10), and the other is the Data page (PageType = 1), which contains the actual data.

Later I checked the actual contents of the data page using the DBCC page

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

I saw how SQL calculates the number of bytes of m_freecnt -

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

i.e. 8K Page = 8192 bytes = 8192 - 1105 = 7087 bytes for free.

Now I continue to add records to this table to understand how many records will be on this page, and when SQL selects a new page in which m_FreeCnt will be taken into account.

(Record size = 1007 and byte offset = 2)

The second entry is added -

 Insert into Employee values ('Employee2') GO 

Last free account = 7087, i.e. 7087 - 1007 - 2 = 6087 => m_FreeCnt = 6078

 Insert into Employee values ('Employee3') GO 

Last free account = 6078, i.e. 6078-1007-2 = 5069 => m_FreeCnt = 5069

 Insert into Employee values ('Employee4') GO 

Last free account = 5069, i.e. 5069 - 1007 - 2 = 4060 => m_FreeCnt = 4060

 Insert into Employee values ('Employee5') GO 

Last free account = 4060, i.e. 4060 - 1007 - 2 = 3051 => m_FreeCnt = 3051

 Insert into Employee values ('Employee6') GO 

Last free account = 3051, i.e. 3051 - 1007 - 2 = 2042 => m_FreeCnt = 2042

 Insert into Employee values ('Employee7') GO 

Last free account = 2042, i.e. 2042 - 1007 - 2 = 1033 => m_FreeCnt = 1033

So far, everything is working fine. Now 1033 bytes are left on this data page. As soon as I add the 8th record, ideally it should not create another page, because the number of free bytes is 1033 , which is enough to accommodate the 8th record (1009 bytes are enough). However, SQL creates a new date page to save this eighth record.

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

 Insert into Employee values ('Employee7') GO DBCC IND('TestDb','dbo.Employee',-1) 

Now he has created a new data page with PageNumber = 300.

I did not understand this part. Regardless of whether SQL stores reserved bytes except [header (96) + Data part + offset per page 2 bytes] this?

Can you try the query and let me know if I'm missing something here? Or should we not worry about this SQL memory data?

Thanks.

Answer 1

Just rephrase another answer to make it clear:
As soon as you reach 81% of your page, the PFS record for this page will change the first 2 bits to 11 , which means that "The page is 95% full" and SQL will not allow you to insert more records, even if there is a lot of space (more than 5 % are free).

There are two ways to deal with this problem:
Easy: create a clustered index - then you can set fillfactor as 100% if necessary.
Difficult: if your page has more than 19% free space, you can insert more than one record using the ONE INSERT to fill the page 100%.
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 a time, or just one record if you insert a record by record.

Answer 2

Here is what I found: http://social.technet.microsoft.com/wiki/contents/articles/21877.sql-server-how-does-sql-server-allocate-space-in-a-heap.aspx

In total, there are 2 records on the data page (m_slotCnt) and 3,063 bytes are available on the page for new records (m_freeCnt). If Microsoft SQL Server inserts a new record into this page, it has input for offset 5.126 (m_freeData). The last, but nonetheless important page is used at 51% - 80%. If Microsoft SQL Server will read the information from the page header, you should know about 3,062 bytes of available space on the page. But this is not so!

Record 3 is 2504 bytes long + row overhead, but Microsoft SQL Server did not insert the record on page 150 due to the following calculation:

PFS reports that space usage is 80%, which means 6554 bytes from the full page space. The free space on the page is 1638 bytes, and the record 3 has a length of 2,504 bytes, which does not correspond to the page based on these calculations! This calculation shows a PFS scan instead of a special page header scan. Because the new record does not fit on the page, a new one will be created as the extraction from the transaction log becomes visible.

In your example:

 m_slotCnt 7 m_freeCnt 1033 m_freeData 7145 PFS (1:1) 0x63 MIXED_EXT ALLOCATED 95_PCT_FULL 

8192 * 0.95 = 7782

8192-7782 = 409 <1007

I repeated your example with a string length = 10 char (107 bytes) and I get:

 m_freeCnt 357 PFS (1:1) 0x64 MIXED_EXT ALLOCATED 100_PCT_FULL 

real PFS = 95.6%, but SQL Engine considers it 100_PCT_FULL and creates a new page for the next line