人人都是 DBA（VIII）SQL Server 頁存儲結構 - gaochundong－IT工程師數位筆記本

文章出處

當在 SQL Server 數據庫中創建一張表時，會在多張系統基礎表中插入所創建表的信息，用于管理該表。通過目錄視圖 sys.tables, sys.columns, sys.indexes 可以查看新建的表的元數據信息。

下面使用創建 Customer 表的過程作為示例。

USE [TEST]
GO

DROP TABLE [dbo].[Customer]
GO

CREATE TABLE [dbo].[Customer](
    [ID] [int] NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [Address] [varchar](100) NULL,
    [Phone] [varchar](100) NULL
) 
GO

select * from sys.tables where [name] = 'Customer';
select * from sys.columns where object_id = (select object_id from sys.tables where [name] = 'Customer');
select * from sys.indexes where object_id = (select object_id from sys.tables where [name] = 'Customer');

如果一張表沒有聚集索引（Clustered Index），則數據本身沒有結構，這樣的表稱為堆（Heap）。sys.indexes 中堆的 index_id 為 0，可以看到 Customer 表沒有建立任何索引，所以 index_id 為 0，并且 type_desc 為 HEAP。

堆中的數據沒有任何結構，當有新的數據行插入時，SQL Server 會尋找空閑的可用空間直接插入。存儲數據的數據頁之間也沒有連接關系，不會形成一個鏈表。

如果為表建立上非聚集索引（Non-Clustered Index），則 index_id 從 2 開始增長。1 的位置始終留給聚集索引（Clustered Index）。

CREATE NONCLUSTERED INDEX [IX_Customer_Name] ON [dbo].[Customer] ([Name]);

ALTER TABLE [dbo].[Customer] ADD CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED ([ID]);

SQL Server 在構成 Primary Key 約束的列上創建一個唯一索引，如果不明確指定，則默認該索引類型是唯一聚集索引。建立 Primary Key 后，可以在 sys.key_constraints 視圖中查看主鍵信息。

select * from sys.check_constraints
select * from sys.default_constraints
select * from sys.key_constraints
select * from sys.foreign_keys

當為表建立聚集索引后，表內的數據會重構成樹狀結構，也就是使用 B 樹形式進行存儲。

SQL Server 數據庫的每張表和索引都可以存儲在多個分區上，sys.partitions 視圖為堆或索引的每個分區包含一行數據。每個堆或索引都至少有一個分區，即使沒有專門進行分區。

select * from sys.partitions where object_id = (select object_id from sys.tables where [name] = 'Customer');

SQL Server 用于描述某個分區上某張表或索引子集的術語是 hobt，代表 Heap Or B-Tree，即堆或 B 樹，發音讀作 "hobbit"。所以上圖中可以看到存在一列為 hobt_id，使得 partition_id 和 hobt_id 形成一對一的關系，實際上這兩列總是具有相同的值。

分區上可以存儲 3 種類型的數據頁：

行內數據頁（In-Row Data Pages）
行溢出頁（Row-Overflow Data Pages）
LOB 數據頁（LOB Data Pages）

一個分區下的一種數據頁類型的一組頁面稱為分配單元（Allocation Unit），可以使用 sys.allocation_units 視圖來查看。

SELECT object_name(object_id) AS [name]
    ,partition_id
    ,partition_number AS pnum
    ,rows
    ,allocation_unit_id AS au_id
    ,type_desc AS page_type_desc
    ,total_pages AS pages
FROM sys.partitions p
JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE object_id = OBJECT_ID('dbo.Customer');

如果行的長度超過了最大值 8060 字節，那么行的數據會被存放到行溢出頁中。如果行中存在 LOB 類型的字段，則會使用 LOB 數據頁存儲。

ALTER TABLE [dbo].[Customer] ADD [OrderDescription] varchar(8000);
ALTER TABLE [dbo].[Customer] ADD [OrderDetails] text;

對表添加聚集索引不會修改 sys.allocation_units 中的行數，但會修改 partition_id，因為創建聚集索引會導致表在系統內重建。添加非聚集索引則會至少添加一行記錄以跟蹤該索引。

ALTER TABLE [dbo].[Customer] ADD CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED ([ID]);
CREATE NONCLUSTERED INDEX [IX_Customer_Name] ON [dbo].[Customer] ([Name]);

