SQL Server2008儲存結構之堆表、行溢位

bq_wang發表於2010-11-02

堆資料表是沒有聚集索引的表。即資料行不按任何特殊的順序儲存，資料頁也沒有任何特殊的順序。資料頁不在連結列表內連結。 sys.system_internals_allocation_units 系統檢視中的列 first_iam_page 指向管理特定分割槽中堆的分配空間的一系列 IAM 頁的第一頁。SQL Server 使用 IAM 頁在堆中移動。堆內的資料頁和行沒有任何特定的順序，也不連結在一起。資料頁之間唯一的邏輯連線是記錄在 IAM 頁內的資訊。

　　那麼堆表是如何儲存資料的呢?出於簡化的目的，我們先來構造不含任何索引的一張堆資料表，然後從簡單到複雜逐步深入探討。

#div_code img { border: 0px none; }

--建立一張資料表，從系統表生成測試資料
DROP TABLE testheap
--建立一張2個固定長度欄位，3個不定長欄位的表，其中4個不為空，1個可為空
CREATE TABLE testheap
(
  ID         INT IDENTITY(1,1) NOT NULL,
  name       VARCHAR(20) NOT NULL,
  type       CHAR(100) NOT NULL,
  other      VARCHAR(50) NOT NULL,
  describle  VARCHAR(500)
)
--產生1000條隨機資料，並插入表中
DECLARE @i INT
SET @i=1
WHILE @i<=1000
BEGIN
  INSERT INTO testheap(name,type,other,describle)
  VALUES('name'+CAST(@i AS VARCHAR(3)),REPLICATE(@i%4,100),FLOOR(RAND()*10),NULL)
  SET @i=@i+1
END
SELECT * FROM testheap
--查詢該表的IAM頁面地址和首頁地址
SELECT total_pages,used_pages,data_pages,
       --first_page,root_page,first_iam_page,
       testdb.dbo.f_get_page(first_page) first_page_address,
       testdb.dbo.f_get_page(root_page) root_address,
       testdb.dbo.f_get_page(first_iam_page) IAM_address
  FROM sys.system_internals_allocation_units
WHERE container_id IN (SELECT partition_id FROM sys.partitions
             WHERE object_id in (SELECT object_id  FROM sys.objects
                                              WHERE name IN ('testheap')))

　　查詢結果如下：

total_pages	used_pages	data_pages	first_page_address	root_address	IAM_address
25	18	17	1:224	0:0	1:119

　　即SQL Server為該表分配了總計25個頁面，實際使用了18個頁面，扣除1個IAM管理頁面，實際資料頁面為17個，IAM管理頁面地址為第一個檔案的第119頁面，資料頁面的第一個頁面為第一個檔案的第224頁面。

　　那麼如何檢視到該表的頁面詳細分配情況呢?

　　首先通過dbcc page(testdb,1,119,3)可以粗略看到頁面分配情況

　　即SQL Server首先分配了8個混合區頁面，其次因為該物件已經超過8頁，SQL Server又分配了從第472頁到第487頁的頁面，共計16個頁面，然後包括本身的IAM頁面，共計25個頁面。

　　其次SQL Server還提供了一個更為友好的命令以找到各個型別的頁面分佈和它們的所在的檔案號和頁號。

　　DBCC IND({'dbname'|dbid},{'objectname'|objectID},

　　{nonclustered indid|1|0|-1|-2}[,partition_number])

　　{'dbname'|dbid}表示資料庫名或者資料庫ID

　　{'objectname'|objectID}表示物件名或者物件ID

　　{nonclustered indid|1|0|-1|-2}表示顯示行內資料分頁及指定物件的行內IAM分頁資訊

　　1 ：顯示所有分頁的資訊，包括IAM分頁,資料分頁,所有存在的LOB分頁和行溢位頁,索引分頁

　　-1：顯示所有IAM、資料分頁、及指定物件上全部索引的索引分頁.

　　-2：顯示指定物件的所有IAM分頁

