13

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

8

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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