Three methods to view physical pge pages of SqlServer data

1. view the file number, page, and slots on the page of the current record based on the data record. Example: SELECTtop10 % physloc %, sys. fn_physlocFormatter (% physloc %) ASRIDFROMtableName -- Note; in 64-bit systems, the format compiled by sys. fn_physlocFormatter is sometimes incorrect.

1. view the file number, page, and slots on the page of the current record based on the data record. Example: SELECT top 10% % physloc %, sys. fn_physlocFormatter (% physloc %) as rid from tableName -- Note; sys. the format compiled by fn_physlocFormatter is sometimes incorrect.

1. view the file number, page, and slots on the page of the current record based on the data record.

Example:

SELECT top 10 %%physloc%%, sys.fn_physlocFormatter (%%physloc%%) AS RID FROM tableName

-- Note: In a 64-bit system, the format compiled by sys. fn_physlocFormatter is sometimes incorrect. You need to manually calculate the format based on physloc. The calculation method is as follows:

In bytes, such

0x0702000001002200 is displayed after Flashback
Zero x 0022000100000207

The first four digits 0022 indicate the slot number 2*16 + 2 = 34, the next four digits 0001 indicate the file number, and the remaining 00000207 indicate the file number 2*16*16 + 7 = 519

DBCC TraceOn (3604) DBCC page (Database Name, 3604, 0) DBCC TraceOff)

2. Obtain the Home Page according to sys. system_internals_allocation_units (the view is the same as sys. allocation_units ).

select OBJECT_NAME(object_id) as Name,p.rows,a.type_desc ,a.total_pages,a.first_page,a.first_iam_page,a.root_pagefrom sys.partitions P join sys.system_internals_allocation_units a on p.partition_id = a.container_idwhere object_id = object_id('dbo.Dumplpt')

The homepage page number is saved in hexadecimal format. After the homepage is in reverse byte order, the first two groups represent two-byte file numbers, and the last four groups represent page numbers. The following functions can be used to return the file number and page number:

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;

3. Use the dbcc ind command, for example:

  DBCC IND(testLogDB,'dbo.Dumplpt',-1)

The following result is returned:

PageFIDPagePIDIAMFIDIAMPIDObjectIDIndexIDPartitionNumberPartitionIDiam_chain_typePageTypeIndexLevelNextPageFIDNextPagePIDPrevPageFIDPrevPagePID1197NULLNULL695752860172057594038976512In-row data10NULL000011961197695752860172057594038976512In-row data10000011981197695752860172057594038976512In-row data10000011991197695752860172057594038976512In-row data10000012001197695752860172057594038976512In-row data10000012011197695752860172057594038976512In-row data10000012021197695752860172057594038976512In-row data10000012031197695752860172057594038976512In-row data10000012041197695752860172057594038976512In-row data10000012081197695752860172057594038976512In-row data10000012091197695752860172057594038976512In-row data10000012101197695752860172057594038976512In-row data10000012111197695752860172057594038976512In-row data10000012121197695752860172057594038976512In-row data10000012131197695752860172057594038976512In-row data100000