SQL Server Internals DataPage 資料頁

2021-12-11

深入 SQL Server 的內部結構，從 Table 物件至 Index 索引，到 Partition、Extent 最後抵達到 Data Page，一層一層探索 SQL Server 的內在。

說明

環境準備

本次使用 AdventureWorks2019 作為實驗用的資料庫。

USE AdventureWorks2019

SELECT * FROM sys.objects 
	WHERE 
		name = 'SalesOrderDetail' OR OBJECT_NAME(parent_object_id) = 'SalesOrderDetail'

叢集索引及其他索引

SELECT 
	OBJECT_NAME(P.object_id) AS 'ObjectName',
	P.object_id,
	P.partition_id, 
	P.index_id,
	I.name,
	I.type_desc AS 'Index_Type',
	P.partition_number,
	P.rows, 
	A.allocation_unit_id,
	A.type_desc,
	A.data_pages
	FROM sys.partitions AS P
INNER JOIN 
	sys.allocation_units AS A ON P.partition_id = A.container_id
INNER JOIN
	sys.indexes AS I ON P.index_id = I.index_id AND I.object_id = P.object_id
WHERE P.object_id = 1810105489

DROP TABLE IF EXISTS #DBCCIND 
CREATE TABLE #DBCCIND 
(
    PageFID  INT,
    PagePID  INT,
    IAMFID   INT,
    IAMPDF   INT,
    ObjectID INT,
    IndexID  INT,
    PartitionNumber INT,
    PartitionID  BIGINT,
    iam_chain_type  SYSNAME,
    PageType    INT,
    IndexLevel  INT,
    NextPageFID INT,
    NextPagePID INT,
    PrevPageFID INT,
    PrevPagePID INT
)
GO

DBCC TRACEON(3604)
INSERT INTO #DBCCIND
	EXEC( 'DBCC IND("AdventureWorks2019","Sales.SalesOrderDetail", -1) 
    WITH TABLERESULTS ')

SELECT PartitionID, IndexID, PageType, IndexLevel, Count(*) 'Count'
FROM #DBCCIND
GROUP BY IndexID, PageType, IndexLevel, PartitionID
ORDER BY IndexId ASC, PageType DESC, IndexLevel Desc

叢集索引下的索引狀態

逐層深入叢集索引

SELECT * FROM #DBCCIND WHERE IndexID = 1 AND pageType = 2

叢集索引根與中間葉

DBCC PAGE('AdventureWorks2019',1, 6376, 3)  -- Root Page

叢集索引根

DBCC PAGE('AdventureWorks2019',1, 16464, 3) -- Intermediate Page

叢集索引中間葉

DBCC PAGE('AdventureWorks2019',1, 6488, 3)  -- Data Page / Leaf Page

資料頁

叢集索引與非叢集索引

SELECT * FROM #DBCCIND WHERE IndexID = 2 AND PrevPagePID = 0

DBCC PAGE('AdventureWorks2019',1, 10728, 3) -- NonClustered Index Root

DBCC PAGE('AdventureWorks2019',1, 11048, 3) -- NCI Intermediate Page

DBCC PAGE('AdventureWorks2019',1, 10824, 3) -- NCI Leaf

Heap Table 及其他索引

ALTER TABLE Sales.SalesOrderDetail
	DROP CONSTRAINT PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
GO

SELECT * FROM #DBCCIND WHERE IndexID = 0

Heap Table 下的索引狀態

Heap Table 下的 IAM

Heap Table 資料頁

Heap Table 與非叢集索引

DBCC PAGE('AdventureWorks2019',1, 25968, 3) -- NonClustered Index Root

非叢集索引根

DBCC PAGE('AdventureWorks2019',1, 26304, 3) -- NCI Intermediate Page

非叢集索引中間葉

DBCC PAGE('AdventureWorks2019',1, 26032, 3) -- NCI Leaf

非叢集索引子葉

藉由特定的規則，可以將 HeapRId 還原為 FID:PID:Slot

DECLARE @Heaprid binary(8)
SET @Heaprid = 0x6C42000001002700
SELECT [FID:PID:Slot] =      
  CONVERT (VARCHAR(5), CONVERT(INT, SUBSTRING(@Heaprid, 6, 1)
  + SUBSTRING(@Heaprid, 5, 1)))
  + ':'
    + CONVERT(VARCHAR(10), CONVERT(INT, SUBSTRING(@Heaprid, 4, 1)
    + SUBSTRING(@Heaprid, 3, 1)
    + SUBSTRING(@Heaprid, 2, 1)
    + SUBSTRING(@Heaprid, 1, 1)))
  + ':'
    + CONVERT(VARCHAR(5), CONVERT(INT, SUBSTRING(@Heaprid, 8, 1)
    + SUBSTRING(@Heaprid, 7, 1)))

可以得到 FID:PID:Slot 1:17004:39

DBCC PAGE('AdventureWorks2019',1, 17004, 3)

藉由 DBCC PAGE 可以取得 Heap Table 中該列資料的詳盡資訊。

DBCC IND 的替代者

USE AdventureWorks2019

SELECT * FROM sys.dm_db_database_page_allocations
	(DB_ID(), Object_ID('Person.Address'), null,null, 'Detailed')

Person.Address 是一張好的示範 Table，同時存在 LOB_DATA 以及 ROW_OVERFLOW_DATA 的 Allocation Unit 🙂

參考資料

SqlServer資料表深入淺出

The sys.dm_db_database_page_allocations DMF