SELECT convert(CHAR(8), object_name(i.object_id)) AS table_name
    ,i.NAME AS index_name
    ,i.index_id
    ,i.type_desc AS index_type
    ,partition_id
    ,partition_number AS pnum
    ,rows
    ,allocation_unit_id AS au_id
    ,a.type_desc AS page_type_desc
    ,total_pages AS pages
FROM sys.indexes i
JOIN sys.partitions p ON i.object_id = p.object_id
    AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE i.object_id = object_id('dbo.Customer');

可以通過 DMV 視圖查詢索引狀況。

SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID('TEST'), OBJECT_ID(N'dbo.Customer'), NULL, NULL, 'DETAILED');

SQL Server 中的數據頁具有 8KB（8192 Bytes）的固定長度，每個數據頁由 3 部分組成：

頁頭（Page Header）
數據行（Data Rows）
行偏移數組（Row Offset Array）

頁頭（Page Header）占了 96 字節長度。向 Customer 表插入條數據看看。

INSERT INTO [dbo].[Customer] (
    [ID]
    ,[Name]
    ,[Address]
    ,[Phone]
    )
VALUES (
    1
    ,'Dennis Gao'
    ,'Beijing Haidian'
    ,'18866667777'
    )
GO

使用 sys.fn_dblog 查詢事務日志，獲取新插入的 Page 相關信息。

SELECT [Current LSN]
    ,[Operation]
    ,[Context]
    ,[Transaction ID]
    ,[Log Record Length]
    ,[Previous LSN]
    ,[AllocUnitId]
    ,[AllocUnitName]
    ,[Page ID]
    ,[Slot ID]
    ,[Xact ID]
FROM sys.fn_dblog(NULL, NULL)

發現 LOP_INSERT_ROWS 操作對應的 Page ID 為 0001:0000008e，翻譯成 10 進制為 1:142。也就是文件號為 1，頁編號為 142。實際上，每個頁都會通過一個 6 Bytes 的值來表示，其中包含 2 Bytes 的文件 ID 和 4 Bytes 的頁編號。

由于我們只插入了 1 條數據，可以使用 sys.system_internals_allocation_units 目錄視圖來查看首頁地址。以 sys.system_internals_ 開頭的目錄視圖是還未公開的目錄視圖。sys.system_internals_allocation_units 比 sys.allocation_units 視圖多 3 個字段：first_page, root_page, first_iam_page。

SELECT object_name(object_id) AS [name]
    ,rows
    ,type_desc AS page_type_desc
    ,total_pages AS pages
    ,first_page
    ,root_page
    ,first_iam_page
FROM sys.partitions p
JOIN sys.system_internals_allocation_units a ON p.partition_id = a.container_id
WHERE object_id = object_id('dbo.Customer');

這里的 first_page = 0x8E0000000100 十六進制結果可以翻譯為 00 01 共 2 字節的文件號，和 00 00 00 8E 共 4 字節的頁編號。與上面的 0001:0000008e 正好匹配。

不過，這么計算基本上樂趣全無了，可以使用如下的 SQL 進行直接翻譯。

DECLARE @page_num BINARY (6) = 0x8E0000000100;

SELECT (convert(VARCHAR(2), (
        convert(INT, substring(@page_num, 6, 1)) * power(2, 8)) 
        + (convert(INT, substring(@page_num, 5, 1)))) 
    + ':' 
    + convert(VARCHAR(11), (
        convert(INT, substring(@page_num, 4, 1)) * power(2, 24)) 
        + (convert(INT, substring(@page_num, 3, 1)) * power(2, 16)) 
        + (convert(INT, substring(@page_num, 2, 1)) * power(2, 8)) 
        + (convert(INT, substring(@page_num, 1, 1))
        ))) AS page_number;

另一種確認頁編號的方式是使用 DBCC IND 命令，用于查找表中的所有的頁。

DBCC IND([TEST], 'dbo.Customer', -1)  
GO

實際上，還有一種查詢頁編號的方式是使用內部函數 sys.fn_PhysLocFormatter，不過很遺憾，這個函數的返回結果存在問題，不準確。

SELECT %%physloc%%, sys.fn_PhysLocFormatter (%%physloc%%) AS RID, * FROM dbo.Customer;

DBCC TRACEON(3604, -1)
GO

DBCC PAGE([TEST], 1, 142, 1)  
GO

DBCC PAGE 的輸出結果分成 4 個部分：BUFFER，PAGE HEADER，DATA，OFFSET TABLE。

