Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I continue to read in many forums and on many blogs that a page is comprised as shown below: Page Size: 16 x 512B = 8192B Page Header: = 96B Maximum In_Row Row: = 8060B

This leaves (8192 - 96 - 8060)B = 36B.

Ok, this is logical and correct. The question I have is this: why do so many people say that the remaining 36B is reserved for the slot array?

Obviously, the slot array gives 2B per row on the page; so, it can be as small as 2B and as large as 1472B:

2B: 1 row * 2B = 2B

1472B: 8096B = n*9B (min row size with overhead...think single TINYINT column) + n*2B (slot array cost per row) => 8096 = 11n => n = 8096 / 11 = 736.

736*2B = 1472B.

This gets me to 20 due to the 14B version tag.

USE master ;
GO

CREATE DATABASE test ;
GO

USE test ;
GO

ALTER DATABASE test
    SET ALLOW_SNAPSHOT_ISOLATION ON ;
GO

ALTER DATABASE test
    SET READ_COMMITTED_SNAPSHOT ON ;
GO

DROP TABLE tbl ;
GO

CREATE TABLE tbl
(
      i CHAR(8000) DEFAULT(REPLICATE('a',8000))
    , j CHAR(53)   DEFAULT(REPLICATE('a',53))
) ;

INSERT INTO tbl 
    DEFAULT VALUES ;
GO

DBCC IND (test,tbl,-1) ;
GO
DBCC TRACEON(3604) ;
GO
DBCC PAGE(test,1,272,3) ;
GO

Another example. If you go to 50 from 49, you get the VARCHAR(MAX) going to LOB_DATA.

DROP TABLE tbl ;
GO

CREATE TABLE tbl
(
      i VARCHAR(MAX) DEFAULT(REPLICATE('a',8000))
    , j CHAR(49)   DEFAULT(REPLICATE('a',49))
) ;

sp_tableoption N'tbl', 'large value types out of row', 'OFF' ;
GO

INSERT INTO tbl 
    DEFAULT VALUES ;
GO

DBCC IND (test,tbl,-1) ;
GO
DBCC TRACEON(3604) ;
GO
DBCC PAGE(test,1,272,3) ;
GO

It appears that this issue remains, even in SQL Server 2012. @SQLKiwi points to this post by Kimberly Tripp - http://www.sqlskills.com/blogs/kimberly/a-simple-start-table-creation-best-practices/.

share|improve this question
1  
Hmm, Wonder why the maximum row size isn't 8094 as for a row that size clearly only one slot will be used. Maybe something going back to Sybase days? Looks like for internal sort pages 8094 is indeed the limit from this error message –  Martin Smith Mar 15 '12 at 12:28
1  
I tweeted #sqlhelp because was curious about this. Paul Randal pointed out things like 14 byte version tag. But testing this on DB with snapshot isolation enabled gives an error CREATE TABLE Foo(A Char(8000),B Char(53));INSERT INTO Foo VALUES(REPLICATE('A',8000),REPLICATE('B',53));UPDATE Foo SET B = REPLICATE('C',53) I get Internal error. Buffer provided to read column value is too small. Run DBCC CHECKDB to check for any corruption.. Maybe Denali uses this space –  Martin Smith Mar 15 '12 at 14:47
    
The other thing mentioned was the 10 bytes forwarding record back-pointer but I haven't managed to get that to eat up any of these 36 bytes either. It just moves onto Row-overflow data as far as I can see in preference to having an 8070 byte row. –  Martin Smith Mar 15 '12 at 15:26
    
@martin That is what bugs me. I can fill a page to m_freeCnt = 0 but if I want just one row I can max it out and still have the 34 bytes left (36B - 2B slot count for my one row). So, even with a fill factor of 100%, I can get pages with 34B of wasted space. This was the reason I asked my question about heaps vs. CIXs yesterday. Obviously, a full page is not likely a good idea in most cases. –  ooutwire Mar 15 '12 at 15:32
3  
The decision to limit in-row data to 8060 does seem to have been a deliberate design decision. The spare 36 bytes were for future expansion, of which row-versioning claimed 14 bytes. I think it would be good to summarize the comments discussion in an answer post - comments are not searchable. It is fine to answer your own question. Oh, last thing, that bug was fixed in 2012 so the update succeeds. –  Paul White Dec 1 '12 at 14:20
show 13 more comments

1 Answer 1

up vote 5 down vote accepted

Where pages are used for internal purposes like sort runs, the maximum row size is 8094 bytes. For data pages, the maximum in-row size including internal row overhead is 8060 bytes.

Internal row overhead can expand significantly if certain engine features are in use. For example, using sparse columns reduces the user-accessible data size to 8019 bytes.

The only example of external row overhead I know of up to SQL Server 2012 is the 14 bytes needed for versioned rows. This external overhead brings the maximum space usage for a single row to 8074 bytes, plus 2 bytes for the single slot array entry, making 8076 bytes total. This is still 20 bytes short of the 8096 limit (8192 page size - 96 byte fixed header).

The most likely explanation is that the original 8060 byte limit left 34 bytes for future expansion, of which 14 were used for the row-versioning implementation.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.