Page Size 8K

Question

Page Size 8K

DSQLServer

SSChasing Mays

Points: 647
More actions
March 15, 2011 at 7:22 pm

#248430

MS tells me that page size is 8K but then says in parens that 8K is 8060. I thought 1K was 1024 so 8K would be 8192 (8x1024).
There is a 96 byte page header so I thought that maybe 8192-96 would be 8060, but it is 8096.
Anyone know how MS get 8060?
Reference: http://msdn.microsoft.com/en-us/library/ms190969

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

great question.

this book references says that some additional logging information is stored inside the page, and as a result, that makes the max size 8060:

http://books.google.com/books?id=G2YqBS9CQ0AC&pg=PA964&lpg=PA964&dq=8060,+but+it+is+8096&source=bl&ots=ZvMVDjIUaL&sig=3HWawbxnTZqAmVYsPmcNyCSVoxY&hl=en&ei=NhKATdffOPKL0QH3_pn5CA&sa=X&oi=book_result&ct=result&resnum=1&ved=0CBUQ6AEwAA#v=onepage&q=8060%2C%20but%20it%20is%208096&f=false

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jim McLeod SSCarpal Tunnel Points: 4057 More actions · Answer 2

Just to clarify Lowell's reply - the page written to the transaction log only has enough room to store 8060 bytes of data. While you might be able to squeeze an extra ~30 bytes in your data page, it's not desirable (possible?) to split an operation over multiple pages in the transaction log.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

The maximum sized datatype, not including the MAX data types, is 8016 (SQL_Variant). For all the

other datatypes, the max row size can actually be less than 8060 because of the "Row Offsets"

most people forget about at the end of the page and several other factors like the 2 bytes extra

that are consumed by each variable length data type, null bits, etc, etc.

Steve Jones actually wrote about this in one of his articles on SQL Server 2000.

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

--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)