【学习散记4之--页和区】

学习散记 --页和区

SQL Server 中数据存储的基本单位是页。为数据库中的数据文件（.mdf 或 .ndf）分配的磁盘空间可以从逻辑上划分成页（从 0 到 n 连续编号）。磁盘 I/O 操作在页级执行
区是八个物理上连续的页的集合，用来有效地管理页。所有页都存储在区中。

 

页：
概念：页的大小为 8 KB,每页的开头是 96 字节的标头，用于存储有关页的系统信息。此信息包括页码、页类型、页的可用空间以及拥有该页的对象的分配单元 ID。

类型：
  1) Data:当 text in row 设置为 ON 时，包含除 text、 ntext、image、nvarchar(max)、varchar(max)、varbinary(max) 和 xml 数据之外的所有数据的数据行。、

  2) Index：索引条目

  3) Text/Image：大型对象数据类型：text、 ntext、image、nvarchar(max)、varchar(max)、varbinary(max) 和 xml 数据。
          数据行超过 8 KB 时为可变长度数据类型列：varchar、nvarchar、varbinary 和 sql_variant

  4) GAM，SGAM：有关区是否分配的信息

  5) Page Free Space（PFS）：有关页分配和页的可用空间的信息

  6) Index Allocation Map(IAM): 有关每个分配单元中表或索引所使用的区的信息

  7) Bulk Changed Map (BCM): 有关每个分配单元中自最后一条 BACKUP LOG 语句之后的大容量操作所修改的区的信息

  8) Differential Changed Map(DCM):有关每个分配单元中自最后一条 BACKUP DATABASE 语句之后更改的区的信息。

     ps；日志文件不包含页，而是包含一系列日志记录。

页结构：
在数据页上，数据行紧接着标头按顺序放置。页的末尾是行偏移表，对于页中的每一行，每个行偏移表都包含一个条目。

大型行支持：
页的单个行中的最大数据量和开销是 8,060 字节 (8 KB)。但是，这不包括用 Text/Image 页类型存储的数据。包含 varchar、nvarchar、varbinary 或 sql_variant 列的表不受此限制的约束。
当表中的所有固定列和可变列的行的总大小超过限制的 8,060 字节时，SQL Server 将从最大长度的列开始动态将一个或多个可变长度列移动到 ROW_OVERFLOW_DATA 分配单元中的页。
将列移动到 ROW_OVERFLOW_DATA 分配单元中的页后，将在 IN_ROW_DATA 分配单元中的原始页上维护 24 字节的指针。如果后续操作减小了行的大小，SQL Server 会动态将列移回到原始数据页.

行溢出的事项：
a.超过 8,060 字节的行大小限制可能会影响性能，因为 SQL Server 仍保持每页 8 KB 的限制。
 上面提到了 当超出8060的时候 动态移动行的做法。所以我们要在设计表的时候考虑溢出的行的比列以及查询这些溢出行的频率.
 如果可能需要经常查询行溢出数据中的许多行，请考虑对表格进行规范化处理，以使某些列移动到另一个表中。然后可以在异步 JOIN 操作中执行查询。

b.其他数据类型列（一般数据列，Text/Image 页类型存储的数据不包含)必须在 8,060 字节的行限制之内.

c.聚集索引的索引键不能包含在 ROW_OVERFLOW_DATA 分配单元中具有现有数据的 varchar 列。
 这里举个例子：
--建表 CREATE table #ksss1 ( id1 varchar(820), id2 varchar(800), id3 varchar(800), id4 varchar(800), id5 varchar(800), id6 varchar(800), id7 varchar(800), id8 varchar(800), id9 varchar(800), id10 varchar(800), id11 varchar(800), id12 int ) --插入测试记录 DECLARE @S VARCHAR(820),@s1 varchar(800) SELECT @S=ISNULL(@S,'')+LTRIM(A.NUMBER+B.NUMBER),@s1=ISNULL(@S,'')+LTRIM(A.NUMBER+B.NUMBER) FROM MASTER..SPT_VALUES A,MASTER..SPT_VALUES B WHERE A.TYPE='P' AND B.TYPE='P' exec ('insert #ksss1 select '''+@s+''','''+@s1+''','''+@s1+''','''+@s1+''','''+@s1+ ''','''+@s1+''','''+@s1+''','''+@s1+''','''+@s1+''','''+@s1 +''','''+@s1+''',1') --建立索引 create clustered index OS_ID1_DI2 ON #Ksss1(ID1,id12)

 

 --这里的表的最大行总和长度超过了8060字节, 我插入的这条记录超过了8060字节,当我插入的时候发生页面溢出,id1列溢出到ROW_OVERFLOW_DATA分配单元内.在IN_ROW_DATA 分配单元中留下了个指针
 --接着我为表建立聚集索引
  create clustered index OS_ID1_DI2 ON #Ksss1(ID1)
 --这个时候再插入上面那条记录,也可以插入成功---
 --这里发生的过程其实是这样的:再创建聚集索引后,数据发生“页面搬家”-此时最为键列的ID1列已经不在ROW_OVERFLOW_DATA分配单元内,它重新回到了IN_ROW_DATA 分配单元中,换成了其他非键列溢出.
 --这样以后每次插入记录ID1不会再溢出.
 ps：可以包括包含行溢出数据的列，作为非聚集索引的键列或非键列

d.对于使用稀疏列的表，记录大小限制为 8,018 字节。

e.若要获得有关可能包含行溢出数据的表或索引的信息，请使用 sys.dm_db_index_physical_stats 动态管理函数。

 

 

 

