How to prove 1 Page = 8 KB in SQL Server

In this post we will see how 8KB page is stored in SQL Server practically.

Before going to the demonstration, we’ll run through some basics. Page is the fundamental unit of data storage in SQL Server. 

One Page = 8 KB Size

One Extent = 8 Pages = 8*8 KB = 64 KB

Each page begins with a header which is 96-bytes (fixed size). Page header stores all system information about page like page number, type, and free space etc. Next comes the data rows and last segment is row offset. So a page can be divided into three parts

-Page Header

-Data

-Row Offset

Source : MSSQLTIPS

For more information about Pages and Extents click here.

Theoretically as documented, single page in SQL server is 8 KB. Now let’s move to the demonstration and see if really a page is of 8 KB size?

First I am going to create a database named ‘EXAMPLE’, a table ‘MYPAGE’ with column 'NAME' (CHAR(2000) datatype) and inset couple of record in MYPAGE table.

To get more information on this record and page details, we will use two undocumented commands

DBCC IND

DBCC PAGE

And trace flag 3604 to display the output of ‘DBCC PAGE’ in query window.

First let’s get the pageID details by running DBCC IND. For this demonstration, we’ll need to look into the following details

PagePID - the page number in the file

IAMFID - the file ID of the IAM page that maps this page (this will be NULL for IAM pages themselves as they're not self-referential)

IAMPID - the page number in the file of the IAM page that maps this page

ObjectID - the ID of the object this page is part of

PageType - the page type. Some common ones are:

          1 - data page
          2 - index page
          3 and 4 - text pages
          8 - GAM page
          9 - SGAM page
          10 - IAM page
          11 - PFS page

In output, first row is IAM page and second row is data page (Check page type). This PagePID of IAM Page will be linked with IAMPID for data page (marked in Yellow). Page number for our record is 230 (marked in red).

Note: From SQL 2012, a new DMV SYS.DM_DB_DATABASE_PAGE_ALLOCATIONS is introduced to get details on page allocation. This DMV is similar to DBCC IND, comparatively it give more information than DBCC IND command. 

Okay. Now we will turn on the trace flag 3604 and run the DBCC PAGE command.

Page Header size is fixed, which is 96 bytes. At present we have free space of 4078 bytes, m_freeCnt indicates free space in bytes.

Scroll down further; under data segment we can see our records (pointed in red).

First record and its size

Second record and its size

In our scenario, each record size will be 2000 bytes (as we have datatype CHAR(2000)). But if you look at the output (marked in blue), record size is of 2007 bytes (extra 7 bytes includes the record NULL_BITMAP).

Data Page = 2007 + 2007 = 4014 Bytes

Final part is row-offset; this will show us where record resides. In our table we have two records, so we will have two entries in row-offset and location for each record.

2 bytes will be allocated for each record in row-offset

Row Offset Size = Number of records * 2 = 2 * 2 = 4 Bytes

Page Size=8KB=8192 bytes = Free Space + Page Header + Data + Row-Offset

Free Space = 4078 Bytes

Page Header = 96 Bytes

Data = 4014 Bytes

Row-Offset = 4 Bytes

Page Size= 4078 + 96 + 4014 + 4 = 8192 Bytes

Hence Proved :-) :-)

Cheers,

Naveen