[SQL Server] How to view the page content with DBCC PAGE (1) : 基礎篇

0. 前言

將近2年前, 筆者曾針對 non-clustered index 的 page 結構, 在 facebook 的 Super SQL Server 社團中詢問, 如本連結, 並非常感謝 Levis Yang, 劉修仁 ... 等前輩的指點, 而有了一些瞭解.

一直以來, 很想對 SQL Server index page 及 data page 的結構及內容, 都有很大的好奇心; 直到最近, 才有機會作一些整理.

對此主題有興趣, 但對 SQL Server 的 index 仍不是很熟悉的朋友, 建議先閱讀參考文件01 及參考文件02.

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

1. 建立測試資料

   
-- ========================
-- 建立資料庫
-- ========================
IF  NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'Cookies')
BEGIN
    CREATE DATABASE Cookies;
END;

USE Cookies;
GO

-- ========================
-- 移除 Table: dbo.Orders
-- ========================
DROP TABLE IF EXISTS dbo.Orders;
GO

-- ========================
-- 建立 Table: dbo.Orders
-- ========================
CREATE TABLE dbo.Orders
(    Id                  INT             NOT NULL
,    OrderDate           DATETIME        NOT NULL
,    ShipDate            DATETIME        NOT NULL
,    ShipType            CHAR(01)        NOT NULL    -- 運送方式: 1-常温, 2-冷藏, 3-冷凍
,    Shipper             CHAR(01)        NOT NULL    -- 貨運公司: 1-黑貓, 2-新竹貨運, 3-郵局
,    CustomerName        VARCHAR(20)     NOT NULL    -- 下單人姓名
,    ReceiverName        VARCHAR(20)     NOT NULL    -- 收貨人姓名
,    ReceiverTel         VARCHAR(20)     NOT NULL    -- 收貨人電話
,    ReceiverMobile      VARCHAR(20)     NOT NULL    -- 收貨人手機
,    ReceiverAddr        VARCHAR(60)     NOT NULL    -- 收貨人地址
,    Notes               VARCHAR(MAX)                -- 備註

CONSTRAINT PK_Orders  PRIMARY KEY CLUSTERED (Id)
) ;
GO

-- ========================
-- 建立 NON-CLUSTERED INDEX
-- ========================
CREATE NONCLUSTERED INDEX IX_Orders_OrderDate
    ON dbo.Orders (OrderDate);  
GO

CREATE NONCLUSTERED INDEX IX_Orders_ShipDate
    ON dbo.Orders (ShipDate);  
GO

-- ========================
-- 新增資料
-- ========================

DECLARE @ls_notes VARCHAR(MAX) = '';

SET @ls_notes = 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod
    tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam,
    quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo
    consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse
    cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non
    proident, sunt in culpa qui officia deserunt mollit anim id est laborum.';

