MSSQL 数据页查询使他 DBCC PAGE 详细说明-白红宇

MSSQL 数据页查询使他 DBCC PAGE 详细说明

阅读量：6948 次

发布时间：2019-06-27

本文共 11178 字，大约阅读时间需要 37 分钟。

----------------------------

--dbcc page内容解释

--author:boyi55

----------------------------

dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])

The printopt parameter has the following meanings:

0 - print just the page header

1 - page header plus per-row hex dumps and a dump of the page slot array (unless its a page that doesn't have one, like allocation bitmaps)

2 - page header plus whole page hex dump

3 - page header plus detailed per-row interpretation

=========================

dbcc traceon(3604)

dbcc page(northwind,1,100,1)

=========================

数据文件的页面结构（管理页面比较重要）

0 文件头页面 1 PFS 页面 2 GAM 页面 3 SGAM 页面 6 DCM 页面 7 BCM 页面

一个GAM 和一个SGAM 可以管理4GB 的数据空间

==========================

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

PAGE: (1:100)

-------------

BUFFER:

-------

内存中页面的管理信息

BUF @0x19217B80-- 内存中页号

---------------

bpage = 0x19A3C000 （对应物理页面） bhash = 0x00000000 （hash ） bpageno = (1:100) （对应物理文件的页面号）

bdbid = 6 ( 对应的数据库ID ） breferences = 0 bstat = 0x9

bspin = 0 bnext = 0x00000000

PAGE HEADER:

------------

页头：96 字节

Page @0x19A3C000

----------------

m_pageId = (1:100) （页号） m_headerVersion = 1 m_type = 1 （数据页类型，1：堆表和聚集索引的叶子节点数据）

m_typeFlagBits = 0x0 m_level = 0 （索引级别） m_flagBits = 0x8000

m_objId = 2041058307 （对应表ID ） m_indexId = 0 m_prevPage = (0:0)

m_nextPage = (0:0) pminlen = 8 m_slotCnt = 8 （页面中数据的行数，八行）

m_freeCnt = 7504 （空余空间） m_freeData = 672 （空余空间偏移量，已用空间数） m_reservedCnt = 0

m_lsn = (4:270:16) m_xactReserved = 0 m_xdesId = (0:0)

m_ghostRecCnt = 0 m_tornBits = 1

Allocation Status

-----------------

GAM (1:2) = ALLOCATED （管理盘区位于第三个页面编号2 ） SGAM (1:3) = ALLOCATED （管理混合盘区位于第四个页面编号是三）

PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED

ML (1:7) = NOT MIN_LOGGED

DATA:

-----

Slot 0, Offset 0x60

-------------------

Record Type = PRIMARY_RECORD

Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

19a3c060: 00080030 00000001 03000004 35002500 0............%.5

19a3c070: 42804580 76006500 72006500 67006100 .E.B.e.v.e.r.a.g

19a3c080: 73006500 87000000 00000000 00005f00 .e.s........._..

19a3c090: 01000100 88000000 00000000 00005f00 ............._..

19a3c0a0: 03000100 00 .....

Slot 1, Offset 0xa5

-------------------

Record Type = PRIMARY_RECORD

Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

19a3c0a5: 00080030 00000002 03000004 37002700 0............'.7

19a3c0b5: 43804780 6e006f00 69006400 65006d00 .G.C.o.n.d.i.m.e

19a3c0c5: 74006e00 00007300 00008900 5f000000 .n.t.s........._

19a3c0d5: 01000000 00000500 00008a00 5f000000 ..............._

19a3c0e5: 01000000 000700 .......

Slot 2, Offset 0xec

-------------------

Record Type = PRIMARY_RECORD

Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

19a3c0ec: 00080030 00000003 03000004 39002900 0............).9

19a3c0fc: 43804980 6e006f00 65006600 74006300 .I.C.o.n.f.e.c.t

19a3c10c: 6f006900 73006e00 8b000000 00000000 .i.o.n.s........

19a3c11c: 00005f00 09000100 8c000000 00000000 ._..............

19a3c12c: 00006900 01000100 00 .i.......

Slot 3, Offset 0x135

--------------------

Record Type = PRIMARY_RECORD

Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

19a3c135: 00080030 00000004 03000004 3f002f00 0............/.?

19a3c145: 44804f80 69006100 79007200 50002000 .O.D.a.i.r.y. .P

19a3c155: 6f007200 75006400 74006300 00007300 .r.o.d.u.c.t.s..

19a3c165: 00008d00 5f000000 01000000 00000a00 ......._........

19a3c175: 00008e00 5f000000 01000000 000b00 ......._.......

Slot 4, Offset 0x184

--------------------

Record Type = PRIMARY_RECORD

Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

19a3c184: 00080030 00000005 03000004 3f002f00 0............/.?

19a3c194: 47804f80 61007200 6e006900 2f007300 .O.G.r.a.i.n.s./

19a3c1a4: 65004300 65007200 6c006100 00007300 .C.e.r.e.a.l.s..

19a3c1b4: 00008f00 5f000000 01000000 00000c00 ......._........

19a3c1c4: 00009000 6c000000 01000000 000100 .......l.......

Slot 5, Offset 0x1d3

--------------------

Record Type = PRIMARY_RECORD

Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

19a3c1d3: 00080030 00000006 03000004 3b002b00 0............+.;

19a3c1e3: 4d804b80 61006500 2f007400 6f005000 .K.M.e.a.t./.P.o

19a3c1f3: 6c007500 72007400 00007900 00009100 .u.l.t.r.y......

19a3c203: 69000000 01000000 00000400 00009200 ...i............

19a3c213: 69000000 01000000 000500 ...i.......

Slot 6, Offset 0x21e

--------------------

Record Type = PRIMARY_RECORD

Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

19a3c21e: 00080030 00000007 03000004 31002100 0............!.1

19a3c22e: 50804180 6f007200 75006400 65006300 .A.P.r.o.d.u.c.e

19a3c23e: 93000000 00000000 00006900 06000100 .........i......

19a3c24e: 94000000 00000000 00007200 01000100 .........r......

19a3c25e: 00 .

Slot 7, Offset 0x25f

--------------------

Record Type = PRIMARY_RECORD

Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

19a3c25f: 00080030 00000008 03000004 31002100 0............!.1

19a3c26f: 53804180 61006500 6f006600 64006f00 .A.S.e.a.f.o.o.d

19a3c27f: 95000000 00000000 00006900 07000100 .........i......

19a3c28f: 96000000 00000000 00006900 08000100 .........i......

19a3c29f: 00 .

OFFSET TABLE:

-------------

Row - Offset 96 （页头）+ （672-96 ）（数据空间）+7504 （空余空间）+16 （行偏移指针数组）=8192 （页面大小）

8190-8191 slot0

...

... 行偏移数组

8176-8177 slot7

672-8175 空余空间

7 (0x7) - 607 (0x25f) 607-671

6 (0x6) - 542 (0x21e) 542-606

5 (0x5) - 467 (0x1d3) 467-541

4 (0x4) - 388 (0x184) 388-466

3 (0x3) - 309 (0x135) 309-387

2 (0x2) - 236 (0xec) 236-308

1 (0x1) - 165 (0xa5) 165-235

0 (0x0) - 96 (0x60) 96-164

0-95 pageheader

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

============================================

create table demodb1..table10(

id int not null,

name char(5) not null,

address char(10) null)

select * from demodb1..sysindexes

where id=object_id('demodb1..table10')

============================================

id status first indid root minlen keycnt groupid dpages reserved used rowcnt rowmodctr reserved3 reserved4 xmaxlen maxirow OrigFillFactor StatVersion reserved2 FirstIAM impid lockflags pgmodctr keys name statblob maxlen rows

----------- ----------- -------------- ------ -------------- ------

1977058079 0 0x000000000000 0 0x000000000000 23 0 1 0 0 0 0 0 0 0 40 0 0 0 0 0x000000000000 -1 0 0 NULL table10 NULL 8000 0

无数据 first 为指向第一列或者根页的指针。

=============================================

insert into demodb1..table10(id,name,address)values(1,'abcde','suzhou')

=================================

----------- ----------- -------------- ------ -------------- ------ ------

1977058079 0 0x0F0000000100 0 0x0F0000000100 23 0 1 1 2 2 1 1 0 0 40 0 0 0 0 0x190000000100 -1 0 0 NULL table10 NULL 8000 1

first 0x0F 编号十五页

=================================

create table table11(id int,name text)

select * from demodb1..sysindexes

where id=object_id('demodb1..table11')

insert into demodb1..table11 values(1,'boyi55')

=================================

----------- ----------- -------------- ------ -------------- ------

1993058136 0 0x1E0000000100 0 0x1E0000000100 8 0 1 1 2 2 1 1 0 0 43 0 0 0 0 0x1F0000000100 -1 0 0 NULL table11 NULL 8000 1

1993058136 2 0x1C0000000100 255 0x1C0000000100 0 0 1 0 2 2 0 0 0 0 0 0 0 0 0 0x1D0000000100 -1 0 0 NULL ttable11 NULL 8000 0

first 0x1E 编号三十页

first 0x1C 编号二十八页

=====================

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

PAGE: (1:30)

------------

BUFFER:

-------

BUF @0x192192C0

---------------

bpage = 0x19AF6000 bhash = 0x00000000 bpageno = (1:30)

bdbid = 7 breferences = 1 bstat = 0xb

bspin = 0 bnext = 0x00000000

PAGE HEADER:

------------

Page @0x19AF6000

----------------

m_pageId = (1:30) m_headerVersion = 1 m_type = 1

m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8000

m_objId = 1993058136 m_indexId = 0 m_prevPage = (0:0)

m_nextPage = (0:0) pminlen = 8 m_slotCnt = 1

m_freeCnt = 8063 m_freeData = 127 m_reservedCnt = 0

m_lsn = (5:49:1) 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

Slot 0 Offset 0x60

------------------

Record Type = PRIMARY_RECORD

Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

19af6060: 00080030 00000001 01000002 00801f00 0...............

19af6070: 0000c900 1c000000 01000000 000000 ...............

id = 1

name = [TextPointer]

------------------------------------------------

TextTimeStamp = 13172736 RowId = (1:28:0)

指针指向页号28.

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

==============================

sp_tableoption table11,'text in row',1000

update table11 set name='boyi55555' where id =1

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

PAGE: (1:30)

------------

BUFFER:

-------

BUF @0x192192C0

---------------

bpage = 0x19AF6000 bhash = 0x00000000 bpageno = (1:30)

bdbid = 7 breferences = 4 bstat = 0xb

bspin = 0 bnext = 0x00000000

PAGE HEADER:

------------

Page @0x19AF6000

----------------

m_pageId = (1:30) m_headerVersion = 1 m_type = 1

m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8000

m_objId = 1993058136 m_indexId = 0 m_prevPage = (0:0)

m_nextPage = (0:0) pminlen = 8 m_slotCnt = 1

m_freeCnt = 8070 m_freeData = 127 m_reservedCnt = 7

m_lsn = (5:52:5) m_xactReserved = 7 m_xdesId = (0:188)

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

Slot 0 Offset 0x60

------------------

Record Type = PRIMARY_RECORD

Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

19af6060: 00080030 00000001 01000002 62001800 0..............b

19af6070: 3569796f 35353535 oyi55555

id = 1

********************************************************

name = [BLOB Inline Data]

-----------------------------------------------------

19af606f: 69796f62 35353535 35 boyi55555

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

=============================================

DBCC EXTENTINFO 得到属于一个对象的所有盘区的列表

dbcc traceon(3604)

dbcc extentinfo(northwind,categories)

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

file_id page_id pg_alloc ext_size obj_id index_id pfs_bytes

----------- ----------- ----------- ----------- ----------- ----------- ------------------

1 98 1 1 2041058307 1 0x6000000000000000

1 100 1 1 2041058307 1 0x6000000000000000

1 389 1 1 2041058307 2 0x6000000000000000

1 95 1 1 2041058307 255 0x6200000000000000

1 97 1 1 2041058307 255 0x6400000000000000

1 103 1 1 2041058307 255 0x6400000000000000

1 104 1 1 2041058307 255 0x6400000000000000

1 105 1 1 2041058307 255 0x6200000000000000

1 106 1 1 2041058307 255 0x6400000000000000

1 107 1 1 2041058307 255 0x6400000000000000

1 108 1 1 2041058307 255 0x6200000000000000

1 112 4 8 2041058307 255 0x4444424400000000

（所影响的行数为 12 行）

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

page_id 对应于obj_id

dbcc traceon(3604)

dbcc extentinfo(northwind)

用法：DBCC EXTENTINFO(dbname,tablename,indexid)

from csdn

转载地址：http://nyenl.baihongyu.com/

你可能感兴趣的文章

11 SVM - SMO - 序列最小优化算法

浅谈视频监控在智慧城市建设中的综合治理业务

查看>>

思科HyperFlex系统提供最完整的新一代超融合解决方案

查看>>

中国电信明确物联网技术路标：7月启动Cat1

查看>>

市场需求持续增长，云计算或将成为资本市场下一个焦点

查看>>

《 FreeSWITCH权威指南》——3.6　小结

高通每天提供超过一百万颗芯片助力物联网发展

查看>>

Python Selenium的js扩展实现

查看>>

全球最大规模窄带物联网智慧水务商用项目在福州启动

查看>>

iDTRONIC推出RFID平板和手持移动设备

查看>>

圆通速递率先推出隐形面单为个人信息安全再添安全锁

查看>>

当200亿个物联网设备同时产生数据是时候重新认识传感器了

查看>>

深度专访：深谈的故事 (LinuxDeepin)

查看>>

《构建高可用Linux服务器第3版》—— 1.2　全面了解Linux服务器

查看>>