[SQL Server] How to view the page content with DBCC PAGE (2) : 進階篇

0. 前言

筆者經由前一篇 [SQL Server] How to view the page content with DBCC PAGE (1) : 基礎篇的撰稿過程, 對 DBCC IND 及 DBCC PAGE 的使用, 大致上有了一些了解; 但都是經由其輸出的結果來探查 page 的內容, 並未看到其內部是如何儲存的; 故欲透過撰寫本文的過程, 對其內部結構能夠有所理解.

本文主要以參考文件06, 參考文件12, 參考文件13, 參考文件14 這4篇為主要的參考對象; 故文章內容, 可能會與前4篇有些重疊或重複, 感謝這 4 篇文章的作者.
測試資料的部份, 係採自前一篇, 故建議由前一篇開始閱讀.

本文將區分為以下幾個部份進行探討:
1. Data Page 的結構
2. Data Page 各筆資料的結構
3. UPDATE 的實地觀察 (定長欄位)
4. UPDATE 的實地觀察 (可變長度欄位)

筆者已盡力查詢相關資料, 但有些文章, 受限於個人的能力, 無法全盤瞭解;
故本文僅以個人所能理解的部份作說明.

1. Data Page 的結構

如下圖所示, Data Page 的大小為 8192 bytes. 共可分為 3 個區域 :

(1) Page Header 區:
A. 大小固定為 96 bytes.
B. 存放該 page 的相關資料, 包括:

page number.
page type : DATA or INDEX or …
剩餘空間 (含 Row Offset 區未用到的部份).
其它資訊 …

(2) Data Record 區:
A. 大小預設為 8060 bytes = 8192 - 96 - 36, 會受到 Row Offset 大小的影響.
B. 此為實際存放資料的區域

(3) Row Offset 區:
A. 大小預設為 36 bytes, 由於每筆資料佔用 2 bytes, 最多只能容納 18 筆資料; 如果超過 18 筆資料, 則本區會再擴增.
B. 此區域用以記錄各筆資料起始的 offset.

Data Page Structure: Overall

以下茲以前一篇的 page #840 及 page #842 為例.
相關指令:


DBCC TRACEON(3604);
GO

DBCC PAGE(Cookies, 1, 840, 3)
-- WITH TABLERESULTS
GO

DBCC PAGE(Cookies, 1, 842, 3)
-- WITH TABLERESULTS
GO

(1) page #840 : 將前述指令輸出結果的長度及Offset資訊, 彙整如下圖:

A. slot_00 的 offset 為 0x60: 這代表 pager header 確實為 96 bytes (0x00 ~ 0x5F)
B. slot_01 的 offset 為 0xb4: 故 slot_00 的長度為 84 bytes = 0xb4 - 0x60
C. slot_00 至 slot_14 的總長度為 7852 bytes, 所以整個 page 剩餘空間為 214 bytes = ( 8060 - 7852 ) + ( 36 - 15 * 2 )
D. slot_00 至 slot_14 的總長度為 7852 bytes, 所以整個 page 剩餘可放資料的空間為 208 bytes = ( 8060 - 7852 )

Data Page Structure: Page #840

(2) page #842 : 將前述指令輸出結果的長度及Offset資訊, 彙整如下圖:

A. slot_00 的 offset 為 0x60: 這代表 pager header 確實為 96 bytes (0x00 ~ 0x5F)
B. slot_01 的 offset 為 0x28b: 故 slot_00 的長度為 555 bytes = 0x28b - 0x60
C. slot_00 至 slot_04 的總長度為 2771 bytes, 所以整個 page 剩餘空間為 5315 bytes = ( 8060 - 2771 ) + ( 36 - 5 * 2 )
D. slot_00 至 slot_04 的總長度為 2771 bytes, 所以整個 page 剩餘可放資料的空間為 5289 bytes = ( 8060 - 2771 )

Data Page Structure: Page #842

另外, 亦可由前述輸出的結果, 得到一些相關的資訊, 如以下2張圖的黃底字, 均與前述的計算說明相符.

DBCC Page #840 header

DBCC Page #842 header

2. Data Page 各筆資料列的結構

