深入 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 與非叢集索引
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 🙂
參考資料
The sys.dm_db_database_page_allocations DMF