PAGE and EXTENTS in SQL Server

Page and Extents are logical division of data storage in SQL Server. The data file (.mdf, .ndf) are logically divided into Pages numbering from 0…n. Disk I/O operations are performed at the page level. That is, 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. All pages are stored in extents.

PAGE: The page size is 8 KB. the page types used in the data files of a SQL Server database.

Page has three important sections:

• Page header
• Actual data i.e. Data row
• Row pointers or Row offset 

There are different types of Pages in SQL Server database.

Data	Data rows with all data except text, ntext, and image data.
Index	Index entries.
Text/Image	Text, ntext, and image data.
Global Allocation Map, Secondary Global Allocation Map	Information about allocated extents.
Page Free Space	Information about free space available on pages.
Index Allocation Map	Information about extents used by a table or index.
Bulk Changed Map	Information about extents modified by bulk operations since the last BACKUP LOG statement.
Differential Changed Map	Information about extents that have changed since the last BACKUP DATABASE statement.

Data pages contain all the data in data rows except text, ntext, andimage data, which is stored in separate pages. Data rows are placed serially on the page starting immediately after the header. A row offset table starts at the end of the page. The row offset table contains one entry for each row on the page and each entry records how far the first byte of the row is from the start of the page. The entries in the row offset table are in reverse sequence from the sequence of the rows on the page.

The maximum amount of data contained in a single row is 8060 bytes, not including text, ntext, and image data.

Data rows are put on the page serially, starting immediately after the header.

OFFSET TABLE: A row offset table starts at the end of the page, and each row offset table contains one entry for each row on the page. Each entry records how far the first byte of the row is from the start of the page. The entries in the row offset table are in reverse sequence from the sequence of the rows on the page.

EXTENTS: Extents are the basic unit in which space is allocated to tables and indexes. An extent is 8 contiguous pages, or 64 KB.

To make its space allocation efficient, SQL Server 2000 does not allocate entire extents to tables with small amounts of data. SQL Server 2000 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.

NOTE: Log files do not contain pages; they contain a series of log records.

Reference: http://technet.microsoft.com/en-us/library/aa174529(v=sql.80).aspx

Never fail to spot a dreadful piece code; it will offer idea of superlative choice to implement the same.

Post Reference: Vikram Aristocratic Elfin Share