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

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

--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)
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 （数据页类型，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