在了解 Data Page 的內部儲存結構後, 我們來探討一下, 各筆資料列 (Data Row) 的結構.
當然, 依常理推想, 一定會有 meta data 來描述儲存狀況, 此通稱為 row overhead, 包括: 欄位的數量, 變動長度欄位的數量, 是否為 NULL 值 (null bitmap) ... 等等; row overhead 的大小不是固定的, 會隨著欄位數量及資料內容, 而會增加.
茲以下圖呈現 Data Row (Primary Record) 的主要結構, 由於該結構不易理解, 故建議配合本文所列 page #840 slot_00 的範例閱讀,

RowStructure_overall

區塊_1: TagA (範圍: byte 0)

RowStructure_p840_part_1

(1) 資料內容: 0x30
(2) 說明: 該部份主要作為 Record Type 及 Record Attributes 的定義
(3) 將 0x30 以二進位呈現, 即 0011 0000, 由右至左說明:
A.bit 0: 記錄是否有 Version Information, 但是目前在 SQL Server 2008 以後, 似乎都是 0.
B.bit 1 - 3: 一般常見為 Primary Record (000) 及 Index Record (011)

a. 000: Primary Record:
* 此為 Clustered Index Leaf Level 的資料記錄; 或者
* Heap Table 中, 沒有被 forwarded 的資料記錄.
b. 001: Forwarded Record:
* Heap Table 中, 因為資料更新, 而無法在目萴 Data Page 儲存, 改存至另一個 Data Page (此新位置, 就稱之為 Forwarded Reocrd).
此時, 就會在原來的位置, 存放一個指標 (此原來位置, 就稱之為 Forwarding Record), 指向實際存放的資料記錄.
* 這樣的好處是, 不用去改 non-clustered index 的內容, 它仍然指向原來的位置. 不用去作異動.
c. 010: Forwarding Record:
* 有時也會被稱為 Forwarding Stub. 其定義, 如前所述.
d. 011: Index Record:
* Clustered Index 的 非 Leaf Level 節點; 或者
* Non-Clustered Index 任何 Level 節點.
e. 100: BLOB Fragment 或 Row Overflow Data
f. 101: Ghost Index Record
g. 110: Ghost Data Record:
* 當資料被刪除時, 該筆資料(clustered index leaf) 及對應的索引頁面 (non-clustered index), 會被註記為 Ghost Record.
* 同時會在 Data Page header 記錄該 page 有多少筆資是 ghost 的. 以 DBCC PAGE 的輸出結果而言, 會呈現在 m_ghostRecCnt 這個項目.
* Ghost Record 的資料, 可以透過以下3個方式清理:
+ 原來 DELETE 的 primary key (例如: id為 30), 再次被 INSERT.
+ 該 Data Page 需要被分割.
+ 每 5 秒被啟動的 Ghost Cleanup Task.
h. 111: Ghost Version Record: 此類型 Record 的大小為 Header(1 byte) + Versioning Tag(14 bytes)

C.bit 4 - 7:

bit 4: 若為1, 代表有 NULL_BITMAP 的屬性
bit 5: 若為1, 代表有 VARIABLE_COLUMNS 的屬性
bit 6: 若為1, 代表有 VERSIONING_INFO 的屬性
bit 7: 保留未用

(4) 小結: 以 0x30 而言, 代表:
A. Record Type: PRIMARY_RECORD
B. Record Attributes: NULL_BITMAP + VARIABLE_COLUMNS

區塊_2: TagB (範圍: byte 1)

RowStructure_p840_part_2

資料內容: 0x00
說明: 該 byte 的值只有 2 種,
- 0x00: 代表無 FORWARDED_GHOST 的屬性
- 0x01: 代表有 FORWARDED_GHOST 的屬性

區塊_3: 定長欄位的長度 (範圍: byte 2 - byte 3)

RowStructure_p840_part_3

資料內容: 0x1a00
A. 因為 Little Endian, 所以這個值要反過來看, 即 0x001a, 換算十進位值為 26
B. 為何是 26 bytes?
- Id: INT --> 4 bytes
- OrderDate: DATETIME --> 8 bytes
- ShipDate: DATETIME --> 8 bytes
- ShipType: CHAR(01) --> 1 byte
- Shipper: CHAR(01) --> 1 byte
- 資料本身: 合計 22 bytes
- byte 0 .. byte 3 row header --> 4 byte
- 資料本身 + row header : 合計 26 bytes

