HoneyHoney

SQL SERVER的index是按照B+tree来存储的。在dm_db_database_page_allocations可以看到这类page的type是2，即index page。
每个index page存储着key - pointer关系，其childPage可能还是intermediate page，也可能是leaf page。leaf page就存储着具体的data。

如下图中的page 623876就是一个intermediate page。

其数据即存储着ChildPageId和key pointer。比如这个例子中，id小于477的，都存在page 623874上。其中level表示该page是root node page。

用fn_PhysLocCracker来实验一下，就可以验证上面的观点。

DROP TABLE LargeTable

CREATE TABLE LargeTable (keyval int, 
    dataval int,
    constraint pk_largetable primary key (keyval)
    )

INSERT INTO LargeTable(keyval, dataval)
select n, rand(10) 
from dbo.GetNums(10000000)


SELECT allocated_page_file_id as PageFID, allocated_page_page_id as PagePID,
       object_id as ObjectID, partition_id AS PartitionID,
       allocation_unit_type_desc as AU_type, page_type as PageType
FROM sys.dm_db_database_page_allocations(db_id('event_service'), object_id('LargeTable'),
                                          1, null, 'DETAILED');
GO

DBCC TRACEON (3604); 
GO 
DBCC PAGE ('event_service', 1, 623876, 3); 
GO

DBCC TRACEON (3604); 
GO 
DBCC PAGE ('event_service', 1, 608307, 3); 
GO

select *
from LargeTable t
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) AS flc
WHERE keyval < 478