转载自“老侯的菜园子” 博客：
http://www.voidcn.com/article/p-exukmyud-kn.html
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 dbcc traceon(3604)
dbcc extentinfo(northwind)
用法：DBCC EXTENTINFO(dbname,tablename,indexID) 总结
以上是内存溢出为你收集整理的sqlserver:dbcc page全部内容，希望文章能够帮你解决sqlserver:dbcc page所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）