November 3, 2015 at 6:43 am
Hi Guys,
In below scenario we have 1033 BYTE free size on data page after adding 7 records & DB system just need 1009 BYTE to add new 8th record.
Why DB system create new data page to add 8th record instead of using free 1033 BYTE in data page 1?
Note: I don't have any kind of index on this table.
--==============================================================
------------------------------------------------
Page Size (KB) = 8
Total Page Size in BYTE = 8192
------------------------------------------------
------------------------------------------------------------------------------------------------
Records in Table = 7
Per Record BYTE Size =1007 (Including NULL_BITMAP 7 BYTE)
Total 7 Records BYTE Size = 7049
Page Header Fix BYTE Size = 96
Total Row Offset Size (2byts Each) =14
Total Free BYTE after 7th Records is 1033 = (8192 - (96 + 7049 + 14)
------------------------------------------------------------------------------------------------
----------------------------------------------------------------------
Expected Size for 8th Record BYTE =1007
Expected Size for Row Offset for 8th Record =2
Expected Total BYTE SIZE for 8th Records is 1009 = 1007 + 2
----------------------------------------------------------------------
--==============================================================
--Script as per below with Create table, Add Records, DBCC IND & DBCC PAGE
--================================================
USE tempdb
Go
Create Table tTemp03NOV2015 (Col1 Char(1000) Not Null);
-----Records on Data Page 1
Insert into ttemp03NOV2015 values('RECORD 1')
Insert into ttemp03NOV2015 values('RECORD 2')
Insert into ttemp03NOV2015 values('RECORD 3')
Insert into ttemp03NOV2015 values('RECORD 4')
Insert into ttemp03NOV2015 values('RECORD 5')
Insert into ttemp03NOV2015 values('RECORD 6')
Insert into ttemp03NOV2015 values('RECORD 7')
--After Inserting 7 Records ouput of DBCC IND & DBCC TPAGE
DBCC IND('tempdb',ttemp03NOV2015,-1)
GO
/*
PageFID PagePID IAMFID IAMPID ObjectID IndexID PartitionNumber PartitionID iam_chain_type PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
4 56 NULL NULL 421576540 0 1 6557241066381830000' In-row data 10 NULL 0 0 0 0
4 28 4 56 421576540 0 1 6557241066381830000' In-row data 1 0 0 0 0 0
*/
DBCC TRACEON (3604)
GO
DBCC PAGE('tempdb',4,28,1)
GO
/*
PAGE HEADER:
pminlen = 1004 m_slotCnt = 7 XXXXXXXXXXX m_freeCnt = 1033 XXXXXXXXXXX
*/
-----Records on Data Page 2
Insert into ttemp03NOV2015 values('RECORD 8')
--After Inserting 8th Records ouput of DBCC IND & DBCC TPAGE
DBCC IND('tempdb',ttemp03NOV2015,-1)
GO
/*
PageFID PagePID IAMFID IAMPID ObjectID IndexID PartitionNumber PartitionID iam_chain_type PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
3 456 4 56 421576540 0 1 6557241066381836288 In-row data 1 0 0 0 0 0
4 56 NULL NULL 421576540 0 1 6557241066381830000' In-row data 10 NULL 0 0 0 0
4 28 4 56 421576540 0 1 6557241066381830000' In-row data 1 0 0 0 0 0
*/
DBCC TRACEON (3604)
GO
DBCC PAGE('tempdb',3,456,1)
GO
/*
pminlen = 1004 m_slotCnt = 1 m_freeCnt = 7087
*/
November 5, 2015 at 10:42 am
The clue is in your question - "Note: I don't have any kind of index on this table."
SQL Server treats heaps differently for inserts. The data page needs to be less than 81% full for another record to be inserted on the same page. That means that after you've inserted 7 records, you only want the page to be 80% full to avoid a second page being created. Doing the math, that gives us:
0.80 * 8192 kb (page size) = 6553.6 kb
6553.6 kb - 96 kb (page header) = 6457.6 kb
6457.6 kb/7 = 922.5 bytes
922.5 b - 9 b = 913.5 bytes (3 bytes null bitmap, 4 bytes record header, 2 bytes row offset)
So 7 rows of 914 bytes should occupy less than 80% of the data page and allow insertion of an eighth record. You might even be able to use 915 or 916 bytes per record depending upon where the cutoff is between 80% and 81%. You can see the percentage full value in the Allocation Status section of the DBCC PAGE output.
Allocation Status
GAM (7:2) = ALLOCATED SGAM (7:3) = ALLOCATED
PFS (7:1) = 0x62 MIXED_EXT ALLOCATED 80_PCT_FULL DIFF (7:6) = NOT CHANGED
ML (7:7) = NOT MIN_LOGGED
See the following article for more information.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply