页和区体系结构指南Pages and Extents Architecture Guide

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse

SQL ServerSQL Server 中数据存储的基本单位是页。The page is the fundamental unit of data storage in SQL ServerSQL Server. 区是由八个物理上连续的页构成的集合。An extent is a collection of eight physically contiguous pages. 区有助于有效管理页。Extents help efficiently manage pages. 本指南介绍用于管理所有版本的 SQL Server 中的页和区的数据结构。This guide describes the data structures that are used to manage pages and extents in all versions of SQL Server. 要设计和开发高效执行的数据库,了解页和区的体系结构是很重要的。Understanding the architecture of pages and extents is important for designing and developing databases that perform efficiently.

页和区Pages and Extents

SQL ServerSQL Server 中数据存储的基本单位是页。The fundamental unit of data storage in SQL ServerSQL Server is the page. 为数据库中的数据文件(.mdf 或 .ndf)分配的磁盘空间可以从逻辑上划分成页(从 0 到 n 连续编号)。The disk space allocated to a data file (.mdf or .ndf) in a database is logically divided into pages numbered contiguously from 0 to n. 磁盘 I/O 操作在页级执行。Disk I/O operations are performed at the page level. 也就是说,SQL Server 读取或写入所有数据页。That is, SQL Server reads or writes whole data pages.

区是八个物理上连续的页的集合,用来有效地管理页。Extents are a collection of eight physically contiguous pages and are used to efficiently manage the pages. 所有页面都组织为盘区。All pages are organized into extents.

Pages

与常规书籍做类比:常规书籍中的所有内容都是写在书页上的。Take a regular book: all content in it is written on pages. 与书籍类似,SQL Server 所有数据行都写在页面上。Similar to a book, in SQL Server all the data rows are written on pages. 书中的所有页都具有相同的物理大小。In a book, all pages are the same physical size. 同样,SQL Server 所有数据页大小均相同 - 8 KB。Similarly, in SQL Server all data pages are the same size - 8 kilobytes. 书中的大多数页都包含数据(书的主要内容),某些页面包含有关内容的元数据(例如目录和索引)。In a book most pages contain the data - the main content of the book - and some pages contain metadata about the content - for example table of contents and index. SQL Server 也是如此:大多数页包含由用户存储的实际数据行;这些称为数据页面和文本/图像页面(在特殊情况下)。Again, SQL Server is not different: most pages contain actual rows of data which were stored by users; these are called Data pages and text/image pages (for special cases). 索引页包含有关数据位置的索引引用,最后有一些系统页,它们存储有关数据组织的各种元数据(PFS、GAM、SGAM、IAM、DCM、BCM 页)。The Index pages contain index references about where the data is and finally there are system pages that store variety of metadata about the organization of the data (PFS, GAM, SGAM, IAM, DCM, BCM pages). 请参阅下表了解页面类型及其说明。See table below for page types and their description.

如前所述,在 SQL ServerSQL Server 中,页的大小为 8-KB。As mentioned, in SQL ServerSQL Server, the page size is 8-KB. 这意味着 SQL ServerSQL Server 数据库中每 MB 有 128 页。This means SQL ServerSQL Server databases have 128 pages per megabyte. 每页的开头是 96 字节的标头,用于存储有关页的系统信息。Each page begins with a 96-byte header that is used to store system information about the page. 此信息包括页码、页类型、页的可用空间以及拥有该页的对象的分配单元 ID。This information includes the page number, page type, the amount of free space on the page, and the allocation unit ID of the object that owns the page.

下表说明了 SQL ServerSQL Server 数据库的数据文件中所使用的页类型。The following table shows the page types used in the data files of a SQL ServerSQL Server database.

页类型Page type 目录Contents
数据Data 当行中的文本设置为 ON 时,具有除 text、ntext、image、nvarchar(max)、varchar(max)、varbinary(max) 和 xml 数据以外的所有数据的数据行。Data rows with all data, except text, ntext, image, nvarchar(max), varchar(max), varbinary(max), and xml data, when text in row is set to ON.
索引Index 索引条目。Index entries.
Text/ImageText/Image 大型对象数据类型:(text、ntext、image、nvarchar(max)、varchar(max)、varbinary(max) 和 xml 数据)Large object data types: (text, ntext, image, nvarchar(max), varchar(max), varbinary(max), and xml data)
数据行超过 8 KB 时为可变长度数据类型列:(varchar、nvarchar、varbinary 和 sql_variant)Variable length columns when the data row exceeds 8 KB: (varchar, nvarchar, varbinary, and sql_variant)
Global Allocation Map、Shared Global Allocation MapGlobal Allocation Map, Shared Global Allocation Map 有关区是否分配的信息。Information about whether extents are allocated.
页可用空间 (PFS)Page Free Space (PFS) 有关页分配和页的可用空间的信息。Information about page allocation and free space available on pages.
索引分配映射 (Index Allocation Map)Index Allocation Map 有关每个分配单元中表或索引所使用的区的信息。Information about extents used by a table or index per allocation unit.
大容量更改映射表Bulk Changed Map 有关每个分配单元中自最后一条 BACKUP LOG 语句之后的大容量操作所修改的区的信息。Information about extents modified by bulk operations since the last BACKUP LOG statement per allocation unit.
差异更改映射表Differential Changed Map 有关每个分配单元中自最后一条 BACKUP DATABASE 语句之后更改的区的信息。Information about extents that have changed since the last BACKUP DATABASE statement per allocation unit.

