2021-12-11

SQL Server Internals DataPage 資料頁

說明
參考資料
相關連結

深入 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

說明