區塊_4: 定長欄位的內容 (範圍: byte 4 - byte 25, 共計 22 bytes)

RowStructure_p840_part_4

資料內容:
- Id: INT --> 4 bytes --> 0x01000000 --> Little Endian --> 0x00000001 --> 值: 1
- OrderDate: DATETIME --> 8 bytes -->
  Date: 0x5ba80000 --> Little Endian --> 0x0000a85b --> 值: 2018-01-01
  Time: 0x00000000 --> Little Endian --> 0x00000000 --> 值: 0
- ShipDate: DATETIME --> 8 bytes --> Date: 0x65a80000 --> Little Endian --> 0x0000a865 --> 值: 2018-01-11
  Time: 0x00000000 --> Little Endian --> 0x00000000 --> 值: 0
- ShipType: CHAR(01) --> 1 byte --> 0x31 --> 值: '1'
- Shipper: CHAR(01) --> 1 byte --> 0x31 --> 值: '1'

區塊_5: 欄位數量 (範圍: byte 26 - byte 27)

RowStructure_p840_part_5

資料內容: 0x0b00 --> Little Endian --> 0x000b --> 值: 11, 亦即 Orders 這個 table 共包含 11 個欄位

區塊_6: NULL_BITMAP (範圍: byte 28 - byte 29)

RowStructure_p840_part_6

因為有 11 個欄位, 所以要用 2 bytes 來表達 NULL_BITMAP
資料內容: 0x0004 --> Little Endian --> 0x0400 --> 二進位: 0000 0100 0000 0000 --> 由右向左觀察, 欄位編號以1起算, 代表欄位11 (即 Notes 欄位) 為 NULL

區塊_7: 可變長度欄位數量 (範圍: byte 30 - byte 31)

RowStructure_p840_part_7

資料內容: 0x0500 --> Little Endian --> 0x0005 --> 值: 5, 代表有 5 個可變長度的欄位
說明: 包含以下 A - E, 共 5 個欄位
A. CustomerName: VARCHAR(20)
B. ReceiverName: VARCHAR(20)
C. ReceiverTel: VARCHAR(20)
D. ReceiverMobile: VARCHAR(20)
E. ReceiverAddr: VARCHAR(60)
F. 因為 Notes: VARCHAR(MAX) 為 NULL, 記錄在 NULL_BITMAP 裡, 故不列入

區塊_8: 可變長度欄位 Offset 陣列 (範圍: byte 32 - byte 41)

RowStructure_p840_part_8

當資料中有可變長度欄位時, 每個可變長度欄位會佔用 2 bytes, 以記錄偏移量(offset), 以本例而言, 共計 5 個可變長度欄位, 所以共計用掉 10 bytes
說明: 包含以下 A - E, 共 5 個欄位的 offset.
A. CustomerName: VARCHAR(20) --> 0x3000 --> Little Endian --> 0x0030 --> 值: 48
--> byte 42 - byte 47: 'Jasper'
B. ReceiverName: VARCHAR(20) --> 0x3600 --> Little Endian --> 0x0036 --> 值: 54
--> byte 48 - byte 53: 'Jasper'
C. ReceiverTel: VARCHAR(20) --> 0x4200 --> Little Endian --> 0x0042 --> 值: 66
--> byte 54 - byte 65: '02-2123-4567'
D. ReceiverMobile: VARCHAR(20) --> 0x4e00 --> Little Endian --> 0x004e --> 值: 78
--> byte 66 - byte 77: '0968-123-456'
E. ReceiverAddr: VARCHAR(60) --> 0x5400 --> Little Endian --> 0x0054 --> 值: 84
--> byte 78 - byte 83: 'Taipei'

區塊_9: 可變長度欄位內容 (範圍: byte 42 - byte 83)

RowStructure_p840_part_9

說明: 如前所述, 包含以下 A - E, 共 5 個欄位的內容
A. CustomerName: VARCHAR(20) --> byte 42 - byte 47: 'Jasper'
B. ReceiverName: VARCHAR(20) --> byte 48 - byte 53: 'Jasper'
C. ReceiverTel: VARCHAR(20) --> byte 54 - byte 65: '02-2123-4567'
D. ReceiverMobile: VARCHAR(20) --> byte 66 - byte 77: '0968-123-456'
E. ReceiverAddr: VARCHAR(60) --> byte 78 - byte 83: 'Taipei'