备注

日志文件不包含页,而是包含一系列日志记录。Log files do not contain pages; they contain a series of log records.

在数据页上,数据行紧接着标头按顺序放置。Data rows are put on the page serially, starting immediately after the header. 页的末尾是行偏移表,对于页中的每一行,每个行偏移表都包含一个条目。A row offset table starts at the end of the page, and each row offset table contains one entry for each row on the page. 每个行偏移量条目记录对应行的第一个字节与页首的距离。Each row offset entry records how far the first byte of the row is from the start of the page. 因此,行偏移量的功能有助于 SQL Server 快速在页面上定位行。Thus, the function of the row offset table is to help SQL Server locate rows on a page very quickly. 行偏移表中的条目的顺序与页中行的顺序相反。The entries in the row offset table are in reverse sequence from the sequence of the rows on the page.

page_architecture

大型行支持Large Row Support

行不能跨页,但是行的部分可以移出行所在的页,因此行实际可能非常大。Rows cannot span pages, however portions of the row may be moved off the row's page so that the row can actually be very large. 页的单个行中的最大数据量和开销是 8,060 字节 (8-KB)。The maximum amount of data and overhead that is contained in a single row on a page is 8,060 bytes (8-KB). 但是,这不包括用 Text/Image 页类型存储的数据。However, this does not include the data stored in the Text/Image page type.

对于包含 varchar、nvarchar、varbinary 或 sql_variant 列的表,可以放宽此限制。This restriction is relaxed for tables that contain varchar, nvarchar, varbinary, or sql_variant columns. 当表中的所有固定列和可变列的行的总大小超过限制的 8,060 字节时,SQL ServerSQL Server 将从最大长度的列开始以动态方式将一个或多个可变长度列移动到 ROW_OVERFLOW_DATA 分配单元中的页。When the total row size of all fixed and variable columns in a table exceeds the 8,060-byte limitation, SQL ServerSQL 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.

每当插入或更新操作将行的总大小增大到超过限制的 8,060 字节时,将会执行此操作。This is done whenever an insert or update operation increases the total size of the row beyond the 8,060-byte limit. 将列移动到 ROW_OVERFLOW_DATA 分配单元中的页后,将在 IN_ROW_DATA 分配单元中的原始页上维护 24 字节的指针。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. 如果后续操作减小了行的大小,SQL ServerSQL Server 会动态将列移回到原始数据页。If a subsequent operation reduces the row size, SQL ServerSQL Server dynamically moves the columns back to the original data page.

行溢出注意事项Row-Overflow Considerations

如前所述,如果可变长度数据类型字段的组合大小超过 8060 字节的限制,则行不能驻留在多个页面上并且可能溢出。As mentioned earlier, a row cannot reside on multiple pages and can overflow if the combined size of variable-length data-type fields exceeds the 8060-byte limit. 举例说明,可以使用两列创建一个表:一个 varchar(7000),另一个 varchar(2000)。To illustrate, a table may be created with two columns: one varchar(7000) and another varchar (2000). 每列都不会超过 8060 字节,但如果每列的整个宽度被填充,它们会合并在一起,这就超过了该限制。Individually neither column exceeds the 8060-byte, but combined they could do so, if the entire width of each column is filled. SQL Server 可以将 varchar(7000) 可变长列动态移动到 ROW_OVERFLOW_DATA 分配单元中的页面。SQL Server may dynamically move the varchar(7000) variable length column to pages in the ROW_OVERFLOW_DATA allocation unit. 合并每行超过 8,060 字节的 varchar、nvarchar、varbinary、sql_variant 或 CLR 用户定义类型的列时,请注意下列事项:When you combine varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns that exceed 8,060 bytes per row, consider the following:

  • 如果更新操作使记录变长,大型记录将被动态移动到另一页。Moving large records to another page occurs dynamically as records are lengthened based on update operations. 如果更新操作使记录变短,记录可能会移回 IN_ROW_DATA 分配单元中的原始页。Update operations that shorten records may cause records to be moved back to the original page in the IN_ROW_DATA allocation unit. 执行查询和其他选择操作(例如,对包含行溢出数据的大型记录进行排序或合并)将延长处理时间,因为这些记录将同步处理,而不是异步处理。Querying and performing other select operations, such as sorts or joins on large records that contain row-overflow data slows processing time, because these records are processed synchronously instead of asynchronously.
    因此,当要设计的表中包含多个 varchar、nvarchar、varbinary、sql_variant 或 CLR 用户定义类型的列时,请考虑可能溢出的行的百分比,以及可能查询这些溢出数据的频率。Therefore, when you design a table with multiple varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns, consider the percentage of rows that are likely to flow over and the frequency with which this overflow data is likely to be queried. 如果可能需要经常查询行溢出数据中的许多行,请考虑对表格进行规范化处理,以使某些列移动到另一个表中。If there are likely to be frequent queries on many rows of row-overflow data, consider normalizing the table so that some columns are moved to another table. 然后可以在异步 JOIN 操作中执行查询。This can then be queried in an asynchronous JOIN operation.
  • 对于 varchar、nvarchar、varbinary、sql_variant 或 CLR 用户定义类型的列,单个列的长度仍然必须在 8,000 字节的限制之内。The length of individual columns must still fall within the limit of 8,000 bytes for varchar, nvarchar, varbinary, sql_variant, and CLR user-defined type columns. 只有它们的合并长度可以超过表的 8,060 字节的行限制。Only their combined lengths can exceed the 8,060-byte row limit of a table.
  • 其他数据类型列的和(包括 char 和 nchar 数据)必须在 8,060 字节的行限制之内。The sum of other data type columns, including char and nchar data, must fall within the 8,060-byte row limit. 大型对象数据也不受 8,060 字节行限制的制约。Large object data is also exempt from the 8,060-byte row limit.
  • 聚集索引的索引键不能包含在 ROW_OVERFLOW_DATA 分配单元中具有现有数据的 varchar 列。The index key of a clustered index cannot contain varchar columns that have existing data in the ROW_OVERFLOW_DATA allocation unit. 如果对 varchar 列创建了聚集索引,并且 IN_ROW_DATA 分配单元中存在现有数据,则对该列执行的将数据推送到行外的后续插入或更新操作将会失败。If a clustered index is created on a varchar column and the existing data is in the IN_ROW_DATA allocation unit, subsequent insert or update actions on the column that would push the data off-row will fail. 有关分配单元的详细信息,请参阅表和索引组织。For more information about allocation units, see Table and Index Organization.
  • 可以包括包含行溢出数据的列,作为非聚集索引的键列或非键列。You can include columns that contain row-overflow data as key or nonkey columns of a nonclustered index.
  • 对于使用稀疏列的表,记录大小限制为 8,018 字节。The record-size limit for tables that use sparse columns is 8,018 bytes. 转换后的数据加上现有记录数据超过 8,018 字节时,会返回 MSSQLSERVER ERROR 576When the converted data plus existing record data exceeds 8,018 bytes, MSSQLSERVER ERROR 576 is returned. 在稀疏和非稀疏类型之间转换列时,数据库引擎会保存当前记录数据的副本。When columns are converted between sparse and nonsparse types, Database Engine keeps a copy of the current record data. 这样,记录所需的存储会临时加倍。This temporarily doubles the storage that is required for the record.
  • 若要获得有关可能包含行溢出数据的表或索引的信息,请使用 sys.dm_db_index_physical_stats 动态管理函数。To obtain information about tables or indexes that might contain row-overflow data, use the sys.dm_db_index_physical_stats dynamic management function.

ExtentsExtents

区是管理空间的基本单位。Extents are the basic unit in which space is managed. 一个区是八个物理上连续的页(即 64 KB)。An extent is eight physically contiguous pages, or 64 KB. 这意味着 SQL Server 数据库中每兆字节有 16 个区。This means SQL Server databases have 16 extents per megabyte.

SQL ServerSQL 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.

统一盘区和混合盘区

一直到(并且包括)SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL ServerSQL Server 不会将所有盘区分配给包含少量数据的表。Up to, and including, SQL Server 2014 (12.x)SQL Server 2014 (12.x), SQL ServerSQL Server does not allocate whole extents to tables with small amounts of data. 新表或索引通常从混合区分配页。A new table or index generally allocates pages from mixed extents. 当表或索引增长到 8 页时,将变成使用统一区进行后续分配。When the table or index grows to the point that it has eight pages, it then switches to use uniform extents for subsequent allocations. 如果对现有表创建索引,并且该表包含的行足以在索引中生成 8 页,则对该索引的所有分配都使用统一区进行。If you create an index on an existing table that has enough rows to generate eight pages in the index, all allocations to the index are in uniform extents.

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始,用户数据库和 tempdb 中大多数分配的默认值都是使用统一盘区,但属于 IAM 链的前八页的分配除外。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), the default for most allocations in a user database and tempdb is to use uniform extents, except for allocations belonging to the first eight pages of an IAM chain. master、msdb 和 model 数据库的分配仍保留以前的行为。Allocations for master, msdb, and model databases still retain the previous behavior.

备注

一直到,并且包括 SQL Server 2014 (12.x)SQL Server 2014 (12.x),跟踪标志 1118 可用于将默认分配更改为始终使用统一区。Up to, and including, SQL Server 2014 (12.x)SQL Server 2014 (12.x), trace flag 1118 can be used to change the default allocation to always use uniform extents. 有关此跟踪标志的详细信息,请参阅 DBCC TRACEON - 跟踪标志For more information about this trace flag, see DBCC TRACEON - Trace Flags.

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始,将为 tempdb 和所有用户数据库自动启用 TF 1118 提供的功能。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), the functionality provided by TF 1118 is automatically enabled for tempdb and all user databases. 对于用户数据库,此行为受 ALTER DATABASESET MIXED_PAGE_ALLOCATION 选项控制,同时默认值设置为禁用,且跟踪标志 1118 无效。For user databases, this behavior is controlled by the SET MIXED_PAGE_ALLOCATION option of ALTER DATABASE, with the default value set to OFF, and trace flag 1118 has no effect. 有关详细信息,请参阅 ALTER DATABASE SET 选项 (Transact-SQL)For more information, see ALTER DATABASE SET Options (Transact-SQL).

SQL Server 2012 (11.x)SQL Server 2012 (11.x) 开始,sys.dm_db_database_page_allocations 系统函数可以报告数据库、表、索引和分区的页分配信息。Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x), the sys.dm_db_database_page_allocations system function can report page allocation information for a database, table, index, and partition.

重要

未记录 sys.dm_db_database_page_allocations 系统函数,并且可能会发生更改。The sys.dm_db_database_page_allocations system function is not documented and is subject to change. 不保证兼容性。Compatibility is not guaranteed.

SQL Server 2019 (15.x)SQL Server 2019 (15.x) 开始,sys.dm_db_page_info 系统函数可用,并返回有关数据库中的页的信息。Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x), the sys.dm_db_page_info system function is available and returns information about a page in a database. 该函数将返回包含页中标头信息的一行,包括 object_id、index_id 和 partition_id。The function returns one row that contains the header information from the page, including the object_id, index_id, and partition_id. 在大多数情况下,此函数取代了使用 DBCC PAGE 的需要。This function replaces the need to use DBCC PAGE in most cases.

管理区分配和可用空间Managing Extent Allocations and Free Space

用来管理盘区分配情况并跟踪可用空间的 SQL ServerSQL Server 数据结构相对而言比较简单。The SQL ServerSQL Server data structures that manage extent allocations and track free space have a relatively simple structure. 该功能有以下优点:This has the following benefits:

  • 可用空间信息被紧密压缩,因此包含此信息的页相对较少。The free space information is densely packed, so relatively few pages contain this information.
    这样,可提高速度,因为它减少了检索分配信息时所需的磁盘读取量。This increases speed by reducing the amount of disk reads that are required to retrieve allocation information. 同时还可增加分配页保留在内存中的机会并且不需要更多的读操作。This also increases the chance that the allocation pages will remain in memory and not require more reads.

  • 大多数分配信息不是链在一起的。Most of the allocation information is not chained together. 这就简化了对分配信息的维护。This simplifies the maintenance of the allocation information.
    可以快速执行每个页的分配或释放。Each page allocation or deallocation can be performed quickly. 这将减少需要分配页或释放页的并发任务之间的争用。This decreases the contention between concurrent tasks having to allocate or deallocate pages.

管理区分配Managing Extent Allocations

SQL ServerSQL Server 使用两种类型的分配映射表来记录盘区的分配:uses two types of allocation maps to record the allocation of extents:

  • 全局分配映射表 (GAM) Global Allocation Map (GAM)
    GAM 页记录已分配的区。GAM pages record what extents have been allocated. 每个 GAM 包含 64,000 个区,相当于近 4 GB 的数据。Each GAM covers 64,000 extents, or almost 4 gigabytes (GB) of data. GAM 用 1 个位来表示所涵盖区间内的每个区的状态。The GAM has 1-bit for each extent in the interval it covers. 如果位为 1,则区可用;如果位为 0,则区已分配。If the bit is 1, the extent is free; if the bit is 0, the extent is allocated.

  • 共享全局分配映射表 (SGAM) Shared Global Allocation Map (SGAM)
    SGAM 页记录当前用作混合区且至少有一个未使用的页的区。SGAM pages record which extents are currently being used as mixed extents and also have at least one unused page. 每个 SGAM 包含 64,000 个区,相当于近 4-GB 的数据。Each SGAM covers 64,000 extents, or almost 4-GB of data. SGAM 用 1 个位来表示所涵盖区间内的每个区的状态。The SGAM has 1-bit for each extent in the interval it covers. 如果位为 1,则区正用作混合区且有可用页。If the bit is 1, the extent is being used as a mixed extent and has a free page. 如果位为 0,则区未用作混合区,或者虽然用作混合区但其所有页均在使用中。If the bit is 0, the extent is not used as a mixed extent, or it is a mixed extent and all its pages are being used.

根据区当前的使用情况,GAM 和 SGAM 中每个区具有以下位模式。Each extent has the following bit patterns set in the GAM and SGAM, based on its current use.

区的当前使用情况Current use of extent GAM 位设置GAM bit setting SGAM 位设置SGAM bit setting
可用,未使用Free, not being used 11 00
统一区或已满的混合区Uniform extent, or full mixed extent 00 00
具有可用页的混合区Mixed extent with free pages 00 11

这将简化区管理算法。This causes simple extent management algorithms.

  • 若要分配统一区,SQL Server 数据库引擎SQL Server Database Engine将搜索 GAM 以查找为 1 的位并将其设置为 0。To allocate a uniform extent, the SQL Server 数据库引擎SQL Server Database Engine searches the GAM for a 1 bit and sets it to 0.
  • 为了查找具有可用页的混合区,SQL Server 数据库引擎SQL Server Database Engine搜索 SGAM 以查找为 1 的位。To find a mixed extent with free pages, the SQL Server 数据库引擎SQL Server Database Engine searches the SGAM for a 1 bit.
  • 若要分配混合区,SQL Server 数据库引擎SQL Server Database Engine在 GAM 中搜索为 1 的位,并将其设置为 0;然后将 SGAM 中的对应位设置为 1。To allocate a mixed extent, the SQL Server 数据库引擎SQL Server Database Engine searches the GAM for a 1 bit, sets it to 0, and then also sets the corresponding bit in the SGAM to 1.
  • 若取消分配某个区,SQL Server 数据库引擎SQL Server Database Engine确保 GAM 位设置为 1,而 SGAM 位设置为 0。To deallocate an extent, the SQL Server 数据库引擎SQL Server Database Engine makes sure that the GAM bit is set to 1 and the SGAM bit is set to 0. SQL Server 数据库引擎SQL Server Database Engine 在内部实际使用的算法比本文介绍的内容更复杂,因为 SQL Server 数据库引擎SQL Server Database Engine 在数据库中平均分布数据。The algorithms that are actually used internally by the SQL Server 数据库引擎SQL Server Database Engine are more sophisticated than what is described in this article, because the SQL Server 数据库引擎SQL Server Database Engine distributes data evenly in a database. 但是,由于无需管理区分配信息链,因此即使是实际算法也会被简化。However, even the real algorithms are simplified by not having to manage chains of extent allocation information.

跟踪可用空间Tracking free space

“页可用空间 (PFS)”页记录每页的分配状态,是否已分配单个页以及每页的可用空间量。Page Free Space (PFS) pages record the allocation status of each page, whether an individual page has been allocated, and the amount of free space on each page. PFS 对每页都有 1 个字节,记录该页是否已分配。如果已分配,则记录该页是为空、已满 1% 到 50%、已满 51% 到 80%、已满 81% 到 95% 还是已满 96% 到 100%。The PFS has 1-byte for each page, recording whether the page is allocated, and if so, whether it is empty, 1 to 50 percent full, 51 to 80 percent full, 81 to 95 percent full, or 96 to 100 percent full.

