8KB page size

Question

8KB page size

Gopi S

Ten Centuries

Points: 1091
More actions
August 28, 2010 at 3:11 am

#226953

Hi,
I am creating a table of row size char(1000) as below.
create table Optimised(id char(1000))
i am insert values 1,2,3,4.....
insert into Optimised values('1')
insert into Optimised values('2')
insert into Optimised values('3')
insert into Optimised values('4')
insert into Optimised values('5')
insert into Optimised values('6')
insert into Optimised values('7')
insert into Optimised values('8')
After inserting value '7', sp_spaceused shows reserved:16KB and data:8KB which is expected. But when i insert next value '8' then sp_spaceused shows reserved:24KB and data:16KB. How the last insert increase the page size to 16KB since still i have used only 8000 bytes which is in 8KB?
Can anyone pls explain this..
Thanks
Gopi

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

Cannot remember details (its probably in BOL) but each page has a header as well at least taking up bytes, so the 8th row did not fit and went onto a new page.

see how much space is wasted if columns are not defined carefully.

---------------------------------------------------------------------

Brian O'Leary SSChasing Mays Points: 623 More actions · Answer 2

The last row inserted causes SQL Server to allocate a new 8K page for your table, this means your table now has two 8K pages therefore its size is 16K. The unused column could read 8K.

MCITP SQL Server 2005/2008 DBA/DBD

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Each Page has overhead as george said. Due to the overhead, you would be creating a new page on the 8th insert.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Gopi S Ten Centuries Points: 1091 More actions · Answer 4

Hi,

Thanks for the update.

Still few more clarification needed.

We have 8192 bytes of page. 96 bytes is used for header information.In my above example 16 bytes will be reserved for offset storage. Still we have 8080 bytes remaining. But how i am unable to store last record in the same page since it takes only 8000 bytes totally? Can you pls explian how the memory is used in my example and the reason how the last insert use next page.

Thanks in advance.

Gopi

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

This article may be of interest. There is more overhead imposed by the internals of SQL server than what you listed.

http://www.sqlservercentral.com/articles/Miscellaneous/pagesize/497/

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 6

Gopinath Srirangan (8/28/2010)
Hi,
I am creating a table of row size char(1000) as below.
create table Optimised(id char(1000))
i am insert values 1,2,3,4.....
insert into Optimised values('1')
insert into Optimised values('2')
insert into Optimised values('3')
insert into Optimised values('4')
insert into Optimised values('5')
insert into Optimised values('6')
insert into Optimised values('7')
insert into Optimised values('8')
After inserting value '7', sp_spaceused shows reserved:16KB and data:8KB which is expected. But when i insert next value '8' then sp_spaceused shows reserved:24KB and data:16KB. How the last insert increase the page size to 16KB since still i have used only 8000 bytes which is in 8KB?
Can anyone pls explain this..
Thanks
Gopi

Perhaps this will explain it...

1000 bytes per row.

4 bytes row header over head per row.

3 bytes (in this case) for the Null bitmap.

2 bytes for the row's entry in the slot array of the page.

-----

1009 * 8 = 8072 bytes... 12 more than a page will hold (8060).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Jeff Moden (8/28/2010)
Gopinath Srirangan (8/28/2010)
Hi,
I am creating a table of row size char(1000) as below.
create table Optimised(id char(1000))
i am insert values 1,2,3,4.....
insert into Optimised values('1')
insert into Optimised values('2')
insert into Optimised values('3')
insert into Optimised values('4')
insert into Optimised values('5')
insert into Optimised values('6')
insert into Optimised values('7')
insert into Optimised values('8')
After inserting value '7', sp_spaceused shows reserved:16KB and data:8KB which is expected. But when i insert next value '8' then sp_spaceused shows reserved:24KB and data:16KB. How the last insert increase the page size to 16KB since still i have used only 8000 bytes which is in 8KB?
Can anyone pls explain this..
Thanks
Gopi
Perhaps this will explain it...
1000 bytes per row.
4 bytes row header over head per row.
3 bytes (in this case) for the Null bitmap.
2 bytes for the row's entry in the slot array of the page.
-----
1009 * 8 = 8072 bytes... 12 more than a page will hold (8060).

Thanks Jeff for outlining it so well.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 8

CirquedeSQLeil (8/28/2010)
Thanks Jeff for outlining it so well.

I may have made a slight mistake but the end result is the same. Let me figure out the mistake.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 9

Heh... nah... I thought I made a mistake but I was... ummm.. mistaken. 🙂

The mistake is in BOL. They claim that there are 8096 bytes per row in that article. If there were, the 8 rows would fit on a single page.

{edit} There are 8096 bytes per row but they didn't account for the extra 36 bytes in any of the calculations according to the link Jason posted.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Brian O'Leary SSChasing Mays Points: 623 More actions · Answer 10

It appears I also made a mistake, I totally misunderstood the initial question.

I'm not going to go back over Jeff Moden's answer it's correct. However if you would like analyse this yourself you can use the following code:

--Find page number

DBCC IND ([DatabaseName], [TableName], -1)

--Find FileID

SELECT * FROM sys.database_files

--Read page

DBCC TRACEON(3604)

GO

DBCC PAGE([DatabaseName], [FileID], [Page], 1)

MCITP SQL Server 2005/2008 DBA/DBD

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 11

It's always fun to look at that stuff at the byte level. Thanks for posting it, Brian. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rahul Bhosale SSC Eights! Points: 931 More actions · Answer 12

hi Gopi,

please check out the blog to ensure correct understanding of how SQL stores rows into pages.

How SQL Stores rows in 8Kb pages [/url]

Hope you will like it.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Please note: 3 year old thread

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass