>데이터 베이스 >MySQL 튜토리얼 >三种查看SqlServer中数据物理pge页的方法

三种查看SqlServer中数据物理pge页的方法

WBOY
WBOY원래의
2016-06-07 15:34:301297검색

1.根据数据记录查看当前记录所在的文件编号、page页、以及在页中的插槽。 示例如下: SELECT top 10 %%physloc%% , sys.fn_physlocFormatter ( %%physloc%% ) AS RID FROM tableName --注意;在64位系统中sys.fn_physlocFormatter 整理出来的格式有时候不对

1.根据数据记录查看当前记录所在的文件编号、page页、以及在页中的插槽。

示例如下:

SELECT top <span>10</span> 
%%physloc%%<span>, 
sys.fn_physlocFormatter (</span>%%physloc%%<span>) AS RID 
FROM tableName</span>

 

--注意;在64位系统中sys.fn_physlocFormatter 整理出来的格式有时候不对,需要手工根据physloc来计算,计算的方法是:

以字节为单位倒叙,如

0x0702000001002200倒叙后是 
0x0022000100000207

前四位0022表示插槽号2*16+2 = 34,接下来的四位0001表示文件号,余下的00000207表示文件号2*16*16+7 = 519

DBCC TraceOn(<span>3604</span><span>) 
DBCC page(数据库名,</span><span>1</span>,<span>40995</span>,<span>0</span><span>) 
DBCC TraceOff(</span><span>3604</span>)

 

2.根据sys.system_internals_allocation_units(该视图同sys.allocation_units)获得首页

<span>select</span> OBJECT_NAME(object_id) <span>as</span><span> Name,p.rows,a.type_desc ,a.total_pages,a.first_page
,a.first_iam_page,a.root_page
</span><span>from</span><span> sys.partitions P 
join sys.system_internals_allocation_units a on p.partition_id </span>=<span> a.container_id
</span><span>where</span> object_id = object_id(<span>'</span><span>dbo.Dumplpt</span><span>'</span>)

 

首页页码按照16进制形式保存,同上按字节反序后,前两组表示2字节文件编号,后4组表示页编号。可用如下函数来返回文件号和页码:

CREATE FUNCTION convert_page_nums (@page_num binary(<span>6</span><span>)) 
RETURNS varchar(</span><span>11</span><span>) 
AS 
BEGIN 
RETURN(convert(varchar(</span><span>2</span>), (convert(<span>int</span>, substring(@page_num, <span>6</span>, <span>1</span><span>)) 
</span>* power(<span>2</span>, <span>8</span>)) +<span> 
(convert(</span><span>int</span>, substring(@page_num, <span>5</span>, <span>1</span>)))) + <span>'</span><span>:</span><span>'</span> +<span> 
convert(varchar(</span><span>11</span><span>), 
(convert(</span><span>int</span>, substring(@page_num, <span>4</span>, <span>1</span>)) * power(<span>2</span>, <span>24</span>)) +<span> 
(convert(</span><span>int</span>, substring(@page_num, <span>3</span>, <span>1</span>)) * power(<span>2</span>, <span>16</span>)) +<span> 
(convert(</span><span>int</span>, substring(@page_num, <span>2</span>, <span>1</span>)) * power(<span>2</span>, <span>8</span>)) +<span> 
(convert(</span><span>int</span>, substring(@page_num, <span>1</span>, <span>1</span><span>)))) ) 
END;</span>

3.使用DBCC IND命令,示例如下:

  DBCC IND(testLogDB,<span>'</span><span>dbo.Dumplpt</span><span>'</span>,-<span>1</span>)

将返回形如下结果:

PageFID	PagePID	IAMFID	IAMPID	ObjectID	IndexID	PartitionNumber	PartitionID	iam_chain_type	PageType	IndexLevel	NextPageFID	NextPagePID	PrevPageFID	PrevPagePID
1	197	NULL	NULL	69575286	0	1	72057594038976512	In-row data	10	NULL	0	0	0	0
1	196	1	197	69575286	0	1	72057594038976512	In-row data	1	0	0	0	0	0
1	198	1	197	69575286	0	1	72057594038976512	In-row data	1	0	0	0	0	0
1	199	1	197	69575286	0	1	72057594038976512	In-row data	1	0	0	0	0	0
1	200	1	197	69575286	0	1	72057594038976512	In-row data	1	0	0	0	0	0
1	201	1	197	69575286	0	1	72057594038976512	In-row data	1	0	0	0	0	0
1	202	1	197	69575286	0	1	72057594038976512	In-row data	1	0	0	0	0	0
1	203	1	197	69575286	0	1	72057594038976512	In-row data	1	0	0	0	0	0
1	204	1	197	69575286	0	1	72057594038976512	In-row data	1	0	0	0	0	0
1	208	1	197	69575286	0	1	72057594038976512	In-row data	1	0	0	0	0	0
1	209	1	197	69575286	0	1	72057594038976512	In-row data	1	0	0	0	0	0
1	210	1	197	69575286	0	1	72057594038976512	In-row data	1	0	0	0	0	0
1	211	1	197	69575286	0	1	72057594038976512	In-row data	1	0	0	0	0	0
1	212	1	197	69575286	0	1	72057594038976512	In-row data	1	0	0	0	0	0
1	213	1	197	69575286	0	1	72057594038976512	In-row data	1	0	0	0	0	0

  

성명:
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.