将区分配给对象后,SQL Server 数据库引擎SQL Server Database Engine将使用 PFS 页来记录区中的哪些页已分配或哪些页可用。After an extent has been allocated to an object, the SQL Server 数据库引擎SQL Server Database Engine uses the PFS pages to record which pages in the extent are allocated or free. SQL Server 数据库引擎SQL Server Database Engine必须分配新页时,将使用此信息。This information is used when the SQL Server 数据库引擎SQL Server Database Engine has to allocate a new page. 保留的页中的可用空间量仅用于堆和 Text/Image 页。The amount of free space in a page is only maintained for heap and Text/Image pages. SQL Server 数据库引擎SQL Server Database Engine必须找到一个具有可用空间的页来保存新插入的行时,使用此信息。It is used when the SQL Server 数据库引擎SQL Server Database Engine has to find a page with free space available to hold a newly inserted row. 索引不要求跟踪页的可用空间,因为插入新行的点是由索引键值设置的。Indexes do not require that the page free space be tracked, because the point at which to insert a new row is set by the index key values.

它将在数据文件中按各自的区域间隔添加一个新的 PFS、GAM 或 SGAM 页面。A new PFS, GAM or SGAM page is added in the data file for every additional range that it keeps track of. 因此,将出现一个新的 PFS 页面,在第一个 PFS 页面之后为 8,088 页,在间隔 8,088 页后为另一个 PFS 页面。Thus, there is a new PFS page 8,088 pages after the first PFS page, and additional PFS pages in subsequent 8,088 page intervals. 举例说明,第 1 页为 PFS 页面,第 8088 页为 PFS 页面,第 16176 页为 PFS 页面,以此类推。To illustrate, page ID 1 is a PFS page, page ID 8088 is a PFS page, page ID 16176 is a PFS page, and so on. 也将出现一个新的 GAM 页面,在第一个 GAM 页面之后为 64,000 区,在间隔 64,000 区后为另一个 GAM 页面,以此类推。There is a new GAM page 64,000 extents after the first GAM page and it keeps track of the 64,000-extents following it; the sequence continues at 64,000-extent intervals. 同样,将出现一个新的 SGAM 页面,在第一个 SGAM 页面之后为 64,000 区,在间隔 64,000 区后为另一个 SGAM 页面。Similarly, there is a new SGAM page 64,000 extents after the first SGAM page and additional SGAM pages in subsequent 64,000 extent intervals. 下图显示了SQL Server 数据库引擎SQL Server Database Engine用来分配和管理区的页顺序。The following illustration shows the sequence of pages used by the SQL Server 数据库引擎SQL Server Database Engine to allocate and manage extents.

manage_extents

管理对象使用的空间Managing space used by objects

“索引分配映射 (IAM)”页将映射分配单元使用的数据库文件中 4-GB 部分中的盘区。An Index Allocation Map (IAM) page maps the extents in a 4-GB part of a database file used by an allocation unit. 分配单元有下列三种类型:An allocation unit is one of three types:

  • IN_ROW_DATAIN_ROW_DATA
    用于存储堆分区或索引分区。Holds a partition of a heap or index.

  • LOB_DATALOB_DATA
    包含大型对象 (LOB) 数据类型,如 XML、VARBINARY(max) 和 VARCHAR(max)。Holds large object (LOB) data types, such as XML, VARBINARY(max), and VARCHAR(max).

  • ROW_OVERFLOW_DATAROW_OVERFLOW_DATA
    包含超过 8,060 字节行大小限制的 VARCHAR、NVARCHAR、VARBINARY 或 SQL_VARIANT 列中存储的可变长度数据。Holds variable length data stored in VARCHAR, NVARCHAR, VARBINARY, or SQL_VARIANT columns that exceed the 8,060 byte row size limit.

堆或索引的每个分区至少包含一个 IN_ROW_DATA 分配单元。Each partition of a heap or index contains at least an IN_ROW_DATA allocation unit. 根据堆或索引的架构,可能还包含一个 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元。It may also contain a LOB_DATA or ROW_OVERFLOW_DATA allocation unit, depending on the heap or index schema.

一个 IAM 页在文件中的范围为 4 GB,与 GAM 或 SGAM 页的范围相同。An IAM page covers a 4-GB range in a file and is the same coverage as a GAM or SGAM page. 如果分配单元包含来自多个文件的区,或者超过一个文件的 4 GB 范围,那么一个 IAM 链中将链接多个 IAM 页。If the allocation unit contains extents from more than one file, or more than one 4-GB range of a file, there will be multiple IAM pages linked in an IAM chain. 因此,每个分配单元在有区的每个文件中至少有一个 IAM 页。Therefore, each allocation unit has at least one IAM page for each file on which it has extents. 如果分配给分配单元的文件中的区的范围超过了一个 IAM 页能够记录的范围,一个文件中也可能会有多个 IAM 页。There may also be more than one IAM page on a file, if the range of the extents on the file allocated to the allocation unit exceeds the range that a single IAM page can record.