INSERT INTO [dbo].[Orders]
VALUES   ( 01, '2018-01-01', '2018-01-11', '1', '1', 'Jasper', 'Jasper', '02-2123-4567', '0968-123-456', 'Taipei', NULL)
,        ( 02, '2018-01-02', '2018-01-12', '2', '2', 'Judy', 'Judy', '02-2123-4567', '0968-123-456', 'Taichuang', @ls_notes)
,        ( 03, '2018-01-03', '2018-01-13', '3', '3', 'Annie', 'Annie', '02-2123-4567', '0968-123-456', 'Tainan', @ls_notes)
,        ( 04, '2018-01-04', '2018-01-14', '1', '1', 'Bill', 'Bill', '02-2123-4567', '0968-123-456', 'Kaohsiung', @ls_notes)
,        ( 05, '2018-01-05', '2018-01-15', '2', '2', 'John', 'John', '02-2123-4567', '0968-123-456', 'Taipei', @ls_notes)
,        ( 06, '2018-01-06', '2018-01-16', '3', '3', 'Anita', 'Anita', '02-2123-4567', '0968-123-456', 'Taipei', @ls_notes)
,        ( 07, '2018-01-07', '2018-01-17', '1', '2', 'Vivid', 'Vivid', '02-2123-4567', '0968-123-456', 'Tainan', @ls_notes)
,        ( 08, '2018-01-08', '2018-01-18', '2', '3', 'Jeff', 'Jeff', '02-2123-4567', '0968-123-456', 'Taipei', @ls_notes)
,        ( 09, '2018-01-09', '2018-01-19', '3', '1', 'Mark', 'Mark', '02-2123-4567', '0968-123-456', 'Taipei', @ls_notes)
,        ( 10, '2018-01-10', '2018-01-20', '1', '1', 'Linda', 'Linda', '02-2123-4567', '0968-123-456', 'Taipei', @ls_notes)
,        ( 11, '2018-01-11', '2018-01-21', '1', '1', 'Jasper', 'Jasper', '02-2123-4567', '0968-123-456', 'Taipei', @ls_notes)
,        ( 12, '2018-01-12', '2018-01-22', '2', '2', 'Judy', 'Judy', '02-2123-4567', '0968-123-456', 'Taichuang', @ls_notes)
,        ( 13, '2018-01-13', '2018-01-23', '3', '3', 'Annie', 'Annie', '02-2123-4567', '0968-123-456', 'Tainan', @ls_notes)
,        ( 14, '2018-01-14', '2018-01-24', '1', '1', 'Bill', 'Bill', '02-2123-4567', '0968-123-456', 'Kaohsiung', @ls_notes)
,        ( 15, '2018-01-15', '2018-01-25', '2', '2', 'John', 'John', '02-2123-4567', '0968-123-456', 'Taipei', @ls_notes)
,        ( 16, '2018-01-16', '2018-01-26', '3', '3', 'Anita', 'Anita', '02-2123-4567', '0968-123-456', 'Taipei', @ls_notes)
,        ( 17, '2018-01-17', '2018-01-27', '1', '2', 'Vivid', 'Vivid', '02-2123-4567', '0968-123-456', 'Tainan', @ls_notes)
,        ( 18, '2018-01-18', '2018-01-28', '2', '3', 'Jeff', 'Jeff', '02-2123-4567', '0968-123-456', 'Taipei', @ls_notes)
,        ( 19, '2018-01-19', '2018-01-29', '3', '1', 'Mark', 'Mark', '02-2123-4567', '0968-123-456', 'Taipei', @ls_notes)
,        ( 20, '2018-01-20', '2018-01-30', '1', '1', 'Linda', 'Linda', '02-2123-4567', '0968-123-456', 'Taipei', @ls_notes)
;
GO

2. 逐步探索

2.1 列出所有 index 的結構


-- ========================
-- 檢查索引的層級結構
-- ========================
-- [目的] 建立 2 個 CTE 物件 (depth_results 及 index_results) , 再進行 JOIN, 以取得索引的層級結構
-- [參考]
-- * sys.dm_db_index_physical_stats (Transact-SQL)
--   https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql?view=sql-server-2017
--
WITH depth_results
AS
(    SELECT
        [index_id],
        [index_type_desc],
        [index_depth],
        [index_level],
        [page_count],
        [record_count]
    FROM    sys.dm_db_index_physical_stats    (
            DB_ID (N'Cookies'),            -- database_id
            OBJECT_ID (N'dbo.Orders'),     -- object_id
            NULL,                          -- index_id
            NULL,                          -- partition_number
            'DETAILED' )                   -- mode
),
index_results
AS
(    SELECT *
     FROM   sys.indexes
     WHERE object_id = object_id('dbo.Orders')
)

SELECT A.*, B.name
FROM depth_results A
LEFT JOIN index_results B ON (A.index_id = B.index_id)
GO

-- ========================
-- 列出所有 dbo.Orders 的所有 data page 及 index page
-- ========================
-- [參考]
-- * Using DBCC PAGE to Examine SQL Server Table and Index Data
--   https://www.mssqltips.com/sqlservertip/1578/using-dbcc-page-to-examine-sql-server-table-and-index-data/#comments
-- * Index Allocation Map
--   https://sqlity.net/en/2315/index-allocation-map/
-- * Allocation Unit
--   https://sqlity.net/en/2287/allocation-unit/
-- * Row-Overflow Data
--   https://sqlity.net/en/1051/blob-and-row-overflow-storage-internals-row-overflow-data/
DBCC IND(    'Cookies'     -- database name
        ,    'dbo.Orders'  -- table name
        ,    -1            -- index id, -- an index_id from sys.indexes; -1 shows all indexes and IAMs, -2 just show IAMs
)
GO

執行結果如下圖

可以一併參考 4. 補充資料(2018.11.23) pages 架構圖, 這樣會比較容易理解

解讀 result #1

(1) 索引深度: PK_Orders 有 2 層; IX_Orders_OrderDate, IX_Orders_ShipDate 各為 1 層
(2) page_count 合計總共有 5 (= 2 + 1 + 1+ 1)個 pages (含 data page 與 index page)

解讀 result #2 (part 1): resultset 欄位說明

(1) PageFID: page 所存在的檔案編號
(2) PagePID: page 在該檔案裡的編號