　　nonclustered indid：顯示所有的IAM、資料分頁以及一個索引的索引分頁資訊。

　　{partition_number}->可選,為了與中的DBCC IND命令向前相容.它指定了一個特定分割槽號,如果不指定,顯示所有分割槽的資訊。

　　以下是DBCC IND命令輸出結果的欄位描述：

欄位名稱	欄位描述
PageFID	索引檔案的ID
PageFID	索引檔案的ID
IAMFID	管理該分頁的IAM分頁所在的檔案ID
IAMFID	管理該分頁的IAM分頁的ID
ObjectID	物件ID
IndexID	索引ID，0 代表堆, 1 代表聚集索引, 2-250 代表非聚集索引
PartitionNumber	表或索引所在的分割槽號碼
PartitionID	包含該分頁的分割槽ID
iam_chain_type	該頁所屬分配單元型別；行內資料、行溢位資料或Lob資料
PageType	分頁型別：1資料頁面；2索引頁面；3Lob_mixed_page；4Lob_tree_page；10IAM頁面
IndexLevel	索引層級，0 代表葉級別分頁；>0 代表非葉級別層次； NULL 代表IAM分頁
NextPageFID	本層下一個分頁所在的檔案ID
NextPageFID	本層下一個分頁ID
PrevPageFID	本層上一個分頁所在的檔案ID
PrevPageFID	本層上一個分頁ID

　　繼續為了簡化的目的，同時因為模擬的是小型資料表，所以可以忽略相關檔案號和iam鏈型別、分割槽號(該表暫無行內遷移和lob欄位)，我們只需要看看各個資料頁之間是否有相互聯絡、各個頁面的型別即可;所以我們構建了一張資料表用以存放dbcc ind命令輸出的結果，並有選擇性的選擇我們想要的欄位。

#div_code img { border: 0px none; }

CREATE TABLE tablepage
(
  PageFID         TINYINT,
  PagePID         INT,
  IAMFID          TINYINT,
  IAMPID          INT,
  ObjectID        INT,
  IndexID         TINYINT,
  PartitionNumber TINYINT,
  PartitionID     BIGINT,
  iam_chain_type  VARCHAR(30),
  PageType        TINYINT,
  IndexLevel      TINYINT,
  NextPageFID     TINYINT,
  NextPagePID     INT,
  PrevPageFID     TINYINT,
  PrevPagePID     INT
);
GO

TRUNCATE TABLE tablepage;
INSERT INTO tablepage EXEC ('DBCC IND(testdb,testheap,1)');
SELECT
  PagePID,IAMPID,ObjectID,IndexID,Pagetype,IndexLevel,
  NextPagePID,PrevPagePID
  FROM tablepage

　　最終結果如下：

　　我們可以看到SQL Server為該表所使用的頁面地址，索引ID、頁面型別、索引級別、前後頁的關係等等。

　　Pagetype=10為IAM頁面，Pagetype=1為資料頁面，即17個資料頁面，1個IAM頁面，與 system_internals_allocation_units輸出結果一致，每一個資料頁面都對應該IAM頁面地址，indexid=0表示為堆表，indexlevel=null表示為IAM頁面，indexlevel=0表示為葉子節點;而讓我們感到有些失望的是每一個頁面似乎除了有共同的 IAM管理頁面之外，相互之間是缺乏聯絡的。　

　　而且從dbcc ind執行的結果來看，每個頁面好像也是不連續的，那麼首先通過Internals Viewer外掛讓我們看一下IAM頁的情況吧，前八頁是斷斷續續的分散分佈的，而後面的16頁卻是連續的，再回頭看一下tablepage表也印證了這個現象。既然頁面與頁面之間缺乏聯絡，那麼對堆表資料的訪問只能靠IAM頁來管理和定位了。

　　SQL Server資料頁的結構大體包括三個部分，即標頭、資料行和行偏移量。

　　現在讓我們正式進入資料頁面去看一下資料頁面的構造，讓我們首先去訪問一下該表的資料首頁即第224個頁面。

