Page and District SQL Server

Original address: Http://msdn.microsoft.com/zh-cn/library/ms190969.aspx

The basic unit of data storage in SQL Server is the page. The disk space allocated for the data file (. mdf or. ndf) in the database can be logically divided into pages (numbered 0 through n consecutively). Disk I/O operations are performed at the page level. In other words, SQL Server reads or writes all data pages.

A zone is a collection of eight physically contiguous pages that are used to manage pages efficiently. All pages are stored in the zone.

page

In SQL Server, the size of the page is 8 KB. This means that there are 128 pages per megabyte in the SQL Server database. The beginning of each page is a 96-byte header that stores system information about the page. This information includes the page number, the page type, the available space for the page, and the allocation unit ID of the object that owns the page.

The following table describes the type of page used in the data file for the SQL Server database.

Page type	Content
Data	When the text in row is set to ON, contains except text, ntext,image,nvarchar (max),varchar (max),varbinary (max ) and data rows for all data outside of the XML data.
Index	The index entry.
Text/image	Large Object Data type: Text, ntext,image,nvarchar (max),varchar (max),varbinary (max), and XML data. A variable-length data type column when the data row exceeds 8 KB: varchar,nvarchar,varbinary and sql_variant
Global Allocation map, Shared Global Allocation map	Information about whether the zone is assigned.
Page Free Space	Information about page assignments and the available space for the page.
Index Allocation Map	Information about the extents used by the table or index in each allocation unit.
Bulk Changed Map	Information about the extents modified by bulk operations in each allocation unit since the last BACKUP LOG statement.
Differential Changed Map	Information about the extents in each allocation unit that were changed since the last BACKUP DATABASE statement.

Attention
The log file does not contain a page, but instead contains a series of log records.

On the data page, the data rows are placed in order, followed by the headers. The end of the page is the row offset table, and for each row in the page, each row offset table contains an entry. Each entry records the distance of the first byte of the corresponding row from the top of the page. The order of entries in the row offset table is reversed in the order of the rows in the page.

Large row Support

Rows cannot span pages, but the part of the row can move the page where the travel is located, so the row may actually be very large. The maximum amount of data and overhead in a single row of a page is 8,060 bytes (8 KB). However, this does not include data stored with the Text/image page type. Tables that contain varchar,nvarchar,varbinary, or sql_variant columns are not constrained by this restriction. When the total size of all fixed and mutable columns in a table exceeds the limit of 8,060 bytes, SQL Server dynamically moves one or more variable-length columns to the page in the Row_overflow_data allocation unit, starting at the maximum length of the column. This will be done whenever the insert or update operation increases the total size of the row to a limit of 8,060 bytes. When you move a column to a page in a Row_overflow_data allocation unit, a 24-byte pointer is maintained on the original page in the In_row_data allocation unit. If subsequent operations reduce the size of the rows, SQL Server dynamically moves the columns back to the original data page. For more information, see Row overflow data exceeds 8 KB.

Area

District is the basic unit of management space. A zone is eight physically contiguous pages (that is, ten KB). This means that there are 16 extents per megabyte in the SQL Server database.

To make space allocations more efficient, SQL Server does not differentiate all of the tables that contain a small amount of data. There are two types of SQL Server zones:

• A unified zone, owned by a single object. All 8 pages in the zone can only be used by the owning object.

• Mixed area, can be shared by up to eight objects. Each page of page eight in the zone can be owned by a different object.

Pages are typically assigned to a new table or index from a mixed zone. When a table or index grows to 8 pages, it becomes a subsequent assignment using a unified zone. If you create an index on an existing table and the table contains enough rows to generate 8 pages in the index, all allocations to that index are made using a uniform zone.

Page and District SQL Server