前述 2 個參數, 在後續 DBCC PAGE 指令會用到; 在後續 DBCC PAGE 的輸出中, 係以 (file_id:page_id) 作為唯一識別, 例如: (1:145)

(3) IAMFID: page 所屬的 IAM 檔案編號 (若該 page 本身是 IAM PAGE, 則該欄位會是 NULL)
(4) IAMPID: page 所屬的 IAM Page 編號 (若該 page 本身是 IAM PAGE, 則該欄位會是 NULL)
(5) ObjectID: 物件的 ID, 以本例而言, 此為 table:Orders 的 table id
(6) IndexID: Index 編號; 1 為 clustered index; >1 為 non-clustered index
(7) PartitionNumber: table or index 所在的 partition nubmer; 如何建立 partition, 可以參考筆者的 SQL Server: 如何建立 Partition Table
(8) PartitionID: 各個 partion 裡, 各個物件的內部識別碼; 以本例而言, 每個 index 都有一個識別碼
(9) IAM-Chain-Type: IN_ROW_DATA / LOB_DATA / ROW_OVERFLOW_DATA
(10) PageType:
- 1 – data page
- 2 – index page
- 3 and 4 – text pages (即: row-overflow pages)
- 8 – GAM page
- 9 – SGAM page
- 10 – IAM page
- 11 – PFS page
(11) IndexLevel: 0 為 leaf level; 最大值 N 為 root level; 1 - N-1 為 intermediate level
(12) (NextPageFID, NextPagePID): 該 page 的後一個 page 的識別碼, 例如: (1:840) 的 next 為 (1:842)
(13) (PrevPageFID, PrevPagePID): 該 page 的前一個 page 的識別碼, 例如: (1:842) 的 prev 為 (1:840)

解讀 result #2 (part 2): 結果描述

(1) PageType: 10 為 IAM (Index Allocation Map), 1 為 data page, 2 為 index page --> 由此計算, 符合 result #1 共有 5 個 pages 的結果 ( 3 個 index page, 2 個 data page)
page #145, #146, #147 為 IAM page; 各自對應到 for INDEX: PK_Orders (IndexID=1), IX_Orders_OrderDate (IndexID=2), IX_Orders_ShipDate (IndexID=3)
(3) page #841 為 PK_Orders 的 INDEX PAGE, 內含 2 筆資料 (稍後會再作說明)
(4) page #840, #842 為 PK_Orders 的 DATA PAGE, 共含 20 筆資料.
(5) page #848, #856 分別為 IX_Orders_OrderDate, IX_Orders_ShipDate 的 INDEX PAGE, 各自含 20 筆的資料; 用以指向 CLUSTERD INDEX 的 leaf (即 data page)

2.2 查看 page 的內容 (IAM page, Index page, Data Page)

關於 DBCC TRACEON(3604), 係因為某些 DBCC 指令的輸出媒體為 log, attached debugger, 或 trace listener. 這個指令是將後續 DBCC 指令執行結果, 轉向到送出指令的客戶端的程式, 例如: SQL Server Management Studio (SSMS). 這是 Session Level 的狀態, 如果想要取消, 可以關閉這個 Session; 或者下達 DBCC TRACEON(3604, -1).

關於 DBCC PAGE 指令參數:

資料庫名稱: 可以是資料庫名稱或資料庫ID
檔案編號: page 所存在的檔案編號 (the file number where the page is found)
頁編號: page 編號 (the page number within the file)
輸出選項: [0|1|2|3] 會有不同的輸出結果
- 0 – 列出 page header
- 1 – 列出 page header + per-row hex dumps + a dump of the page slot array
- 2 – 列出 page header + whole page hex dump
- 3 – 列出 page header + detailed per-row interpretation
WITH TABLERESULTS 選項: 以表格的方式, 呈現執行結果

2.2.1 IAM page

IAM page 主要係在提供那些頁面已被配置 (註: 1 個 IAM 至少管理 8 個 page 的空間).

-- ========================
-- 查看 page 的內容
-- ========================
-- * DBCC TRACEON(3604)
--   https://blogs.msdn.microsoft.com/askjay/2011/01/21/why-do-we-need-trace-flag-3604-for-dbcc-statements/
-- * Using DBCC PAGE to Examine SQL Server Table and Index Data
--   https://www.mssqltips.com/sqlservertip/1578/using-dbcc-page-to-examine-sql-server-table-and-index-data/
-- * How to use DBCC PAGE 
--   https://blogs.msdn.microsoft.com/sqlserverstorageengine/2006/06/10/how-to-use-dbcc-page/
-- * Poking about with DBCC PAGE (Part 1 of ?) 
--   https://blogs.msdn.microsoft.com/sqlserverstorageengine/2006/08/08/poking-about-with-dbcc-page-part-1-of/

