Data Pages & Extents

Data Pages & Extents

How Indexes Are Stored

All SQL Server data is physically organized and stored in data pages. Unlike other relational database management systems, SQL Server does not provide the option of choosing the page size for different data types or tables. All SQL Server pages are 8KB in size. When you create primary or secondary database files, SQL Server allocates pages and extents for data storage. Each page has a number, starting from 0; the number of the last page in the database is determined by the database file size. When the database file size is increased through the ALTER DATABASE statement, new data pages are appended to the end of the file. Similarly if you shrink a database, then its data pages are removed, starting from the end of the file and gradually moving to the beginning.

Eight data pages (8KB each) make up a single extent. Therefore, each extent is 64KB. SQL Server has two classes of extents: uniform and mixed. Uniform extents are dedicated to a single object. Normally SQL Server allocates multiple uniform extents for each data table. However, if a table is small, SQL Server won't allocate an entire extent for it. Instead it will allocate data pages from a mixed extent, which can be thought of as a pool of pages for small tables. Each mixed extent can be shared by multiple tables. Since each extent has eight data pages, up-to eight objects can share data pages from a single mixed extent. When you first create a table SQL Server starts by allocating a data page to it from a mixed extent. Once the table has enough data to warrant a full extent, SQL Server will allocate a uniform extent to it. Similarly if you build an index on a table that has at least eight pages SQL Server will dedicate a uniform extent for storing the index data.

A set of pages used to hold table or index data within a single partition is referred to as an allocation unit. An allocation unit can be one of three types:

·         In-row data. This type holds column data with most data types, except data types for large objects.
 

·         Large object (LOB) data. This type holds large data type columns, such as TEXT, NTEXT, IMAGE, VARHCAR(MAX), NVARHCAR(MAX), XML and VARBINARY(MAX).
 

·         Row-overflow data. This type holds data for variable length columns that exceed the row size limit of 8060 bytes.

The Relationship Between Index Size and Page Size

A data record (or data row) within any SQL Server table is always stored on a single page. However, if the row contains large data types and all of its data won't fit on an 8KB page, then portions of the row (variable length columns, for example those with VARCHAR, VARBINARY, SQL_VARIANT data types) can be moved to data pages in row overflow data allocation unit. SQL Server maintains such row overflow data dynamically by adding a pointer to the original data page. If data in variable length columns is subsequently trimmed by an UPDATE statement and the total row size becomes less than 8060 bytes, SQL Server moves the variable length columns back to their original location. Note that the length of each column must still be less than 8000 characters; their combined length can exceed this limit. Note also, that such dynamic allocation of row overflow data comes with the performance penalty.

Normally you should try to keep the row length small enough to fit on a single page. If you have several variable length columns whose combined length will frequently exceed the 8060 byte limit, split the existing table into multiple tables that have one-to-one relationship with each other to ensure that the whole row can fit on a single data page. You cannot build a clustered index on a varying length column whose data is on row-overflow pages; however, you can include such columns as the key or non-key columns of non-clustered indexes.

Your table rows could contain large data, stored in TEXT, NTEXT, VARCHAR(MAX), VARBINARY(MAX), XML and IMAGE data types. Data rows with TEXT, NTEXT and IMAGE data type are stored outside of the normal data pages in special storage structures, allowing up to 2GB of storage. The data page contains a 16-byte pointer to the TEXT and IMAGE pages. Note that these data types are deprecated and will be removed from future versions of SQL Server. You should favor VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) columns for all new development. If you anticipate columns with TEXT, NTEXT or IMAGE data types to contain relatively small amount of data, you can advise SQL Server to attempt storing such content in row; as opposed to creating new TEXT and IMAGE pages. To do so you can use system procedure sp_tableoption with the 'text in row' option. If the data is indeed relatively small, then the storage will work the same way as it does with VARCHAR, NVARCHAR and VARBINARY data types. If the data can fit on a data page it is stored there; otherwise it is stored in row-overflow pages.

