PAGE: (1:1)
BUFFER:
BUF @0x02BB582C
bpage = 0x03772000 bhash = 0x00000000 bpageno = (1:1)
bdbid = 1 breferences = 1 bUse1 = 42182
bstat = 0xc0000b blog = 0x21598979 bnext = 0x00000000
PAGE HEADER:
Page @0x03772000
m_pageId = (1:1) m_headerVersion = 1 m_type = 11
m_typeFlagBits = 0x3 m_level = 0 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 99 m_indexId (AllocUnitId.idInd) = 0 Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0 Metadata: IndexId = 0 Metadata: ObjectId = 99
m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 0
m_slotCnt = 1 m_freeCnt = 2 m_freeData = 8188
m_reservedCnt = 0 m_lsn = (199:344:5) m_xactReserved = 0
m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = 351018853
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL
DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
There are a bunch of things on this that are beyond the scope of this post to explain - I'll get to them in future posts. One thing you'll notice is that there's some interpretation of the object and index IDs that are stamped on the page. This is because in SQL Server 2005, these are derived from the allocation unit ID, not the actual object and index ID. Simplistically, this is because of partitioning - each index can now have multiple b-trees, and hence multiple IAM chains. Each IAM chain is called an allocation unit. In the next post I'll go into some detail on how this is structured. Chicken-and-egg problem again, or a teaser to keep reading the blog :-)
Now how about if we ask for the detailed dump of the PFS page?
DBCC
PAGE (master, 1, 1, 3);
GO
PAGE: (1:1)
BUFFER:
BUF @0x02BB582C
bpage = 0x03772000 bhash = 0x00000000 bpageno = (1:1)
bdbid = 1 breferences = 1 bUse1 = 42182
bstat = 0xc0000b blog = 0x21598979 bnext = 0x00000000
PAGE HEADER:
Page @0x03772000
m_pageId = (1:1) m_headerVersion = 1 m_type = 11
m_typeFlagBits = 0x3 m_level = 0 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 99 m_indexId (AllocUnitId.idInd) = 0 Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0 Metadata: IndexId = 0 Metadata: ObjectId = 99
m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 0
m_slotCnt = 1 m_freeCnt = 2 m_freeData = 8188
m_reservedCnt = 0 m_lsn = (199:344:5) m_xactReserved = 0
m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = 351018853
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL
DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED
PFS: Page Alloc Status @0x4414C000
(1:0) - (1:3) = ALLOCATED 100_PCT_FULL
(1:4) - (1:5) = NOT ALLOCATED 0_PCT_FULL
(1:6) - (1:7) = ALLOCATED 100_PCT_FULL
(1:8) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:9) - (1:10) = ALLOCATED 100_PCT_FULL Mixed Ext
(1:11) - = ALLOCATED 0_PCT_FULL Mixed Ext
(1:12) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext
<deleted to keep this post from being enormous>
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
How about being able to dump some pages from a user table? Firstly, you have to work out which pages comprise the table and index. Here's one way to do this (without having to divulge any more undocumented commands :-)
- create an empty database
- do a type 3 page dump of the first page PFS page in the database (1:1) using DBCC PAGE
- creating a simple heap and insert a few rows into it
- do another PFS dump like in step 2 and you'll see some more pages have been allocated - these are the ones being used to store your new table. One of them will be the IAM page and another will be a data page.
Find some pages to dump and play about with DBCC PAGE. Experiment with adding indexes and LOB columns to see what different kinds of pages are created and look at the linkages between them. I'll go into what the various parts of the output mean in another post - in the meantime, if there's anything in particular you want to know, add a comment and I'll reply.
Have fun!