　　Dbcc page(testdb,1,224,2)

　　PAGE HEADER部分，即該頁面的前96個位元組。

m_pageId = (1:224)	當前頁面號碼
m_headerVersion = 1	版本號，始終為1
m_type = 1	當前頁面型別，m_type=1表示資料頁面
m_typeFlagBits = 0x4	資料頁和索引頁為4，其他頁為0
m_level = 0	該頁在索引頁（B樹）中的級數，0表示為葉子節點
m_flagBits = 0x8200	頁面標誌
m_objId (AllocUnitId.idObj) = 94
m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594044088320	儲存單元的ID,sys.allocation_units.allocation_unit_id
Metadata: PartitionId = 72057594039107584	資料頁所在的分割槽號，sys.partitions.partition_id
Metadata: IndexId = 0	物件的索引號，sys.objects.object_id&sys.indexes.index_id
Metadata: bjectId = 133575514	該頁面所屬的物件的id，sys.objects.object_id
m_prevPage = (0:0)	該資料頁的前一頁面
m_nextPage = (0:0)	該資料頁的後一頁面
pminlen = 108	定長資料所佔的位元組數為108個位元組 ID INT IDENTITY(1,1) NOT NULL, type CHAR(100) NOT NULL, 共計104個位元組，每個定長欄位需要2個位元組的管理位元組
m_slotCnt = 62	頁面中的資料的行數，每頁62條記錄
m_freeCnt = 293	頁面中剩餘的空間，還剩293位元組的空間
m_freeData = 7775	從第一個位元組到最後一個位元組的空間位元組數（包括96位元組的檔案頭的長度）
m_reservedCnt = 0	活動事務釋放的位元組數
m_lsn = (67:272:3)	日誌記錄號
m_xactReserved = 0	最新加入到m_reservedCnt領域的位元組數
m_xdesId = (0:0)	新增到m_reservedCnt的最近的事務id
m_ghostRecCnt = 0	幻影資料的行數
m_tornBits = 1213019927	頁的校驗位或者被由資料庫頁面保護形式決定分頁保護位取代

　　上在頁的尾部還有個行偏移矩陣，記錄了每條記錄的起始位置，每條記錄需要2個位元組來記錄該位置，所以62條記錄共計124個維護位元組，加上293個剩餘空間和實際已使用的7775個位元組，剛好8192個位元組，即一頁。

　　從Offset table和page結構可以知道，第一條記錄從第96個位元組開始。

ID	name	Type	other	describle
1	name1	1111111111111111111111111111111111.	8	NULL

　　如前文所說，關於資料的儲存從第96個位元組開始

　　關於資料行的結構我們還可以採用稍微巨集觀一些的視角來檢視。

　　其中狀態A為如下說明：

　　bit0:版本資訊,在SQL Server 2005/08總是為0

　　bit1-3: 0=(primary record);1=(forwarded record);2=(forwarding stud);3=(index record);4=(溢位資料);5=(ghost索引記錄);6=(ghost資料記錄)

　　bit4:表示存在NULL點陣圖(在資料行裡SQL2005/08總存在NULL點陣圖)

　　bit5:表示存在變長列

　　bit6:未啟用

　　bit7:表示存在幽靈記錄

　　本例中30->00110000 它是一個行屬性的點陣圖從高位存到地位(右邊第一位是bit0)，bit4為1即存在變長列的欄位，因為在SQLServer2005/2008中總存在NULL點陣圖，所以bit5也為1。

　　狀態位B在SQLServer2005//2008中未啟用，所以為00

　　記錄定長部分的長度為2個位元組，是所有定長欄位的長度之和加4，該處為int型別4個位元組，char(100)為100個位元組，再加上4，所以為108，換算成16進位制即6c。

　　緊跟其後的為定長欄位的內容，即ID欄位的4個位元組和TYPE欄位的100個位元組。

