Pages and Extents

Pages and Extents

The fundamental unit of data storage in SQL Server is the page.  Disk I/O operations are performed at the page level. SQL Server reads or writes whole data pages.

Extents are a collection of eight physically contiguous pages and are used to efficiently manage the pages. 

Pages 

In SQL Server, the page size is 8 KB. Each page has number, starting from 0; the number of last page in database is determined by database file size.

1 Megabyte = 128 Pages

Each page begins with a 96-byte header that is used to store system information about the page. This information includes the page number, page type, the amount of free space on the page, and the allocation unit ID of the object that owns the page.  

• Page header stores information about the page like page type, next and previous page if it’s an index page, free space in the page etc.
• After the page header data row section follows. This is where you data is actually stored.
• Row offset information is stored at the end of the page i.e. after the data row section. Every data row has a row offset and the size of row offset is 2 bytes per row. Row offset stores information about how far the row is from the start of the page.

Putting in simple words the complete page equation comes as shown below.

Page (8192 bytes) = Page header(96 bytes) + Actual data(8060 bytes) + Row offset (32 bytes ).

 

 Youtube video which  demonstrates how 8 KB pages 

Extents

 

Extents are the basic unit in which space is managed. An extent is eight physically contiguous pages, or 64 KB. This means SQL Server databases have 16 extents per megabyte. 

To make its space allocation efficient, SQL Server does not allocate whole extents to tables with small amounts of data. 

SQL Server has two types of extents:

• Uniform extents are owned by a single object; all eight pages in the extent can only be used by the owning object.
• Mixed extents are shared by up to eight objects. Each of the eight pages in the extent can be owned by a different object.

A new table or index is generally allocated pages from mixed extents. When the table or index grows to the point that it has eight pages, it then switches to use uniform extents for subsequent allocations. If you create an index on an existing table that has enough rows to generate eight pages in the index, all allocations to the index are in uniform extents.

REFERENCES:

WEBSITES

Page In SQL Server on StackOverFlow

SQL Server Storage Engine: Allocation Maps 

[TOADWORLD] Data Pages and Extents 

BOOKS: 

Exam 70-432 Microsoft SQL Server 2008 Implementation and Maintenance