Page Types:

The most basic storage area is a page. Pages are used by SQL Server to store everything in the database.

Each page is created to use 8KB (8192 bytes) of space and they are numbered starting at 0 and incrementing 1 for every page allocated.

There are three primary components to a page: Header, Records, and Offset array.

Header: is 96 bytes and contains meta-information about the page.

Records  : Between these two areas are 8060 bytes where records are stored on the page.

offset array is 36 bytes and provides pointers to the byte location of the start of rows on the page.

Extents :There are two types of extents below  :

    Mixed Extent > Default

    Uniform Extent

Pages are grouped together eight at a time into structures called extents.

An extent is simply eight physically contiguous data pages in a data file

There are many ways in which a page can be used in the database.

Here’s an example, using a table with ten pages allocated – eight single-page allocations, and two from an extent dedicated to the object

CREATE DATABASE IAM_page;

GO

USE IAM_page;

GO

CREATE TABLE test (c1 INT IDENTITY, c2 CHAR (8000) DEFAULT 'a');

GO

-- Insert 10 recorsds, requiring 10 pages

INSERT INTO test DEFAULT VALUES;

GO 10

select * from test

DBCC IND ('IAM_page', 'test', -1);

DBCC TRACEON (3604);

GO

DBCC PAGE ('IAM_page', 1, 10, 3);

GO

DBCC TRACEOFF (3604);

GO