Database page and Zone

Now we will introduce how SQL Server 2008 R2 stores data.

The basic unit of data storage in SQL Server is page. Disk space allocated for data files. mdf or. ndf in the database can be logically divided into pages with numbers ranging from 0 to n ). Disk I/O operations are performed on pages. That is, SQL Server reads or writes data pages.

A zone is a set of eight consecutive physical pages used to effectively manage pages. All pages are stored in the zone.

 

In SQL Server, the page size is 8 KB. This means that each MB of the SQL Server database has 128 pages. Each page starts with a 96-byte header to store system information about the page. This information includes the page number, page type, available space of the page, and the allocation unit ID of the object that owns the page.

The following table describes the page types used in the data files of the SQL Server database.

Page type

Content

Data

WhenText in rowWhen set to ON, it contains all data rows except text, ntext, image, nvarchar (max), varchar (max), varbinary (max), and xml data.

Index

Index entries.

Text/Image

Large Object Data Type:

• Text, ntext, image, nvarchar (max), varchar (max), varbinary (max), and xml data.

Variable-length data type columns when the data row exceeds 8 KB:

• Varchar, nvarchar, varbinary, and SQL _variant

Global Allocation Map, Shared Global Allocation Map

Information about whether a zone is allocated.

Page Free Space

Information about page allocation and available space of the page.

Index Allocation Map

Information about the partitions used by tables or indexes in each allocation unit.

Bulk Changed Map

Information about the region modified by the large-capacity operation after the last backup log statement in each allocation unit.

Differential Changed Map

Information about the zones changed after the last backup database statement in each allocation unit.

650) this. width = 650; "title =" NOTE "alt =" NOTE "src =" http://www.bkjia.com/uploads/allimg/140207/214T91516-0.gif "/>Note:

A log file does not contain pages, but contains a series of log records.

On the data page, data rows are placed in sequence followed by headers. The end of the page is a row offset table. For each row on the page, each row Offset Table contains an entry. The distance between the first byte of the corresponding row and the header of each entry record. The order of entries in the row Offset Table is the opposite to that of the row on the page.

650) this. width = 650; "title =" SQL Server data page with row offset "alt =" SQL Server data page with row offset "src =" http://www.bkjia.com/uploads/allimg/140207/214T94192-1.gif "/>

Large Row Support

A row cannot span pages, but some of the rows can be moved to the page where the trip is located. Therefore, the row may be very large. The maximum data size and overhead of a single row of a page are 8,060 bytes (8 KB ). However, this does not include data stored in the Text/Image page type. Tables that contain the varchar, nvarchar, varbinary, or SQL _variant columns are not restricted by this restriction. When the total size of all rows of fixed and variable columns in the table exceeds the limit of 8,060 bytes, SQL Server dynamically moves one or more variable-length columns from the maximum-length column to the page in the ROW_OVERFLOW_DATA allocation unit. This operation is performed whenever an insert or update operation increases the total size of a row to 8,060 bytes exceeding the limit. After moving the column to the page in the ROW_OVERFLOW_DATA allocation unit, the 24-byte pointer is maintained on the original page in the IN_ROW_DATA allocation unit. If the row size is reduced in subsequent operations, SQL Server dynamically moves the column back to the original data page.

Zone

A zone is the basic unit for space management. One partition contains eight consecutive physical pages, namely 64 KB ). This means that each MB in the SQL Server database has 16 zones.

To make space allocation more effective, SQL Server does not distribute all tables that contain a small amount of data. SQL Server has two types of zones:

• Unified zone, owned by a single object. All 8 pages in the partition can only be used by the object.

• Hybrid zone, which can be shared by a maximum of eight objects. Each page of the eight pages in the partition can be owned by different objects.

Generally, a page is allocated to a new table or index in the hybrid zone. When the number of tables or indexes increases to 8 pages, the uniform partition will be used for subsequent allocation. If an index is created for an existing table and the table contains enough rows to generate eight pages in the index, all the indexes are allocated in a unified partition.

650) this. width = 650; "title =" hybrid zone and unified zone "alt =" hybrid zone and unified zone "src =" http://www.bkjia.com/uploads/allimg/140207/214T92J2-2.gif "/>