BUFFER 部分顯示給定頁面的緩沖區信息。
PAGE HEADER 部分顯示 Page 頁頭信息。
DATA 部分顯示 Page 中存放的記錄信息。
OFFSET TABLE 部分顯示行偏移數組信息。

PAGE: (1:142)


BUFFER:


BUF @0x000000027D0048C0

bpage = 0x0000000272292000          bhash = 0x0000000000000000          bpageno = (1:142)
bdbid = 7                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 17332                       bstat = 0x10b
blog = 0x15acc                      bnext = 0x0000000000000000          

PAGE HEADER:


Page @0x0000000272292000

m_pageId = (1:142)                  m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 110   m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594045136896                                
Metadata: PartitionId = 72057594040287232                                Metadata: IndexId = 0
Metadata: ObjectId = 1045578763     m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 8                         m_slotCnt = 1                       m_freeCnt = 8039
m_freeData = 151                    m_reservedCnt = 0                   m_lsn = (34:369:91)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 0                      DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = ALLOCATED              
PFS (1:1) = 0x61 MIXED_EXT ALLOCATED  50_PCT_FULL                        DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED           

DATA:


Slot 0, Offset 0x60, Length 55, DumpStyle BYTE

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 55                    
Memory Dump @0x000000000B7DA060

0000000000000000:   30000800 01000000 04000003 001d002c 00370044  0..............,.7.D
0000000000000014:   656e6e69 73204761 6f426569 6a696e67 20486169  ennis GaoBeijing Hai
0000000000000028:   6469616e 31383836 36363637 373737             dian18866667777

OFFSET TABLE:

Row - Offset                        
0 (0x0) - 96 (0x60)

字段名	描述
m_pageId	頁在文件中的位置序號。(1:142) 表示文件 1 中第 142 頁。
m_headerVersion	Page Header 的定義版本。目前一直為 1。
m_type	頁類型。 1 - 數據頁，存放堆或聚集索引的頁節點數據等。 2 - 索引頁，存放聚集索引的內部節點數據，或者所有的非聚集索引數據。 3 - Text Mix Page 4 - Text Tree Page 7 - Sort Page，存放排序操作的中間結果。 8 - GAM Page 9 - SGAM Page 10 - IAM Page，IAM = Index Allocation Map。 11 - PFS Page 13 - Boot Page 15 - File Header Page 16 - Diff Map Page 17 - ML Map Page 18 - A page is deallocated by DBCC CHECKDB during a repair. 19 - Temporary page for DBCC INDEXDEFRAG 20 - Page pre-allocated as part of a bulk load operation.
m_level	代表頁在 B 樹上的層。 B 樹中葉節點 Level 為 0，向上累加。
m_objId	關聯的對象 ID
m_indexId	關聯的索引 ID
m_prevPage	指向前一頁的指針。
m_nextPage	指向后一頁的指針。
pminlen	行的固定長度部分的字節數。
m_slotCnt	該頁上存放的記錄行數。
m_freeCnt	頁上的可以用空間字節數。
m_freeData	該頁上第一個可用空間的字節偏移位置。
m_reservedCnt	為活動事務保留的可用空間字節數量。
m_lsn	最后一個更改頁信息的操作的 Log Sequence Number 事務日志編號。
m_xactReserved	最后更新 m_reservedCnt 字段的事務保留的可用空間的字節數。
m_xdesId	最近更新 m_reservedCnt 字段的事務 ID。
m_ghostRecCnt	Ghost 記錄的數量。
m_tornBits	Page Checksum

Metadata 為前綴的字段不是 Page Header 中的內容，它們是 DBCC PAGE 的一些查詢結果。

Metadata: AllocUnitId = 72057594045136896
Metadata: PartitionId = 72057594040287232
Metadata: IndexId = 0
Metadata: ObjectId = 1045578763

這與使用目錄視圖查詢的結果一致。

SELECT convert(CHAR(8), object_name(i.object_id)) AS table_name
    ,i.object_id
    ,i.index_id
    ,partition_id
    ,partition_number AS pnum
    ,rows
    ,allocation_unit_id AS au_id
    ,total_pages AS pages
FROM sys.indexes i
JOIN sys.partitions p ON i.object_id = p.object_id
    AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE i.object_id = object_id('dbo.Customer');

我們發現 m_objId 和 m_indexId 兩個字段的值沒有在之前的查詢中出現過：

m_objId (AllocUnitId.idObj) = 110
m_indexId (AllocUnitId.idInd) = 256

