question

srivivek avatar image
srivivek asked

page size in sql

Hi all, can anyone clarify my doubt, thanks in advance. why the page size in sql is 8 kb only,Is there any specific reason for that?
sizepage
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
The antithesis of the DBA stock answer 'it depends' but with the same vague tone - **it just is**!
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The page size is always 8192 bytes, out of which 8060 are available to us lesser mortals and the rest is used by the engine to store data such as volatile rowid, 2 byte overhead per each varchar column etc. This means that create table dbo.dont_do_it(col1 varchar(100), col2 varchar(8000)); is silly but table is created with warning and works until one tries to modify data such that one of the records is wider than 8060 bytes. The good news is that at least the stupidity like this is not allowed with create table dbo.cant_do_it(col1 char(100), col2 char(8000)); because the engine will not allow such table to be created. Unfortunately, SQL Server 2008 actually allows the insanity described above because it has newly added overflow pages (not to be mixed up with LOB pages used to store CLOBs such as varchar(max) and BLOBs such as varbinary(max)).
3 Likes 3 ·
Yes & no. You can create a table that defines a row that goes beyond 8060 and you'll get warnings when createing the table. As long as data inserted stays below 8060 you won't have any trouble, but if you insert data beyone 8060, you'll get an error. Physically, SQL Server cannot store beyond 8060. So while you can define a structure beyond that, it is a hard stop.
1 Like 1 ·
I'm sure it has something to do with memory architecture. 8 pages of 8k each to make up a 64k extent... I agree with your answer.
0 Likes 0 ·
thanks for reply, and one more doubt, is it compulsory that a row size should be <8060 bytes.
0 Likes 0 ·
...unless you are using the new (MAX) datatypes, and overflow-pages, of course... :)
0 Likes 0 ·
Mark avatar image
Mark answered
I found this from MichaelJasson : Why This is 8k? 1 MB = 128 pages. Now 1 MB = 1024 so we needed something that should divide this completely and that should now be cumbersome as well. You know that one row, if this doesn't fit into one page, is written on another page. One row can't be written on two pages. Keeping this in mind 8K was choosen as 4K would be too short and every row would have to be < 4K. [from http://www.sqlservercentral.com/Forums/Topic747798-146-1.aspx ]
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I have this vague memory that the maximum rowsize wasn't always just shy of 8k...
0 Likes 0 ·
Mark avatar image
Mark answered
Actually the more I think of this, the more interesting it becomes since I used to think that if you needed to add a lot of text, say, over 100 characters wide, then you should use a text application like Word. A database just wasn't the place for lots of text! Databases were primarily thought of as tools for numbers and a little text (think GL and totals). So I can hear a developer from long ago thinking that we would never need that much space anyway. I also ran across this comment from Rick Strahl from 2004 too, who is one brilliant guy, I think: This is yet another instance where I'm surprised by some of the inflexibility that a SQL Backend provides. This limit seems pretty superficial based on some engine behavior that probably dates back to the Sybase days... [ http://www.west-wind.com/weblog/posts/207.aspx]
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David 1 avatar image
David 1 answered
The simple answer is: History. 8K sucks. It makes no sense. Bigger would be better. Unfortunately making the change would be an expensive opportunity cost for Microsoft and it would be disruptive and probably unpopular with customers when they upgraded. So there is little incentive to change it.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you imagine all the databases that would contain one wide table though? :)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.