Page Architecture in SQL Server

 Page Architecture in SQL Server:

Page:

Page is low level or physical unit where the actual data is present .
Each page is defined with unique number called pageno .
Page No Consists of
File ID: Page No
Default type of page is data page consists of 8 kb.
Each page consists of page header which consists of
                        PageNo
                        Type of Page
                        Free space present in the page
Page header takes 96 bytes of memory

Types of Pages :

1. Data Page
2. Index Page
3. Text/Image Page
4. Global Allocation Map (GAM) Page
5. Shared Global Allocation Map (SGAM) Page
6. Page Free Space Page
7. Index Allocation Map Page
8. Bulk Changed Map Page
9. Differential Changed Map Page

1. Data Page : Data page contains actual data except  text, ntext, image, nvarchar(max), varchar(max), varbinary(max), and xml data, whentext in row is set to ON.

2. Index Page : It contains index entries which consists of ColumnName , Row Reference

3. Text/Image Page : Consists of data whose data type is text, ntext, image, nvarchar(max), varchar(max), varbinary(max) and xml .Can constain 2 GB of data .

4. Global Allocation Map (GAM) Page : Consists of extents information  i.e how many are free and how many are used .

5. Shared Global Allocation Map (SGAM) Page : Consists of extents information  i.e how many are free and how many are used .

6. Page Free Space Page : It contains information about page allocation and free space available on pages.

7. Index Allocation Map Page : It contains information about extents used by a table or index per allocation unit.

8. Bulk Changed Map Page : It contains information about extents modified by bulk operations since the last BACKUP LOG statement per allocation unit.

9. Differential Changed Map Page : It contains information about extents that have changed since the last BACKUP DATABASE statement per allocation unit.

If you want more information about page architecture click here .



How to view page content :

We can view the page content by using DBCC PAGE , DBCC IND commands .

Syntax: 

DBCC PAGE

(

['database name'|database id], -- can be the actual name or id of the database

file number, -- the file number where the page is found

page number, -- the page number within the file

print option = [0|1|2|3] -- display option; each option provides differing levels of information

)

0 -  Page header

1 - page header plus per-row hex dumps

2 - page header plus whole page hex dump

3 - page header plus detailed per-row interpretation

Hex dump is a hexadecimal view (on screen or paper) of computer data, from RAM or from a file or storage device. 

Note : To view about DBCC PAGE we need to enable trace flag 3604 othser wise will get blank results .

Ex:

DBCC TRACEON(3604)

DBCC PAGE('ABC Database',1,143,3) WITH TABLERESULTS

Note : If you are not using with table results then you will see the results in an un documented format

you can use DBCC IND to list all of a table's data and index pages

Syntax:

DBCC IND

(

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

table name, -- the table name to list results

index id, -- an index_id from sys.indexes; -1 shows all indexes and IAMs, -2 just show IAMs

)

Ex:List data and index pages allocated to the xyz table

DBCC IND('ABC',XYZ,-1)