Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

LivingForSqlServer

I am Ramkumar, 34 years old Consultant, trainer, blogger and speaker at SQL Server user group in India. I have more than 10 years of experience as a developer and SQL Server DBA. I love reading, teaching and blogging about SQL Server internals and performance tuning. My Facebook page: https://www.facebook.com/LivingForSqlServer

Data Page math : 96 + 8060 + 36 = 8192. is this correct?

I have come accross interesting puzzle yesterday on numbers 96 and 36.
Everyone knows that header size is 96 and maximum allowable row size is 8060.

But I was bit confused while I saw the response as 36 bytes is for row offset array (as this is not enough to point 40+ rows in a page).

So I decided to do quick R & D on this.

Purpose of this post to understand how space is managed within a page (for header, data rows and row offset array).

Quick recall:
1. Page header size is 96 bytes - No control on this
2. 2 bytes for every row pointer in Row offset array

so 36 bytes of row offset array is not sufficient to point all rows/slots in a page.

Lets do quick POC on this.

Step1: Create a simple table that consume 9 bytes per record (+ 7 overhead bytes = 16 bytes) this is to do validation quicker

create table t1(intSlno int identity, strName char(5))

Step 2:  Insert 500 records in this table

insert into t1 values('A')
GO 500
 

Step 3: check number of pages consumed for this table

DBCC TRACEON(3604)
DBCC IND('<dbname>, 't1', -1)
DBCC TRACEOFF(3604)

Observation: 2 data pages are allocated for this.

Step 4: Lets look at number of records/slots stored in first data page (page header)

DBCC TRACEON(3604)
DBCC PAGE(<dbname>, 1, <pageid>, 1)
DBCC TRACEOFF(3604)

Number of Slots and  free bytes in first page header
m_slotCnt = 428
m_freeCnt = 392

Maths Now:

Bytes consumed for Page header: 96
Bytes consumed for 428 records: 428 * 16 bytes/record = 6848
Bytes consumed for row offset array: 428 records * 2 bytes/record = 856

Total (header + data + row offset array) : 96 + 6848 + 856 = 7800
Bytes free (as in page header): 392
Total bytes/Page: 8192

Lesson learnt:

1. Bytes used for data in a page depends on number of records (columns, data type and column size)
2. Minimum bytes overhead/ Record: 7 bytes (with in record for only fixed length datatypes) + 2 bytes for row offset array to point a record = 9 bytes

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.