實際上這兩個字段的值是計算出來的結果，計算規則：

將 m_indexId 向左移 48 位，記錄為值 A；
將 m_objId 向左移 16 位，記錄為值 B；
AllocUnitId = A|B；（邏輯或）

例如，使用上面的頁中的值，

A = 256 << 48 = 72057594037927936
B = 110 << 16 = 7208960
A|B = 72057594045136896

可以使用如下 SQL 進行計算。

SELECT 256 * CONVERT(BIGINT, POWER(2.0, 48));
SELECT 110 * CONVERT(BIGINT, POWER(2.0, 16));
SELECT 256 * CONVERT(BIGINT, POWER(2.0, 48)) | 110 * CONVERT(BIGINT, POWER(2.0, 16));

那么，反向根據 AllocUnitId 求 m_objId 和 m_indexId 的方式和上面的規則正好相反：

m_indexId = AllocUnitId >> 48
m_objId = (AllocUnitId - (m_indexId << 48)) >> 16

可以使用下面的 SQL 語句進行計算。

DECLARE @alloc BIGINT = 72057594045136896;
DECLARE @index BIGINT;

SELECT @index = CONVERT(BIGINT, CONVERT(FLOAT, @alloc) * (1 / POWER(2.0, 48))
    );

SELECT CONVERT(BIGINT, CONVERT(FLOAT, @alloc - (@index * CONVERT(BIGINT, POWER(2.0, 48)))) * (1 / POWER(2.0, 16))
    ) AS [m_objId]
    ,@index AS [m_indexId];
GO

DBCC PAGE 輸出的 OFFSET TABLE 部分是一塊 2 字節的條目塊，其中每個條目指向著行數據頁的起始位置。也就是，每一行數據都有一個 2 字節的條目在行偏移數組中。行偏移數組指向著頁內數據的邏輯順序。例如，對于一個聚集索引的表，SQL Server 會按照聚集索引的鍵的順序對數據進行存儲，這并不是說物理順序就是按照鍵的順序排列，而是在行偏移數組中的 slot 0 存放索引的第 1 個數據引用，slot 1 存放第 2 個數據引用，以此類推。這樣，物理上數據就可以存放在任意位置了。

對于數據行存儲的結構，可以用下圖所示，包括存儲固定長度字段數據和變長字段數據。

可以與 DBCC PAGE 輸出的 DATA 數據段落進行數據對比分析，具體的內容較為復雜，這里就不詳細展開了。

Slot 0, Offset 0x60, Length 55, DumpStyle BYTE

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 55                    
Memory Dump @0x000000000B1DA060

0000000000000000:   30000800 01000000 04000003 001d002c 00370044  0..............,.7.D
0000000000000014:   656e6e69 73204761 6f426569 6a696e67 20486169  ennis GaoBeijing Hai
0000000000000028:   6469616e 31383836 36363637 373737             dian18866667777

由于每頁有固定的 8K 字節的大小。每一個數據頁中都包含多條數據記錄，一頁中能存儲多少條記錄則直接依賴于記錄本身的大小。由于從磁盤讀取數據時也是從磁道和柱面一次讀取多個數據頁，讓后將數據頁存放到內存緩沖區，所以如果每數據頁可以存放更多的數據記錄，則可以直接減少物理讀的次數，而通過邏輯讀來提高性能。

《人人都是 DBA》系列文章索引：

序號	名稱
1	人人都是 DBA（I）SQL Server 體系結構
2	人人都是 DBA（II）SQL Server 元數據
3	人人都是 DBA（III）SQL Server 調度器
4	人人都是 DBA（IV）SQL Server 內存管理
5	人人都是 DBA（V）SQL Server 數據庫文件
6	人人都是 DBA（VI）SQL Server 事務日志
7	人人都是 DBA（VII）B 樹和 B+ 樹
8	人人都是 DBA（VIII）SQL Server 頁存儲結構
9	人人都是 DBA（IX）服務器信息收集腳本匯編
10	人人都是 DBA（X）資源信息收集腳本匯編
11	人人都是 DBA（XI）I/O 信息收集腳本匯編
12	人人都是 DBA（XII）查詢信息收集腳本匯編
13	人人都是 DBA（XIII）索引信息收集腳本匯編
14	人人都是 DBA（XIV）存儲過程信息收集腳本匯編
15	人人都是 DBA（XV）鎖信息收集腳本匯編