What is the Maximum Page Size in SQL Server 2000?
Introduction
I have always read that 8060 bytes is the maximum size. This is stated over and over again in
Books Online, the MS site and numerous other sites, including this one. However a post in our forum
recently questioned this. I decided to verify the problem and do a little research.
The Problem
A reader posted the following question:
create table testtablesize (col1 varchar(8000), col2 varchar(35)) Message: The command(s) completed successfully. create table testtablesize (col1 varchar(8000), col2 varchar(36)) Message: The total row size (8061) for table 'testtablesize' exceeds the maximum number of bytes per row (8060). Rows that exceed the maximum number of bytes will not be added. The maximum after splitting into two columns is 8035 Bytes 8060 - 8035 = 25 Bytes Is the 25 Bytes used for header information ?
My Detective Work
I started researching this problem using Books Online and Inside SQL Server 2000 by Kalen Delaney,
which I keep by my desk. Whenever I have questions about the internals of SQL Server, this is usually
my first resource.
An 8kb page contains 8192 bytes. This is because each kb is 1024 bytes. The Microsoft documentation specifies
that a page header contains 96 bytes of overhead. This is for keeping track of the page within the system, kind of
like a File Allocation Table on your hard drive. This leaves 8096 bytes for data and
row offsets (pg 247, Inside SQL Server 2000). Supposedly this leaves the maximum size for
a single data row at 8060 bytes.
So why does the above table not get created?
I decided to post to Usenet (microsoft.public.sqlserver.server). I got a couple responses in a few
hours, but none provided an explanation (one person merely answered "overhead").
Meanwhile, I decided to look up some more information in Inside SQL Server.
I learned the following:
First, my DDL for testing:
create table MyTest (mychar char( 8000) , mychar2 char( 39) )
Each row has a 2 byte offset (pg 248, Inside SQL Server 2000). For a single row on a page this is
1 row x 2 bytes/row = 2 bytes. This leaves 8094 bytes (8096 - 2) for data and row offsets, 8060 for
data (I would think).
For a variable length column (like a varchar column), there is 2 bytes, per varchar column.
However my table doesn't contain any variable length columns, so this does not apply.
I was somewhat confused by the explanations in both the book and BOL. I kept flipping between the
explanations of datatype storage (Chapter 12 in Inside SQL Server) and the architecture descriptions
of the pages in both BOL and Inside SQL Server. Finally, I decided to look at my table
in syscolumns. I found:
This shows thatcolumn 1 uses 8000bytes (size), with an offset of 4 (overhead). The offset is where the
data starts from the end of the overhead. Column 2 (since these are fixed size columns) starts at an offset of
8004 (4 bytes + 8000 for column 1) and has a size of 39 bytes.
This makes sense. One other thing to notice, sysindexes shows the minlen=8043. This implies, based
on the descriptions in my resources, that this row will be 8043 bytes in length, no matter what the data. If
there were variable length columns, then this would be the minimum if all variable length columns were NULL.
Inside SQL Server says that this minlen does not include the following:
- 2 bytes for # of columns.
- bytes needed for the null bitmap.
I assume this means the status bytes (2) and the basic overhead of 2 bytes for are included.
Earlier, the null bitmap is shown as 1 bit for each column, which means a maximum number of bytes
equal to (the # of columns/8). For me, this is a single byte (2 bits).
For my row, I should see the following overhead:
- 1 byte - status bits
- 1 byte - more status bits
- 2 bytesrepresenting the length of the fixed data (value for these 2 bytes is 8039)
- 8039 bytes - for storing the fixed length data.
- 2 bytes that represent the number of columns (value here is 2)
- 1 byte to store the null bitmap (2 cols = 2 bits = 1 byte)
This is a total of 8046 bytes. Still far short of what we expect. So where are the other 14 bytes?
At this point in my detective work, I was a little annoyed. In my mind, there should be 8096 total bytes on
a page with 36 of these bytes used for overhead. Instead, the 8060 figure often quoted includes some
overhead, so you cannot really create a row (excluding rows with BLOBs) with 8060 bytes.
If I add a row to this table, then I can examine a page. Here is what I ran:
insert MyTest values ('A', 'B')
With a single row in a new table, the page data should be easy to decode.
When I run:
dbcc traceon( 3604) dbcc page( 8, 1, 150, 1 ) dbcc traceoff( 3604)
I get:
DBCC execution completed. If DBCC printed error messages, contact your system administrator. PAGE: (1:150) ------------- BUFFER: ------- BUF @0x192ABAC0 --------------- bpage = 0x38916000 bhash = 0x00000000 bpageno = (1:150) bdbid = 8 breferences = 1 bstat = 0xb bspin = 0 bnext = 0x00000000 PAGE HEADER: ------------ Page @0x38916000 ---------------- m_pageId = (1:150) m_headerVersion = 1 m_type = 1 m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8000 m_objId = 1531868524 m_indexId = 0 m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 8043 m_slotCnt = 1 m_freeCnt = 48 m_freeData = 8142 m_reservedCnt = 0 m_lsn = (2082:2096:1) 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) = 0x64 MIXED_EXT ALLOCATED 100_PCT_FULL DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED DATA: ----- Slot 0, Offset 0x60 ------------------- Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 38916060: 1f6b0010 20202041 20202020 20202020 ..k.A 38916070: 20202020 20202020 20202020 20202020 ... (lots of these rows) 38917FA0: 20202020 20202042 20202020 20202020 B 38917FB0: 20202020 20202020 20202020 20202020 38917FC0: 20202020 20202020 02202020 0000 ... OFFSET TABLE: ------------- Row - Offset 0 (0x0) - 96 (0x60) DBCC execution completed. If DBCC printed error messages, contact your system administrator. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
If you examine the header information, this shows 48 bytes free on this page. Remember that
these are fixed columns, so where is the extra overhead?
NOTE: There were lots of rows that contained 20202020 in the space where the ... is used. I removed them
to save some space.
I did some research on the www.insidesqlserver.com
site and found the answer (which was not in the book). SQL Server sometimes moves rows. In
moving rows, it places a pointer that points back to the original row location. To reserve
space for this back pointer, 10 bytes are required. However, the space for the back pointer is
a variable length field, so there are 4 bytes needed for this as overhead (2 bytes for the "column"
and 2 bytes for the length of the variable length field). Thus, there are a minimum of 14 extra
bytes required by each row that is not really documented. Not in BOL, not in Inside SQL Server.
If you add these 14 bytes to the row size I calculated, 8046, then you get the 8060 that is
the maximum row size. Tada!!!!
Conclusions
This was not all that interesting, but I did learn more about internal storage. I was also
more than slightly annoyed to learn about the 14 bytes for the back pointer. To me, since
this is not space that is available for the row, it should not be included, or even
documented as a possible row size. The maximum possible row size is in fact 8039. IMHO,
Microsoft is incorrectly providing a specification.
As always, I hope you learned something and I welcome feedback on this article using the
"Your Opinion" button below. Please take a moment to also rate this article.
Steve Jones
©dkRanch.net October 2001