SQL Server2008存储结构之对象存储视图

时间：2010-10-22 来源：baoqiangwang

SQL Server2008存储结构之对象存储视图

原稿首发于it168，链接为http://tech.it168.com/a2010/0915/1104/000001104075.shtml

实际上最重要的数据库对象的存储结构来之于两张SQLServer支持的系统视图，即sys.partitions和 sys.allocation_units，还有一张SQLServer尚未公开支持的系统视图 sys.system_internals_allocation_units。

　　Sys.partitions视图，数据库中所有表和索引的每个分区在表中各对应一行;因此可以理解为与sys.indexes为一对多的关系，完全取决于表和索引的分区数。该视图结构如下：

序号	列名	说明
1	partition_id	分区的 ID。在数据库中是唯一的。
2	object_id	此分区所属的对象的 ID。每个表或视图都至少包含一个分区。
3	index_id	此分区所属的对象内的索引的 ID。
4	partition_number	所属索引或堆中的从 1 开始的分区号。对于未分区的表和索引，此列的值为 1。
5	hobt_id	包含此分区的行的数据堆或 B 树的 ID。
6	rows	此分区中的大约行数。
7	database_fragment_id	标识为仅供参考。不提供支持。不保证以后的兼容性。
8	data_compression	指示每个分区的压缩状态： 0 = NONE 1 = ROW 2 = PAGE
9	data_compression_desc	指示每个分区的压缩状态。可能的值为 NONE、ROW 和 PAGE

　　sys.allocation_units视图，数据库中的每个分配单元都在表中占一行;一个表可有4种分配单元类型：已删除、行内数据、LOB数据、行溢出数据，这取决于表的结构和行数据分布情况;同时如sys.partitions视图所述，表和索引又可能包括若干分区;因此一个表的分配单元数量= 表和索引情况*分区数*包含的分配单元类型;sys.allocation_units与sys.partitions也是一对多的关系。

　　同时分配单元视图中还包括了该分配单元所分配、已使用和数据使用的页面数量。

序号	列名	说明
1	allocation_unit_id	分配单元的 ID。在数据库中是唯一的。
2	type	分配单元的类型： 0 = 已删除 1 = 行内数据（所有数据类型，但 LOB 数据类型除外） 2 = 大型对象 (LOB) 数据 3 = 行溢出数据
3	type_desc	对分配单元类型的说明： DROPPED IN_ROW_DATA LOB_DATA ROW_OVERFLOW_DATA
4	container_id	与分配单元关联的存储容器的 ID。如果 type = 1 或 3，则 container_id = sys.partitions.hobt_id。如果 type 为 2，则 container_id = sys.partitions.partition_id。 0 = 标记为要延迟删除的分配单元
5	data_space_id	该分配单元所在文件组的 ID。
6	total_pages	该分配单元分配或保留的总页数。
7	used_pages	实际使用的总页数。
8	data_pages	包含下列数据的已使用页的数目：行内数据 LOB 数据行溢出数据返回的值排除了内部索引页和分配管理页。
9	database_fragment_id	标识为仅供参考。不提供支持。不保证以后的兼容性

　　sys.system_internals_allocation_units视图，用法与sys.allocation_units完全一样，不过在 sys.allocation_units的基础上增加了对分配单元的跟踪管理的页面地址信息，关于 first_page,root_page,first_iam_page的概念，会在后续的章节中介绍。

序号	列名	说明
1	allocation_unit_id	同sys.allocation_units
2	type	同sys.allocation_units
3	type_desc	同sys.allocation_units
4	container_id	同sys.allocation_units
5	filegroup_id	同sys.allocation_units
6	total_pages	同sys.allocation_units
7	used_pages	同sys.allocation_units
8	data_pages	同sys.allocation_units
9	first_page	首页的地址（文件号+页号）
10	root_page	索引根节点的地址（文件号+页号）
11	first_iam_page	Iam页的地址（文件号+页号）

　让我们还是以实例说话吧

#div_code img { border: 0px none; } --创建一张包含BLOB字段的数据表，同时创建一个聚集索引和非聚集索引，并插入3条记录
CREATE TABLE test(a INT,b VARCHAR(20),c TEXT);
CREATE UNIQUE CLUSTERED INDEX idx_test ON test(a);
CREATE INDEX ix_test ON test(b);
INSERT INTO test VALUES(1,'a','aaa')
INSERT INTO test VALUES(2,'b','bbb')
INSERT INTO test VALUES(3,'c','ccc')
SELECT * FROM test
--根据表名称查询出object_id
SELECT name,object_id,parent_object_id,type_desc
  FROM SYS.OBJECTS WHERE NAME='TEST'  --2089058478
