SQL Server2008儲存結構之物件儲存檢視

bq_wang發表於2010-10-22
SQLServer物件

SQL Server2008儲存結構之物件儲存檢視

實際上最重要的資料庫物件的儲存結構來之於兩張SQLServer支援的系統檢視，即sys.partitions和 sys.allocation_units，還有一張SQLServer尚未公開支援的系統檢視 sys.system_internals_allocation_units。

　　Sys.partitions檢視，資料庫中所有表和索引的每個分割槽在表中各對應一行;因此可以理解為與sys.indexes為一對多的關係，完全取決於表和索引的分割槽數。該檢視結構如下：

序號列名說明
1partition_id分割槽的 ID。在資料庫中是唯一的。
2object_id此分割槽所屬的物件的 ID。每個表或檢視都至少包含一個分割槽。
3index_id此分割槽所屬的物件內的索引的 ID。
4partition_number所屬索引或堆中的從 1 開始的分割槽號。對於未分割槽的表和索引，此列的值為 1。
5hobt_id包含此分割槽的行的資料堆或 B 樹的 ID。
6rows此分割槽中的大約行數。
7database_fragment_id標識為僅供參考。不提供支援。不保證以後的相容性。
8data_compression指示每個分割槽的壓縮狀態：
0 = NONE
1 = ROW
2 = PAGE
 
9data_compression_desc指示每個分割槽的壓縮狀態。可能的值為 NONE、ROW 和 PAGE

　 　sys.allocation_units檢視，資料庫中的每個分配單元都在表中佔一行;一個表可有4種分配單元型別：已刪除、行內資料、LOB資料、 行溢位資料，這取決於表的結構和行資料分佈情況;同時如sys.partitions檢視所述，表和索引又可能包括若干分割槽;因此一個表的分配單元數量= 表和索引情況*分割槽數*包含的分配單元型別;sys.allocation_units與sys.partitions也是一對多的關係。

　　同時分配單元檢視中還包括了該分配單元所分配、已使用和資料使用的頁面數量。

序號列名說明
1allocation_unit_id分配單元的 ID。在資料庫中是唯一的。
2type分配單元的型別：
0 = 已刪除
1 = 行內資料（所有資料型別，但 LOB 資料型別除外）
2 = 大型物件 (LOB) 資料
3 = 行溢位資料
 
3type_desc對分配單元型別的說明：
DROPPED
IN_ROW_DATA
LOB_DATA
ROW_OVERFLOW_DATA
 
4container_id與分配單元關聯的儲存容器的 ID。
如果 type = 1 或 3，
則 container_id = sys.partitions.hobt_id。
如果 type 為 2，
則 container_id = sys.partitions.partition_id。
0 = 標記為要延遲刪除的分配單元
 
5data_space_id該分配單元所在檔案組的 ID。
6total_pages該分配單元分配或保留的總頁數。
7used_pages實際使用的總頁數。
8data_pages包含下列資料的已使用頁的數目：
行內資料
LOB 資料
行溢位資料
返回的值排除了內部索引頁和分配管理頁。
 
9database_fragment_id標識為僅供參考。不提供支援。不保證以後的相容性

　 　sys.system_internals_allocation_units檢視，用法與sys.allocation_units完全一樣，不過在 sys.allocation_units的基礎上增加了對分配單元的跟蹤管理的頁面地址資訊，關於 first_page,root_page,first_iam_page的概念，會在後續的章節中介紹。

序號列名說明
1allocation_unit_id同sys.allocation_units
2type同sys.allocation_units
3type_desc同sys.allocation_units
4container_id同sys.allocation_units
5filegroup_id同sys.allocation_units
6total_pages同sys.allocation_units
7used_pages同sys.allocation_units
8data_pages同sys.allocation_units
9first_page首頁的地址（檔案號+頁號）
10root_page索引根節點的地址（檔案號+頁號）
11first_iam_pageIam頁的地址（檔案號+頁號）

 　讓我們還是以例項說話吧

#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

1

#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

1
 



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/6517/viewspace-676505/，如需轉載，請註明出處，否則將追究法律責任。

相關文章