　　固定長度的欄位資料之後，是該表的總欄位數，用兩個位元組表示，本表包括5個欄位所以為05 00。

　　NULL點陣圖:f0->11110000 因為該表只有列所以只需要看後面個,1表示該行的對應列為NULL或者該點陣圖未使用。本表前4個欄位不為空，第5個為空，第6-8未使用。

　　接下來是行記憶體儲資料的變長列的數目:0200->00000000 00000010=2 表示該行儲存了列name和other欄位的資料。

　　第一變長列資料終止位置為：7a00->00000000 01111010=122=1+1+2+(4+100)+2+ceiling(5/8)+2+2+2+len(“name1”)

　　第二變長列資料終止位置:7b00->00000000 01111011=123 實際上就是在前者的基礎上加了第二個變長列的欄位長度。

　　1+1+2+(4+100)+2+ceiling(5/8)+2+2+2+len(“name1”)+len(“8”)

　　第一列變長列的資料: 6e616d 6531換算成字元即'name1'

　　第二列變長列的資料:38換算成字元即8　　

　　下面讓我們將該記錄的describle欄位更新為非空值後，再看看該記錄儲存結構相應的變化。

#div_code img { border: 0px none; }

UPDATE testheap SET describle='abc' WHERE id=1

　　再次使用dbcc page(testdb,1,224,1)命令

　　我們不難發現狀態A，狀態B，定長長度、定長內容和欄位總數是沒有發生任何變化的。

　　NULL點陣圖部分變成了e0即11100000，表示describle欄位即第五個欄位不為空了

　　第一個和第二個變長列資料終止位置分別加了2個長度，這是因為當第三個變長列變更為非空後，自動新增了2個位元組的第三個欄位的維護欄位

　　第一個變長列資料終止位置從7a00變更為7c00

　　第二個變長列資料終止位置從7b00變更為7d00

　　新增加的第三個變長列終止位置為8000

　　同時在第一、二列變長列的資料後面新增加了616263，即字串”abc”

　　還有一個最顯著的區別就是該記錄的偏移位置顯然轉到了尾部，即5F1E的位置;但很奇怪的是該記錄原來的位置上還保留著原值，並沒有刪除掉。也就是說對於該記錄而言，應該是先刪除，然後又新增了一條新紀錄，只是把指標指向了新的偏移地址而已。

　　最後觀察一下記錄是如何刪除的

#div_code img { border: 0px none; }

DELETE FROM testheap WHERE ID IN (2,3)

　　當我們對比一下刪除前後兩條記錄的資訊，發現基本上原來的位置上資料沒有發生任何變化，只是原來的slot1和slot2已經不存在了。即SQL Server認為該資料已經不存在了。

行溢位頁面

#div_code img { border: 0px none; }

USE TESTDB
CREATE TABLE testOverFlow
(
  ID    INT IDENTITY(1,1),
  NAME1 VARCHAR(5000),
  NAME2 VARCHAR(5000)
)
INSERT INTO testOverFlow (NAME1,NAME2)
SELECT REPLICATE('A',5000),REPLICATE('AB',2500)
UNION
SELECT REPLICATE('B',5000),REPLICATE('BA',2500)
SELECT * FROM testOverFlow
SELECT type_desc
       total_pages,used_pages,data_pages,
       testdb.dbo.f_get_page(first_page) first_page_address,
       testdb.dbo.f_get_page(root_page) root_address,
       testdb.dbo.f_get_page(first_iam_page) IAM_address
  FROM sys.system_internals_allocation_units
WHERE container_id IN (SELECT partition_id FROM sys.partitions
               WHERE object_id in (SELECT object_id  FROM sys.objects
                                              WHERE name IN ('testoverflow')))

DBCC TRACEON(3604)
DBCC PAGE(testdb,1,54242,2)  --行內資料
DBCC PAGE(testdb,1,52343,2)  --行遷移資料