Columns built using large variable length data types (VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), XML) are normally stored on the data page and handled the same way as VARCHAR, NVARCHAR and VARBINARY data types. You could use sp_tableoption system procedure with 'large value types out of row' option and advise SQL Server to manage these data types same way as TEXT, NTEXT and IMAGE data types. If you do choose this option the data page will contain a 16-byte pointer to the TEXT and IMAGE pages.

Other Things Stored in the Index

In addition to storing data, all auxiliary information used for managing free space, tracking modified extents, allocating new pages and extents and so forth is also stored in pages. The following table summarizes different types of pages used by SQL Server:

Page Type	Description
Data	Stores all data except those data types stored by text and image pages.
Index	Stores index data.
Text and Image	Stores TEXT, NTEXT, IMAGE, VARCHAR(MAX), VARBINARY(MAX) and XML data types. In addition these pages also store variable length columns (VARCHAR, VARBINARY, SQL_VARIANT data types) once they exceed 8KB in size.
Global Allocation Map (GAM)	Records information about allocation of mixed and uniform extents. GAM pages contain bits that help SQL Server determine whether the extent is used or is available for use. When a new page needs to be allocated, SQL Server reads GAM pages to find whether any extents are available. When an extent is de-allocated, the bit for this extent is reset in GAM to make it available for future allocations.
Shared Global Allocation Map (SGAM)	Records information about mixed extents. SGAM pages record whether each mixed extent has any unused pages. If no mixed extent has any unused pages SQL Server attempts to find an unused extent in GAM pages. Once an extent is found it will be allocated as a mixed extent. If GAM has no free extents and SGAM shows that all extent pages have been allocated the data file is full and must be expanded before you can store additional data.
Page Free Space (PFS)	Keeps track of how full each data page is. In addition PFS also tracks the allocation status of each individual page. GAM bits are set so that SQL Server knows that the extent is allocated; SQL Server uses PFS pages to see which pages can be used for new allocation requests. The free space percentage is only maintained for tables without a clustered index (also called "heaps") and for TEXT / IMAGE pages. If the page has any free space it can be used for newly inserted rows.
Index Allocation Map (IAM)	Stores information about extents used by tables without a clustered index (also called "heaps") or indexes within a single allocation unit. IAM pages have a large bitmap, each bit representing an extent. When you execute an INSERT statement, SQL Server checks the extents currently allocated to the table which you wish to populate. SQL Server will use PFS pages to see if the row can be inserted into previously allocated page. It also uses IAM to determine which extents are allocated to the given partition (allocation unit). If no pages are found with enough room to store the row, SQL Server allocates a new extent.
Bulk Changed Map (BCM)	Stores information about extents altered by bulk operations such as Bulk Copy Program (BCP) or BULK INSERT statement, per allocation unit. BCM only tracks the extents modified since the last BACKUP LOG statement. BCM pages are only relevant for databases that use bulk-logged recovery model because with this model BACKUP LOG statement includes the modified extents in the log backup. Doing so allows the database to be recovered including the bulk operations. If the database uses the simple recovery model, then bulk operations aren't logged. The full recovery model treats bulk operations as fully logged commands. Bulk-logged recovery model logs only the fact that a bulk operation took place, but transaction log backup includes the copy of all extents that were modified through the bulk operation. The performance of bulk operations could be considerably faster if the database uses the bulk-logged model as compared to using the full recovery model, but the transaction log backup size can grow large. So although BCM pages exist in every database they're only used with databases using the bulk-logged recovery model.
Differential Changed Map (DCM)	Stores information about all extents that have been modified since the last full database backup was taken, per allocation unit. Differential backups scan the DCM pages and copy only those extents that were altered since the last full backup. This makes differential database backups considerably faster than full database backups.

Retrieving Database Page Metadata

You can use DBCC PAGE command to retrieve meta-data about a database page. Normally you should only execute this (undocumented) DBCC command when troubleshooting any issues with Microsoft support engineers. Before executing DBCC PAGE you need to execute DBCC TRACEON (3604) in order to return the output to the screen.