This article will be helpful to the SQL beginners like me, who may be confused with SQL pages, how SQL Server page is storing the data and how pages are aligned in Master data file. I am writing my view here and if I am wrong in this concept, kindly please correct me. Page split happens only conceptually not physically.

Pages

   Page is the fundamental storage unit of SQL, the page is 8KB size with 96 byte page header store information about page, such as Page type, page number, amount of free space on the page and allocation unit ID.

Extents

   Extents are eight contiguous pages or 64KB.

Type of Extents

1. Uniform Extents-Owned by a single object, all eight pages are used by single user objects.
2. Mixed Extents –Owned by multiple objects, each of the pages is shared by different objects may be some times eight objects.

Where my table is stored?

    Many people are confused with where my table is stored in which page. How to find where is my table data stored?

Here we go

use Demo

go

create table PageDemo

(

  id int identity primary key,

  FirstName varchar(10) ,

  LastName Varchar(10)

)

go

----------------------

create nonclustered index row_cl ON PageDemo (Firstname);

GO

-------------

insert into PageDemo values('Selva','Kumar')

insert into PageDemo values('John','Paul')

go

-----------------------

Here we created small demo table in our database.

Before we will go inside page,we want to enable traceon flag to get output in console (Management studio console), otherwise engine will write the output in Error log file.

Run the Traceon Command

use master

go

DBCC Traceon(3604)       ----------Enable output in Management studio Console

go

Now we will go in depth about page

Command to get page number:

  Here is the command to get the page number where our table is stored.

DBCC IND

(

['database name'|database id], -- the database to use

table name, -- the table name to list results

index id, -1 shows all indexes and IAMs, -2 just show IAMs

)

We choose option -1 in the above command; so it will give all pages related to the table.

We will focus on key column- page Type.

Here the page types:

• 1-Data Page
• 2-Index Page
• 3 and 4 –Text pages
• 8-GAM Page (Global Allocation map)
• 9-SGAM Page (Shared Global Allocation map)
• 10-IAM page (Index Allocation map)
• 11-PFS page (Page Free Space)

Now we will get the pages which are occupied by our table data and index.

Page ID=Page number

Page ID=154 and 156 are used by Index allocation mapping.

Now our actual data and index storage

Page ID=153 and Page type=1 is our actual data is storing

How to get deep on actual data

   Here the generic command to get the page details

DBCC page ( {'dbname' | dbid}, filenumber, pagenumber [, printoption={0|1|2|3} ])

The print option parameter has the following meanings:

  0 - print just the page header

     1 - Page header plus per-row hex dumps and a dump of the page slot array (unless it’s a page that doesn't have one, like allocation bitmaps)

    2 - Page header plus whole page hex dump

   3 - Page header plus detailed per-row interpretation

Output in table format:

Where is my INDEX PAGE?

  Now we will go with

 Page ID=155 and Page Type=2 is our actual non clustered index data.

We create non clustered index on First name

Our non-clustered index plus Key hash value for leaf node.

 Hope my article will be helpful to beginners.

See Also

Understanding Pages and Extents

How SQL Server stores data