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

What is the Maximum Page Size in SQL Server 2000?

By Steve Jones, (first published: 2002/02/07)

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 that	column 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 bytes representing 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
    Return to Steve Jones Home

     

Total article views: 36407 | Views in the last 30 days: 18
 
Related Articles
BLOG

Explore file physical structure - fixed length row

1. Create sample db use master go CREATE DATABASE [test] ON  PRIMARY ( NAME = N'test', FILEN...

FORUM

How to Specify Length of Output Column in SELECT Stmt?

How to Specify Length of Output Column in SELECT Stmt?

FORUM

Estimating replication overhead

replication overhead

FORUM

Fixed Length Flat File

I have a flat file with over 200 fixed length columns. Is there a way to easily load them into SQL ...

FORUM

Get longest length of data in a column

Get longest length of data in a column

Tags
miscellaneous    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones