Sql server Page architecture

Whenever we are writing or reading the records from a table, it actually refers the 8kb chuck of size, called as page. The page is the building block of storage in Sql server. Data is stored in Sql server data files (.MDF and .NDF). Which are built by group of pages, logically speaking data is stored in pages. Each page is of 8 KB in size, unlike the page/block size in file systems, Sql server cannot change the page size.

Let’s see how the Data store the pages

Here I am creating a table and inserting some records.

create table employee ( EmpID int,   Name varchar(100),   EAdd varchar(1500),   Mobile varchar(10) )

Now I am inserting a record.

Let’s see where the data store in the pages. For seeing this we need to use of DBCC command.

DBCC (Database console commands)

DBCC statements are Database Console Commands and come in four flavors: Maintenance, Informational, Validation, and Miscellaneous. Maintenance commands are those commands that allow the DBA to perform maintenance activities on the database such as shrinking a file. Informational commands provide feedback regarding the database such as providing information about the procedure cache. Validation commands include commands that validate the database such as the ever-popular CHECKDB. Finally, miscellaneous commands are those that obviously don't fit in the other three categories. This includes statements like DBCC HELP, which provides the syntax for a given DBCC command.

See it details in next post

First we see the index allocation page using below script.

DBCC IND('Test',employee,-1) --Test – database name --employee –table name -- -1 it means see the all pages stored in IAM(Index allocation Map) page.

   

There are 14 types of pages in SQL Server.

Page Type	Page name
1	Data page
2	Index page
3	Text Mix page
4	Text page
7	Sort page
8	GAM (Global allocation map) page
9	SGAM(shared GAM) page
10	IAM page
11	PFS Page
13	Boot page
14	Server configuration page
15	File header page
16	Differential changed map
17	Bulk change map

Now I am seeing the page vales using below script

DBCC TRACEON(3604) DBCC Page('Test',4,9,1)

  

Executing this command

  

Basically page is divided on 3 parts

Ø  Page header (It is 96 bytes and stores the Meta data about page like page number, page type, the amount of free space, allocation unit ID.)

Ø  Data rows (It store the actual data)

Ø  Row offset (It is 36 bytes, stores the information about the data rows and address to next pages)

I this given example we can see that after inserting one record 8055 byte space is free

I means for one record take

For one record take 39 bytes. Let’s see I am inserting some more records

Now there are 16 records in a table. New see the free space on the page

  

See in the chart

For one record it takes 39 byes. Now think suppose we insert 208 records then what happen.

  

8094 byte store in 1 page and other 330 byte store in 2^nd page.

See two pages created.

  

188 records are store on the page 1 and remaining 28 records are store in 2^nd page.