SqlServer找頁鏈

標籤：

1.根據資料記錄查看目前記錄所在的檔案編號、page頁、以及在頁中的插槽。

樣本如下：

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

 

--注意；在64位系統中sys.fn_physlocFormatter 整理出來的格式有時候不對，需要手工根據physloc來計算，計算的方法是：

以位元組為單位倒敘，如

0x0702000001002200倒敘後是 
0x0022000100000207

前四位0022表示插槽號2*16+2 = 34，接下來的四位0001表示檔案號，餘下的00000207表示檔案號2*16*16+7 = 519

DBCC TraceOn(3604) DBCC page(資料庫名,1,40995,0) DBCC TraceOff(3604)

 

2.根據sys.system_internals_allocation_units（該視圖同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‘)

 

首頁頁碼按照16進位形式儲存，同上按位元組反序後，前兩組表示2位元組檔案編號，後4組表示頁編號。可用如下函數來返迴文件號和頁碼：

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.使用DBCC IND命令，樣本如下：

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

將返回形如下結果：

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

　　

SqlServer找頁鏈