--再查询相关索引视图，可以清楚的看到索引视图中包含两条索引记录，即聚集索引和非聚集索引
SELECT object_id,name,index_id,type,type_desc
  FROM SYS.INDEXES WHERE OBJECT_ID=2089058478
--再查询相关分区视图，可以看到分区视图中包含两条记录，即聚集索引和非聚集索引
SELECT partition_id,object_id,index_id,partition_number,hobt_id,rows
  FROM SYS.PARTITIONS WHERE OBJECT_ID=2089058478
--再查询分配单元视图，可以看到分区视图中包含三条记录，即聚集索引和非聚集索引以及LOB数据
SELECT allocation_unit_id,type,type_desc,container_id,
       data_space_id,total_pages,used_pages,data_pages
  FROM
  (
    SELECT * FROM SYS.ALLOCATION_UNITS U,SYS.PARTITIONS P
     WHERE U.TYPE IN (1,3) AND U.CONTAINER_ID=P.HOBT_ID
       AND P.OBJECT_ID=2089058478
    UNION ALL
    SELECT * FROM SYS.ALLOCATION_UNITS U,SYS.PARTITIONS P
     WHERE U.TYPE =2 AND U.CONTAINER_ID=P.PARTITION_ID
       AND P.OBJECT_ID=2089058478
  ) A
--最后再查询system_internals_allocation_units视图，可以看到该视图中与分配单元视图基本类似，除了多了三个页面地址
SELECT allocation_unit_id,type,type_desc,container_id,filegroup_id,
       total_pages,used_pages,data_pages,
       first_page,root_page,first_iam_page
  FROM
  (
    SELECT * FROM sys.system_internals_allocation_units U,SYS.PARTITIONS P
     WHERE U.TYPE IN (1,3) AND U.CONTAINER_ID=P.HOBT_ID
       AND P.OBJECT_ID=2089058478
    UNION ALL
    SELECT * FROM sys.system_internals_allocation_units U,SYS.PARTITIONS P
     WHERE U.TYPE =2 AND U.CONTAINER_ID=P.PARTITION_ID
       AND P.OBJECT_ID=2089058478
  ) A

#div_code img { border: 0px none; } --创建一张包含BLOB字段的数据表
CREATE TABLE heaptest(a INT,b VARCHAR(20),c TEXT);
INSERT INTO heaptest VALUES(1,'a','aaa')
INSERT INTO heaptest VALUES(2,'b','bbb')
INSERT INTO heaptest VALUES(3,'c','ccc')
SELECT * FROM heaptest
SELECT name,object_id,parent_object_id,type_desc
  FROM SYS.OBJECTS WHERE NAME='heaptest'  --2105058535
SELECT object_id,name,index_id,type,type_desc
  FROM SYS.INDEXES WHERE OBJECT_ID=2105058535
SELECT partition_id,object_id,index_id,partition_number,hobt_id,rows
  FROM SYS.PARTITIONS WHERE OBJECT_ID=2105058535
SELECT allocation_unit_id,type,type_desc,container_id,
       data_space_id,total_pages,used_pages,data_pages
  FROM
  (
    SELECT * FROM SYS.ALLOCATION_UNITS U,SYS.PARTITIONS P
     WHERE U.TYPE IN (1,3) AND U.CONTAINER_ID=P.HOBT_ID
       AND P.OBJECT_ID=2105058535
    UNION ALL
    SELECT * FROM SYS.ALLOCATION_UNITS U,SYS.PARTITIONS P
     WHERE U.TYPE =2 AND U.CONTAINER_ID=P.PARTITION_ID
       AND P.OBJECT_ID=2105058535
  ) A

SELECT allocation_unit_id,type,type_desc,container_id,filegroup_id,
       total_pages,used_pages,data_pages,
       first_page,root_page,first_iam_page
  FROM
  (
    SELECT * FROM sys.system_internals_allocation_units U,SYS.PARTITIONS P
     WHERE U.TYPE IN (1,3) AND U.CONTAINER_ID=P.HOBT_ID
       AND P.OBJECT_ID=2105058535
    UNION ALL
    SELECT * FROM sys.system_internals_allocation_units U,SYS.PARTITIONS P
     WHERE U.TYPE =2 AND U.CONTAINER_ID=P.PARTITION_ID
       AND P.OBJECT_ID=2105058535
  ) A