DBCC TRACEON(3604);
GO

-- ------------------------
-- IAM pages
-- ------------------------

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

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

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

執行結果如下圖

page 145: 管理 (1:840) 至 (1:847)
page 146: 管理 (1:848) 至 (1:855)
page 147: 管理 (1:856) 至 (1:863)

2.2.2 CLUSTERED: INDEX and DATA pages


DBCC TRACEON(3604);
GO

-- ------------------------
-- CLUSTERED: INDEX and DATA pages
-- ------------------------

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

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

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

------ 執行結果(總圖) ------

page #841 為 index page; 它有 2 個 child data page: #840, #842
(1) 840 的 PKEY 起始值為 NULL (代表起點)
(2) 842 的 pkey 起值值為 16

以測試資料而言,
(1) 訂單編號 1 - 15 的資料在 page #840,
(2) 訂單編號 16 - 20 的資料在 page #842

----- 執行結果(細圖)(INDEX PAGE) ------

page #841:

m_level=1: 代表所在的 index level 為 1; 由於資料量不多, 此即為 clustered-index 的 root page
m_slotCnt=2: 代表共有 2 筆索引配置資料 (如前所述)
slot 0 length 11 INDEX_RECORD: 由 offset 0x60 至 0x6a, 共 11 個 bytes
slot 1 length 11 INDEX_RECORD: 由 offset 0x6a 至 0xc4, 共 11 個 bytes

----- 執行結果(細圖)(DATA PAGE) ------

* page #840:

m_level=0: 代表所在的 index level 為 0; 此即 clustered-index 的 leaf page 之一
m_slotCnt=15: 代表共有 15 筆資料
m_nextPage=(1:842): 代表後一個 page 是 (1:842)
slot 0 length 84 PRIMARY_RECORD: 由 offset 0x60 至 0xb3, 共 84 個 bytes; 內容解釋如上圖 DATA PAGE (1:840) part #3
slot 1 length 556 PRIMARY_RECORD: 由 offset 0xb4 至 0x2df, 共 556 個 bytes; 內容解釋如上圖 DATA PAGE (1:840) part #4; 不過要特別留意的是 Notes 欄位有值, 但因為資料實際內容, 仍然可以放在 data page 裡, 所以是 [BLOB Inline Data]

* page #842:

m_level=0: 代表所在的 index level 為 0; 此即 clustered-index 的 leaf page 之一
m_slotCnt=5: 代表共有 5 筆資料
m_prevPage=(1:840): 代表前一個 page 是 (1:840)
slot 0 length 555 PRIMARY_RECORD: 由 offset 0x60 至 0x28a, 共 555 個 bytes; 內容解釋如上圖 DATA PAGE (1:842) part #2; 不過要特別留意的是 Notes 欄位有值, 但因為資料實際內容, 仍然可以放在 data page 裡, 所以是 [BLOB Inline Data]

2.2.3 NON-CLUSTERED: INDEX pages

DBCC TRACEON(3604);
GO

-- ------------------------
-- CLUSTERED: INDEX and DATA pages
-- ------------------------

-- IX_Orders_OrderDate
DBCC PAGE(Cookies, 1, 848, 3)
WITH TABLERESULTS
GO
-- IX_Orders_ShipDate
DBCC PAGE(Cookies, 1, 856, 3)
WITH TABLERESULTS
GO

* page #848: IX_Orders_OrderDate

以第0筆資料為例, 2018-01-01 對應到 CLUSTERED INDEX 進行 key lookup 時採用的 Id (key) 為 1

* page #856: IX_Orders_ShipDate

以第0筆資料為例, 2018-01-11 對應到 CLUSTERED INDEX 進行 key lookup 時採用的 Id (key) 為 1

3. 結論

本文只是針對 DBCC PAGE 的查詢結果作說明, 並未對 data page 內部記憶體如何記錄欄位存放位置作探討. 關於此議題, 可以閱讀參考文件13 及參考文件06, 筆者有時間, 會對此作進一步的探討, 雖然已有前輩撰寫過, 但總是自身有經驗過, 並寫成文章, 才可以加深自己的印象.
補充: (2018.01.06) 已撰寫完成進階篇 [SQL Server] How to view the page content with DBCC PAGE (2) : 進階篇

4. 補充資料(2018.11.23)

茲附上 pages 架構圖, 以利閱讀理解.

5. 參考文件

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結構

傑士伯的IT學習之路

2018年9月4日星期二