Extents and pages

What are extents and pages in the context of SQL Server 2005 ? What are the different types extents and pages in SQL Server 2005 ?



An extent is a block of eight pages totaling 64KB in size. Because the extent is the basic unit of allocation for tables and indexes and all objects are saved in a table of some kind, all objects are stored in extents.



SQL Server has two types of extents:

» Uniform: In uniform extents, all eight pages are used by the same object.

» Mixed:Mixed extents are used by objects that are too small to take up eight pages, so more than one object is stored in the extent.



When a table or an index needs additional storage space, another extent is allocated to that object. A new extent will generally not be allocated for a table or index until all pages on that extent have been used. This process of allocating extents rather than individual pages to objects serves two useful purposes. First, the time-consuming process of allocation takes place in one batch rather than forcing each allocation to occur whenever a new page is needed. Second, it forces the pages allocated to an object to be at least somewhat contiguous. If pages were allocated directly, on an asneeded basis, then pages belonging to a single object would not be next to each other in the data file. Page 1 might belong to table 1, page 2 might belong to index 3, page 3 might belong to table 5, and so on. This is called fragmentation. Fragmentation can have a significant negative impact on performance. When pages for a single object are contiguous, though, reads and writes can occur much more quickly.



PAGES
At the most fundamental level, everything in SQL Server is stored on an 8KB page. The page is the one common denominator for all objects in SQL Server. Many types of pages exist, but every page has some factors in common. Pages are always 8KB in size and always have a header, leaving about 8,060 bytes of usable space on every page.



SQL Server has eight primary types of pages:

Data pages
Data pages hold the actual database records. The data page is 8,192 bytes, but only 8,060 of those bytes are available for data storage because a header at the beginning of each data page contains information about the page itself. Rows are not allowed to span more than one page, but if you have variable-length columns that exceed this limit, you can move them to a page in the ROW_OVERFLOW_DATA allocation unit.

Index pages
Index pages store the index keys and levels making up the entire index tree. Unlike data pages, you have no limit for the total number of entries you can make on an index page.

Text/image pages
Text and image pages hold the actual data associated with text, ntext, and image datatypes. When a text field is saved, the record will contain a 16-byte pointer to a linked list of text pages that hold the actual text data. Only the 16-byte pointer inside the record is counted against the 8,060-byte record-size limit.

Global Allocation Map pages
The Global Allocation Map (GAM) page type keeps track of which extents in a data file are allocated and which are still available.

Index Allocation Map pages
Index Allocation Map (IAM) pages keep track of what an extent is being used for—specifically, to which table or index the extent has been allocated.

Page Free Space pages
This is not an empty page; rather, it is a special type of page that keeps track of free space on all the other pages in the database. Each Page Free Space page can keep track of the amount of free space of up to 8,000 other pages.

Bulk Changed Map pages
This page contains information about other pages that have been modified by bulk operations (such as BULK INSERT) since the last BACKUP LOG statement.

Differential Changed Map pages
This page contains information about other pages that have changes since the last BACKUP DATABASE statement.