Sometimes when you insert row on heap, even if the page has enough free space, the new row can not be inserted into that page, and a new page will be created for the new row. Here is interesting example:
1. Create testing DB
use master
go
CREATE DATABASE [test] ON PRIMARY
( NAME = N'test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test_log.ldf' , SIZE = 1024KB , FILEGROWTH = 1024KB)
GO
2. Create table
CREATE TABLE myHeap
(
a char(500) NOT NULL,
b char(100) NOT NULL,
c char(153) NOT NULL,
) ON [PRIMARY]
GO
so the row length will be 753+7=760 bytes, in theory, the every page can hold 10 rows(8060/760=10)
3, insert 10 rows
Declare @int int
set @int=1
while (@int <= 10)
begin
insert into myHeap(a,b,c)
values(right('00000'+CONVERT(varchar(5),@int),5),'bbbbb','ccccc')
set @int=@int+1
end
4. check the table page status
SELECT %%physloc%% rowid, sys.fn_PhysLocFormatter (%%physloc%%) AS [Physical RID],DATALENGTH(a) a_length, DATALENGTH(b) b_length,DATALENGTH(c) d_length, *
FROM test.dbo.myHeap
| rowid | Physical RID | a_length | b_length | d_length |
| 0x9900000001000000 | (1:153:0) | 500 | 100 | 153 |
| 0x9900000001000100 | (1:153:1) | 500 | 100 | 153 |
| 0x9900000001000200 | (1:153:2) | 500 | 100 | 153 |
| 0x9900000001000300 | (1:153:3) | 500 | 100 | 153 |
| 0x9900000001000400 | (1:153:4) | 500 | 100 | 153 |
| 0x9900000001000500 | (1:153:5) | 500 | 100 | 153 |
| 0x9900000001000600 | (1:153:6) | 500 | 100 | 153 |
| 0x9900000001000700 | (1:153:7) | 500 | 100 | 153 |
| 0x9900000001000800 | (1:153:8) | 500 | 100 | 153 |
| 0x9B00000001000000 | (1:155:0) | 500 | 100 | 153 |
in fact, the first 9 rows are in page 153, however, the 10th row are in the other page 155.
4. let's check how much free space is in page 153
DBCC TRACEON(3604)
GO
dbcc page(test,1,153,3)
GO
m_pageId = (1:153) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 27 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594039697408
Metadata: PartitionId = 72057594038779904 Metadata: IndexId = 0
Metadata: ObjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 757 m_slotCnt = 9 m_freeCnt = 1238
m_freeData = 6936 m_reservedCnt = 0 m_lsn = (32:86:2)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:1) = 0x63 MIXED_EXT ALLOCATED 95_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
Slot 0 Offset 0x60 Length 760
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 760
well, there is 1238 bytes free space, the row size is 760 byte, so there is enough free space to hold the 10th rows. why sql server create a new page to keep 10th row??? here we found a interesting info in the page head : PFS indicts the page is 95% full, so there is only 8060*5%=403 bytes free space.
let's review how sql server insert rows on Heap, because there is no clustered index, a new row is always inserted wherever room is available in the heap table. sql server will use IAMs and the PFS pages to keep track of which extents in ta file already belong to a table and which of the pages in those extens have space available.
5. let's check the PFS page:
DBCC TRACEON(3604)
GO
dbcc page(test,1,1,3)
GO
(1:153) - = ALLOCATED 95_PCT_FULL Mixed Ext
The PFS page contains 1 byte for each page in a 8088-age range of a file, and the last three bits are used to indicate the age fullness.
The bits in each byte has some meaning as illustrated below
- bits 0-2: how much free space is on the page
- 000 is empty
- 001 is 1 to 50% full
- 010 is 51 to 80% full
- 011 is 81 to 95% full
- 100 is 96 to 100% full
- bit 3 : 1 if page has ghost records else 0
- bit 4 : 1 if an IAM page else 0
- bit 5 : 1 if mixed extent, 0 if uniform
- bit 6 : 1 if page allocated, 0 if unallocated
we have 9 rows in page 153, 9*760/8060 =84.9% full, so PFS indicts it is 95% full. when sql server check page 153, it think the free space on page 153 is 8060*5%=403 bytes < row size 760bytes, so a new page 156 was created for 10th row.
6. In order to save space and keep the 10 rows in the same page, we can create a clustered index,
ALTER TABLE dbo.myheap ADD CONSTRAINT
PK_mytest PRIMARY KEY CLUSTERED
(
a
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
then we can find all 10 rows are in the same page
SELECT %%physloc%% rowid, sys.fn_PhysLocFormatter (%%physloc%%) AS [Physical RID],DATALENGTH(a) a_length, DATALENGTH(b) b_length,DATALENGTH(c) d_length, *
FROM test.dbo.myHeap
| rowid | Physical RID | a_length | b_length | d_length |
| 0x9C00000001000000 | (1:156:0) | 500 | 100 | 153 |
| 0x9C00000001000100 | (1:156:1) | 500 | 100 | 153 |
| 0x9C00000001000200 | (1:156:2) | 500 | 100 | 153 |
| 0x9C00000001000300 | (1:156:3) | 500 | 100 | 153 |
| 0x9C00000001000400 | (1:156:4) | 500 | 100 | 153 |
| 0x9C00000001000500 | (1:156:5) | 500 | 100 | 153 |
| 0x9C00000001000600 | (1:156:6) | 500 | 100 | 153 |
| 0x9C00000001000700 | (1:156:7) | 500 | 100 | 153 |
| 0x9C00000001000800 | (1:156:8) | 500 | 100 | 153 |
| 0x9C00000001000900 | (1:156:9) | 500 | 100 | 153 |
DBCC TRACEON(3604)
GO
dbcc page(test,1,156,3)
GO
m_pageId = (1:156) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x4
m_objId (AllocUnitId.idObj) = 28 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594039762944
Metadata: PartitionId = 72057594038845440 Metadata: IndexId = 1
Metadata: ObjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 757 m_slotCnt = 10 m_freeCnt = 476
m_freeData = 7696 m_reservedCnt = 0 m_lsn = (32:93:21)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
this example is one of the reasons why clustered index is recommended on big table
reference
http://sqlsimplified.blogspot.com/2012/01/page-free-space.html