小結:

實際資料內容長度為 64 bytes = 22 (第4部份) + 42 (第9部份); 但整個 slot_00 長度為 84 bytes; 這代表有 20 bytes 的 meta data.

3. UPDATE 的實地觀察 (定長欄位)

BEGIN 
    BEGIN TRANSACTION

    UPDATE  A
    SET     A.Shipper = '2'
    FROM    Orders A
    WHERE   A.Id = 01;

    DBCC TRACEON(3604);

    DBCC PAGE(Cookies, 1, 840, 3);

    ROLLBACK TRANSACTION;
END
GO

經上述實測, 與原來相比, 只有 slot_00 那個對應定長欄位的內容有不同, 其它的均沒有變.

Upd_fixed_compare

4. UPDATE 的實地觀察 (可變長度欄位)

BEGIN 
    BEGIN TRANSACTION

    UPDATE  A
    SET     A.Notes = 'These are notes'
    FROM    Orders A
    WHERE   A.Id = 01;

    DBCC TRACEON(3604);

    DBCC PAGE(Cookies, 1, 840, 3);

    ROLLBACK TRANSACTION;
END
GO

經上述實測, 發現變動很大 :
1. 起始位址: 由 0x60, 移到 0x1f0c (十進位: 7948)
(1) 代表 SQL Server 重新取得記憶體, A. 放置新的資料, B. 複製其它資料列.
(2) 注意: 雖然重新配置記憶體, 但其 page id 仍然是 #840
2. 因為異動的部份, 是 Notes 欄位:
(1) 將 NULL 改為 'These are notes'; 所以, NULL_BITMAP 的內容有變.
(2) 可變長度欄位數量: 由 0x0500 改為 0x0600
(3) 可變長度欄位 Offset 陣列: 其 size 會多 2 byte 出崍 (因為可變長度欄位數量由 5 變成 6)
(4) 變長度欄位內容: 增加 15 bytes, 以存放 'These are notes'.
(5) 故合計: 新增 2 + 15 = 17 bytes = 101 - 84

Upd_variant_compare

5. 結論

本文已盡力針對 Data Page 內部記憶體如何記錄欄位存放位置作探討.
同時, 也可以看到對於可變長度欄位作 UPDATE 的影響, 這也就是為何要盡量採用固定長度欄位的原因.
是故, 官方文件對於 char 與 varchar 的使用, 有以下準則:

(1) 當資料行資料項目的大小一致時, 請使用 char.
(2) 當資料行資料項目的大小變化相當大時, 請使用 varchar.
(3) 當資料行資料項目的大小變化相當大, 且字串長度可能超出 8,000 位元組時, 請使用 varchar(max).

因此, 若是當資料行資料項目的大小變化不大時, 還是可以用 char, 對於效能的提昇, 會有一些幫助.

6. 參考文件

01. Microsoft Technet, Clustered Index Structures
02. Microsoft Technet, Non-Clustered Index Structures
03. SQL Server Premier Field Engineer Blog, Viewing SQL Server Non-Clustered Index Page Contents
04. SQL Database Engine Blog, More undocumented fun: DBCC IND, DBCC PAGE, and off-row columns
05. SQL Database Engine Blog, How to use DBCC PAGE
06. SQL Database Engine Blog, Poking about with DBCC PAGE (Part 1 of ?)
07. Sqlity.net, The Index Allocation Map (IAM)
08. Sqlity.net, The Allocation Unit
09. Sqlity.net, Blob and Row Overflow Storage Internals – Row-Overflow Data
10. Microsoft Docs, sys.dm_db_index_physical_stats (Transact-SQL)
11. MSSQLTips, Using DBCC PAGE to Examine SQL Server Table and Index Data
12. 橄欖葉子, 資料分頁(Data Page)中的剩餘空間(m_freeCnt)是如何計算出來的?
13. 橄欖葉子, 初步瞭解Data Record結構
14. SQLServer Central, SQL Server: Understanding the Data Page Structure
15. Microsoft Docs, char 和 varchar (Transact-SQL)
16. Pinal Dave, SQL SERVER – What is Forwarded Records and How to Fix Them?
17. SQL Journey, An in-depth look at Ghost Records in SQL Server

傑士伯的IT學習之路

2019年1月6日星期日