创建测试表
Use Test
create table dbo.employee(
emp_lname varchar(12) not null,
emp_fname varchar(12)not null,
address varchar(30)not null,
phone char(12) not null,
job_level smallint not null
)
从以下视图中获取页的地址信息
SELECT object_name(object_id) as name,
rows,type_desc as page_type_desc,total_pages as Pages,first_page
FROM sys.partitions p
join sys.system_internals_allocation_units a
on p.partition_id = a.container_id
where P.object_id = object_id('dbo.employee')
创建通过页地址获取页ID
CREATE FUNCTION convert_page_nums(@page_num binary(6))
RETURNS VARCHAR(11)
AS
BEGIN
RETURN(
CONVERT(VARCHAR(2),(
CONVERT(INT,SUBSTRING(@page_num,6,1))* POWER(2,8)
) +
(CONVERT(INT,SUBSTRING(@page_num,5,1)))
) + ':' +
CONVERT(VARCHAR(11),
(CONVERT(INT,SUBSTRING(@page_num,4,1)) * POWER(2,24)) +
(CONVERT(INT,SUBSTRING(@page_num,3,1)) * POWER(2,16)) +
(CONVERT(INT,SUBSTRING(@page_num,2,1)) * POWER(2,8)) +
(CONVERT(INT,SUBSTRING(@page_num,1,1)))
)
)
END
将上部得到的first_page 当参数传入
select [dbo].[convert_page_nums](0x970000000100)
得到结果：1:151
还有一种方法得到PageID
dbcc ind(Test,employee, -1)
根据PageID找出页中的详细信息
dbcc traceon(3604)
dbcc page (Test,1,151,1)
结果分四个段落：Buffer, Page Header, Data 和 Offset Table (真正的偏移矩阵)。Buffer段落显示了给定页面的缓冲信息。在此Buffer时指一中内存中的结构，用于管理页面，此段的信息仅当该页面处于内存中才有意义。
显示结果：
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
PAGE: (1:151)
BUFFER:
BUF @0x0000000085FA7680
bpage = 0x000000008513C000 bhash = 0x0000000000000000 bpageno = (1:151)
bdbid = 15 breferences = 0 bcputicks = 0
bsampleCount = 0 bUse1 = 41047 bstat = 0xc0000b
blog = 0x32159bb bnext = 0x0000000000000000
PAGE HEADER:
Page @0x000000008513C000
m_pageId = (1:151) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 36 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594040287232
Metadata: PartitionId = 72057594039238656 Metadata: IndexId = 0
Metadata: ObjectId = 261575970 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 18 m_slotCnt = 1 m_freeCnt = 8062
m_freeData = 128 m_reservedCnt = 0 m_lsn = (21:480:20)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:1) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
DATA:
Slot 0, Offset 0x60, Length 32, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 32
Memory Dump @0x000000001023A060
0000000000000000: 30001200 64202020 20202020 20202020 †0...d
0000000000000010: 01000500 0003001e 001f0020 00616263 †........... .abc
OFFSET TABLE:
Row - Offset
0 (0x0) - 96 (0x60)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.