iam_pages

IAM 页根据需要分配给每个分配单元,在文件中的位置也是随机的。IAM pages are allocated as required for each allocation unit and are located randomly in the file. sys.system_internals_allocation_units 系统视图指向分配单元的第一个 IAM 页。The sys.system_internals_allocation_units system view points to the first IAM page for an allocation unit. 该分配单元的所有 IAM 页都链接到一个 IAM 链中。All the IAM pages for that allocation unit are linked in an IAM chain.

重要

sys.system_internals_allocation_units 系统视图仅供内部使用,随时可能更改。The sys.system_internals_allocation_units system view is for internal use only and is subject to change. 不保证兼容性。Compatibility is not guaranteed. 此视图在 Azure SQL 数据库Azure SQL Database中不可用。This view is not available in Azure SQL 数据库Azure SQL Database.

iam_chain

每个分配单元的链中所链接的 IAM 页 IAM 页有一个标头,指明 IAM 页所映射的区范围的起始区。IAM pages linked in a chain per allocation unit An IAM page has a header that indicates the starting extent of the range of extents mapped by the IAM page. IAM 页中还有一个大位图,其中每个位代表一个区。The IAM page also has a large bitmap in which each bit represents one extent. 位图中的第一个位代表范围内的第一个区,第二个位代表第二个区,依此类推。The first bit in the map represents the first extent in the range, the second bit represents the second extent, and so on. 如果某个位是 0,它所代表的区将不会分配给拥有该 IAM 页的分配单元。If a bit is 0, the extent it represents is not allocated to the allocation unit owning the IAM. 如果这个位是 1,它所代表的区将被分配给拥有该 IAM 页的分配单元。If the bit is 1, the extent it represents is allocated to the allocation unit owning the IAM page.

SQL Server 数据库引擎SQL Server Database Engine必须在当前页中插入新行,而当前页中没有可用空间时,它将使用 IAM 和 PFS 页查找要将该行分配到的页,或者(对于堆或 Text/Image 页)查找具有足够空间容纳该行的页。When the SQL Server 数据库引擎SQL Server Database Engine has to insert a new row and no space is available in the current page, it uses the IAM and PFS pages to find a page to allocate, or, for a heap or a Text/Image page, a page with sufficient space to hold the row. SQL Server 数据库引擎SQL Server Database Engine使用 IAM 页查找分配给分配单元的区。The SQL Server 数据库引擎SQL Server Database Engine uses the IAM pages to find the extents allocated to the allocation unit. 对于每个区,SQL Server 数据库引擎SQL Server Database Engine将搜索 PFS 页,以查看是否有可用的页。For each extent, the SQL Server 数据库引擎SQL Server Database Engine searches the PFS pages to see if there is a page that can be used. 每个 IAM 和 PFS 页覆盖大量数据页,因此一个数据库内只有很少的 IAM 和 PFS 页。Each IAM and PFS page covers lots of data pages, so there are few IAM and PFS pages in a database. 这意味着 IAM 和 PFS 页通常位于内存中的 SQL ServerSQL Server 缓冲池中,所以能够很快找到它们。This means that the IAM and PFS pages are generally in memory in the SQL ServerSQL Server buffer pool, so they can be searched quickly. 对于索引,新行的插入点由索引键设置,但是当需要新页面时,将发生先前描述的过程。For indexes, the insertion point of a new row is set by the index key, but when a new page is needed, the previously described process occurs.

仅当 SQL Server 数据库引擎SQL Server Database Engine不能在现有的区中快速找到足以容纳插入行的页时,才将新区分配给分配单元。The SQL Server 数据库引擎SQL Server Database Engine allocates a new extent to an allocation unit only when it cannot quickly find a page in an existing extent with sufficient space to hold the row being inserted.

SQL Server 数据库引擎SQL Server Database Engine 使用“比例填充分配算法”从文件组的可用盘区中分配盘区。The SQL Server 数据库引擎SQL Server Database Engine allocates extents from those available in the filegroup using a proportional fill allocation algorithm. 如果同一文件组内有两个文件,而一个文件的可用空间是另一个文件的两倍,那么每从后一个文件分配一页,就从前一个文件分配两页。If in the same filegroup with two files, one file has two times the free space as the other, two pages will be allocated from the file with the available space for every one page allocated from the other file. 这意味着文件组内的每个文件应该有近似的空间使用百分比。This means that every file in a filegroup should have a similar percentage of space used.