区：
概念：区是管理空间的基本单位。一个区是八个物理上连续的页（即 64 KB）。这意味着 SQL Server 数据库中每 MB 有 16 个区。

分类：SQL Server 不会将所有区分配给包含少量数据的表
  1):统一区，由单个对象所有。区中的所有 8 页只能由所属对象使用。
  2):混合区，最多可由八个对象共享。区中八页的每页可由不同的对象所有。
分配页的过程：通常从混合区向新表或索引分配页。

  当表或索引增长到 8 页时，将变成使用统一区进行后续分配。
  如果对现有表创建索引，并且该表包含的行足以在索引中生成 8 页，则对该索引的所有分配都使用统一区进行。

区分配：
 SQL Server 使用两种类型的分配映射表来记录区的分配：
 全局分配映射表 (GAM)：记录已分配的区。每个 GAM 包含 64,000 个区，相当于近 4 GB 的数据。GAM 用一个位来表示所涵盖区间内的每个区的状态。如果位为 1，则区可用；如果位为 0，则区已分配。
 共享全局分配映射表 (SGAM)：记录当前用作混合区且至少有一个未使用的页的区。每个 SGAM 包含 64,000 个区，相当于近 4 GB 的数据。SGAM 用一个位来表示所涵盖区间内的每个区的状态。如果位为 1，则区正用作混合区且有可用页。如果位为 0，则区未用作混合区，或者虽然用作混合区但其所有页均在使用中。

 进行区管理算法简单点情况如下：
  a.分配统一区:数据库引擎将在 GAM 中搜索为 1 的位，并将其设置为 0。
  b.查找具有可用页的混合区:将在 SGAM 中搜索为 1 的位.
  c.分配混合区:在 GAM 中搜索为 1 的位，将其设置为 0，然后将 SGAM 中对应的位设置为 1。
  d.释放区，数据库引擎确保将 GAM 位设置为 1，将 SGAM 位设置为 0。

跟踪可用空间:
 页可用空间 (PFS) 页记录每页的分配状态，是否已分配单个页以及每页的可用空间量。PFS 对每页都有一个字节，记录该页是否已分配。如果已分配，则记录该页是为空、已满 1% 到 50%、已满 51% 到 80%、已满 81% 到 95% 还是已满 96% 到 100%。
 将区分配给对象后，数据库引擎将使用 PFS 页来记录区中的哪些页已分配或哪些页可用。数据库引擎必须分配新页时，将使用此信息。

管理对象使用的空间 :
 索引分配映射 (IAM)页将映射分配单元使用的数据库文件中 4 GB 部分中的区.
 分配单元分三种类型：
  IN_ROW_DATA：用于存储堆分区或索引分区,至少含有一个;
  LOB_DATA：用于存储大型对象 (LOB) 数据类型，例如 xml、varbinary(max) 和 varchar(max)。
  ROW_OVERFLOW_DATA：用于存储超过 8,060 字节行大小限制的 varchar、nvarchar、varbinary 或 sql_variant 列中存储的可变长度数据。
 结构：IAM页含有一个标头,指明 IAM 页所映射的区范围的起始区.IAM 页中还有一个大位图，其中每个位代表一个区。
    如果某个位是 0，它所代表的区将不会分配给拥有该 IAM 页的分配单元。如果这个位是 1，它所代表的区将被分配给拥有该 IAM 页的分配单元。

 IAM页的分布：
  每个分配单元在有区的每个文件中至少有一个 IAM 页。如果分配给分配单元的文件中的区的范围超过了一个 IAM 页能够记录的范围，一个文件中也可能会有多个 IAM 页。
  因为如果分配单元包含来自多个文件的区，或者超过一个文件的 4 GB 范围，那么一个 IAM 链中将链接多个 IAM 页。

 插入行过程：
   在当前页中插入新行，而当前页中没有可用空间时，它将使用 IAM 和 PFS 页查找要将该行分配到的页.
   数据库引擎使用 IAM 页查找分配给分配单元的区。对于每个区，数据库引擎将搜索 PFS 页，以查看是否有可用的页。

跟踪已修改的区：
 SQL Server 使用DCM，BCM跟踪被大容量复制操作修改的区，以及自上次完整备份后修改的区。这些数据结构极大地加快了差异备份的速度。当数据库使用大容量日志恢复模式时，这些数据结构也可以加快将大容量复制操作记录至日志的速度。
  差异更改映射表 (DCM)：跟踪自上次执行 BACKUP DATABASE 语句后更改过的区。
             差异备份只读取 DCM 页便可以确定已修改的区。这样大大减少了差异备份必须扫描的页数
  大容量更改映射表 (BCM)：跟踪自上次执行 BACKUP LOG 语句后，被大容量日志记录操作修改的区/
              只有在数据库使用大容量日志记录恢复模式时，才会与 BCM 页有关.
              在此恢复模式中，当执行 BACKUP LOG 时，备份进程将扫描 BCM 查找已经修改的区。然后，将那些区包括在日志备份中。

在数据文件中： 
 文件头=》PFS=》GAM=》SGAM=》BCM=》DCM
 第0页   第1页 第2页  第3页  第4页 第5页
 第一个 PFS 页之后是一个大小大约为 8,000 页的 PFS 页。
 在第 2 页的第一个 GAM 页之后还有另一个 GAM 页（包含 64,000 个区），在第 3 页的第一个 SGAM 页之后也有另一个 SGAM 页（包含 64,000 个区）
 DCM 页和 BCM 页的间隔与 GAM 和 SGAM 页的间隔相同，都是 64,000 个区。