Thursday, January 3, 2013

Heap might take more space than clustered Index when inserting row

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


No comments:

Post a Comment