跟踪已修改的区Tracking Modified Extents

SQL ServerSQL Server 使用两个内部数据结构跟踪被大容量复制操作修改的盘区,以及自上次完整备份后修改的盘区。uses two internal data structures to track extents modified by bulk copy operations and extents modified since the last full backup. 这些数据结构极大地加快了差异备份的速度。These data structures greatly speed up differential backups. 当数据库使用大容量日志恢复模式时,这些数据结构也可以加快将大容量复制操作记录至日志的速度。They also speed up the logging of bulk copy operations when a database is using the bulk-logged recovery model. 与全局分配图 (GAM) 和共享全局分配图 (SGAM) 页相同,这些结构也是位图,其中的每一位代表一个单独的区。Like the Global Allocation Map (GAM) and Shared Global Allocation Map (SGAM) pages, these structures are bitmaps in which each bit represents a single extent.

  • 差异更改映射表 (DCM) Differential Changed Map (DCM)
    这样便可跟踪自上次执行 BACKUP DATABASE 语句后更改过的盘区。This tracks the extents that have changed since the last BACKUP DATABASE statement. 如果扩展盘区的位是 1,则自上次执行 BACKUP DATABASE 语句后扩展盘区已被修改。If the bit for an extent is 1, the extent has been modified since the last BACKUP DATABASE statement. 如果位是 0,则扩展盘区没有被修改。If the bit is 0, the extent has not been modified. 差异备份只读取 DCM 页便可以确定已修改的区。Differential backups read just the DCM pages to determine which extents have been modified. 这样大大减少了差异备份必须扫描的页数。This greatly reduces the number of pages that a differential backup must scan. 运行差异备份所需的时间与自上次执行 BACKUP DATABASE 语句之后修改的区数成正比,而不是与整个数据库的大小成正比。The length of time that a differential backup runs is proportional to the number of extents modified since the last BACKUP DATABASE statement and not the overall size of the database.

  • 大容量更改映射表 (BCM) Bulk Changed Map (BCM)
    跟踪自上次执行 BACKUP LOG 语句后,被大容量日志记录操作修改的盘区。This tracks the extents that have been modified by bulk logged operations since the last BACKUP LOG statement. 如果某个扩展盘区的位是 1,表明自上次执行 BACKUP LOG 语句后,该扩展盘区已经被有日志记录的大容量复制操作修改。If the bit for an extent is 1, the extent has been modified by a bulk logged operation after the last BACKUP LOG statement. 如果位是 0,则该扩展盘区未被有日志记录的大容量复制操作修改。If the bit is 0, the extent has not been modified by bulk logged operations. 尽管所有数据库中都显示 BCM 页,但只有在数据库使用大容量日志记录恢复模式时,才会与 BCM 页有关。Although BCM pages appear in all databases, they are only relevant when the database is using the bulk-logged recovery model. 在此恢复模式中,当执行 BACKUP LOG 时,备份进程将扫描 BCM 查找已经修改的盘区。In this recovery model, when a BACKUP LOG is performed, the backup process scans the BCMs for extents that have been modified. 然后,将那些区包括在日志备份中。It then includes those extents in the log backup. 如果数据库从数据库备份和一系列事务日志备份恢复,便可以恢复大容量日志记录操作。This lets the bulk logged operations be recovered if the database is restored from a database backup and a sequence of transaction log backups. 在使用简单恢复模式的数据库中,BCM 页是不相关的,因为大容量日志记录操作不记入日志。BCM pages are not relevant in a database that is using the simple recovery model, because no bulk logged operations are logged. 在使用完整恢复模式的数据库中,BCM 页同样不相关,因为该恢复模式将大容量日志记录操作视为有完整日志记录的操作。They are not relevant in a database that is using the full recovery model, because that recovery model treats bulk logged operations as fully logged operations.

DCM 页和 BCM 页的间隔与 GAM 和 SGAM 页的间隔相同,都是 64,000 个区。The interval between DCM pages and BCM pages is the same as the interval between GAM and SGAM page, 64,000 extents. 在物理文件中,DCM 和 BCM 页位于 GAM 和 SGAM 页之后。The DCM and BCM pages are located behind the GAM and SGAM pages in a physical file:

special_page_order

另请参阅See Also

sys.allocation_units (Transact-SQL) sys.allocation_units (Transact-SQL)
堆(没有聚集索引的表) Heaps (Tables without Clustered Indexes)
sys.dm_db_page_info sys.dm_db_page_info
读取页 Reading Pages
写入页Writing Pages