# Slot Array and Total Page Size

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
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/.

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