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

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

--dbcc page内容解释

--author:boyi55

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

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

dbcc traceon(3064)
dbcc page(northwind,1,100,1)
go
=========================
数据文件的页面结构（管理页面比较重要）
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
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')
=================================
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 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')

=================================
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
----------- ----------- -------------- ------ -------------- ------
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