The storage architecture of the Database Engine contains several units for storing database objects:
- Page
- Extent
- File
- Filegroup
The main unit of data storage is the page. The size of a page is always 8KB. Each page has a 96-byte header used to store the system information. Data rows are placed on the page immediately after the header.
The Database Engine supports different page types. The most important are
- Data pages
- Index pages
When you create a table or index, the system allocates a fixed amount of space to contain the data belonging to the table or index. When the space fills, the space for additional storage must be allocated. The physical unit of storage in which space is allocated to a table (index) is called an extent. An extent comprises eight contiguous pages, or 64KB. There are two types of extents:
- Uniform extents
- Mixed extents
Uniform extents are owned by a single table or index, while mixed extents are shared by up to eight tables or indices. The system always allocates pages from mixed extents first. After that, if the size of the table (index) is greater than eight pages, it switches to uniform extents.
Properties of Data Pages
All types of data pages have a fixed size (8KB) and consist of the following three parts:
- Page header
- Space reserved for data
- Row offset table
The following sections describe these parts.
Page Header
Each page has a 96-byte page header used to store the system information, such as page ID, the ID of the database object to which the page belongs, and the previous and next page in a page chain. As you may have already guessed, the page header is stored at the beginning of each page. Table 15-1 shows the information stored in the page header.
| Page Header Information | Description |
|---|---|
| pageId | Database file ID plus the page ID |
| level | For index pages, the level of the page (leaf level is level 0, first intermediate level is level 1, and so on) |
| flagBits | Additional information concerning the page |
| nextPage | Database file ID plus the page ID of the next page in the chain (if a table has a clustered index) |
| prevPage | Database file ID plus the page ID of the previous page in the chain (if a table has a clustered index) |
| objId | ID of the database object to which the page belongs |
| lsn | Log sequence number (see Chapter “Concurrency Control”) |
| slotCnt | Total number of slots used on this page |
| indexId | Index ID of the page (0, if the page is a data page) |
| freeData | Byte offset of the first available free space on the page |
| pminlen | Number of bytes in fixed-length part of rows |
| freeCnt | Number of free bytes on page |
| reservedCnt | Number of bytes reserved by all transactions |
| xactReserved | Number of bytes reserved by the most recently started transaction |
| xactId | ID of the most recently started transaction |
| tornBits | One bit per sector for detecting torn page write |
Space Reserved for Data
The part of the page reserved for data has a variable length that depends on the number and length of rows stored on the page. For each row stored on the page, there is an entry in the space reserved for data and an entry in the row offset table at the end of the page. (A data row cannot span two or more pages, except for values of VARCHAR(max) and VARBINARY(max) data that are stored in their own specific pages.) Each row is stored subsequently after already-stored rows, until the page is filled. If there is not enough space for a new row of the same table, it is stored on the next page in the chain of pages.
For all tables that have only fixed-length columns, the same number of rows is stored at each page. If a table has at least one variable-length column (a VARCHAR column, for instance), the number of rows per page may differ and the system then stores as many rows per page as will fit on it.
Row Offset Table
The last part of a page is tightly connected to a space reserved for data, because each row stored on a page has a corresponding entry in the row offset table (see Figure 15-1). The row offset table contains 2-byte entries consisting of the row number and the offset byte address of the row on the page. (The entries in the row offset table are in reverse order from the sequence of the rows on the page.) Suppose that each row of a table is fixed-length, 36 bytes in length.
The first table row is stored at byte offset 96 of a page (because of the page header). The corresponding entry in the row offset table is written in the last 2 bytes of a page, indicating the row number (in the first byte) and the row offset (in the second byte). The next row is stored subsequently in the next 36 bytes of the page. Therefore, the corresponding entry in the row offset table is stored in the third- and fourth-to-last bytes of the page, indicating again the row number (1) and the row offset (132).