--同時我們也可以通過dbcc ind獲取所有資料頁面地址，然後進行頁面資訊顯示
TRUNCATE TABLE tablepage;
INSERT INTO tablepage EXEC ('DBCC IND(testdb,testOverFlow,1)');
SELECT
  PagePID,IAMPID,ObjectID,IndexID,Pagetype,IndexLevel,
  NextPagePID,PrevPagePID
  FROM tablepage

在NAME2欄位之前和普通的行記錄資訊是一致的，我們只從NAME2欄位開始就可以了。NAME2欄位在NAME1欄位之後，儲存了以下內容，即改列的溢位列型別、節點型別、資料庫更新次數、欄位長度、指向OVERFLOW頁的指標。

0200	0000	0100	00009d75	0000	8813 0000	77cc0000 0100 0000000
溢位列型別	節點型別	Lob資料更新次數	ID	未知	欄位長度	行溢位指標
RowOVerFlow	0	1	1973223424		5000	1:52343:0

讓我們再來看一下第52343頁看一下行溢位頁的資料情況，該頁面首先是一個LOB型別的頁面，然後主要包括該欄位的長度、關聯ID，和資料行；很顯然行內資料和溢位行資料的關聯是通過一個行溢位指標和ID進行的；因此對某個資料查詢而言，首先要找到該記錄的資訊，同時如果發生行溢位，還有根據該列的行溢出指標和關聯ID，才能找到整條記錄。

1個位元組	1個位元組	2個位元組	8個位元組	4個位元組	2個位元組
08	00	9613	00009d75	00000000	0300
狀態A	狀態B	欄位長度	ID	unkown	型別
即包含行溢位		5014(同變長欄位)	1973223424	未知	lob資料行

　　LOB頁面

　　從SQL Server 2005版本以後中，新增加了大值資料型別varchar(max)、nvarchar(max)、varbinary(max)。大值資料型別最多可以儲存2^30-1個位元組的資料。

　　從行為上來講這幾個資料型別和之前的資料型別 varchar、nvarchar 和 varbinary 相同。

　　按照微軟的說法是用這個資料型別來代替之前的text、ntext 和 image 資料型別，它們之間的對應關係為：

　　varchar(max)-------text;

　　nvarchar(max)-----ntext;

　　varbinary(max)----image

　　對大值資料型別的操作更類似於之前的varchar和varbinary之後，因此用法上也比之前的text和image比靈活和便宜。同時觸發器也可以直接引用大值資料型別;而之前的text和image是不行的。

　　因此varchar(max)與varchar(n)和text有著千絲萬縷的聯絡。對於varbinary(max)也一樣。

　　因為之前我們已經觀察過varchar(n)的行為，那麼讓我們看看這個新的varchar(max)與varchar(n)、text到底有什麼不同。

#div_code img { border: 0px none; }

CREATE TABLE testVARCHARMAX
(
  ID     INT IDENTITY(1,1),
  name   VARCHAR(20),
  remark VARCHAR(MAX)
)
CREATE TABLE testTEXT
(
  ID     INT IDENTITY(1,1),
  name   VARCHAR(20),
  remark TEXT
)

INSERT INTO testVARCHARMAX (name,remark)
SELECT REPLICATE('A',20),REPLICATE('AB',2500)
UNION
SELECT REPLICATE('B',20),REPLICATE('BA',2500)

INSERT INTO testTEXT (name,remark)
SELECT REPLICATE('A',20),REPLICATE('AB',2500)
UNION
SELECT REPLICATE('B',20),REPLICATE('BA',2500)

SELECT c.name,a.type_desc
       total_pages,used_pages,data_pages,
       testdb.dbo.f_get_page(first_page) first_page_address,
       testdb.dbo.f_get_page(root_page) root_address,
       testdb.dbo.f_get_page(first_iam_page) IAM_address
  FROM sys.system_internals_allocation_units a,sys.partitions b,sys.objects c
WHERE a.container_id=b.partition_id and b.object_id=c.object_id
   AND c.name in ('testVARCHARMAX','testTEXT')

　　執行結果如下：

LOB頁面