Page 53 - DCAP508_DATABASE_ADMINISTRATION

Unit 4: SQL Server Storage Architecture

Data rows are put on the page serially, starting immediately after the header. A row offset table Notes
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.

Figure 4.1

Large Row Support

Rows cannot span pages in SQL Server 2005, however portions of the row may be moved off the
row’s page so that the row can actually be very large. The maximum amount of data and
overhead that is contained in a single row on a page is 8,060 bytes (8 KB). However, this does not
include the data stored in the Text/Image page type. In SQL Server 2005, this restriction is
relaxed for tables that contain varchar, nvarchar, varbinary, or sql_variant columns. When the
total row size of all fixed and variable columns in a table exceeds the 8,060 byte limitation, SQL
Server dynamically moves one or more variable length columns to pages in the
ROW_OVERFLOW_DATA allocation unit, starting with the column with the largest width. This
is done whenever an insert or update operation increases the total size of the row beyond the
8060 byte limit. When a column is moved to a page in the ROW_OVERFLOW_DATA allocation
unit, a 24-byte pointer on the original page in the IN_ROW_DATA allocation unit is maintained.
If a subsequent operation reduces the row size, SQL Server dynamically moves the columns
back to the original data page.

4.2.2 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

LOVELY PROFESSIONAL UNIVERSITY 47