(The Curious Case of… used to be part of our bi-weekly newsletter but we decided to make it a regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.)
Earlier today I was answering a question on the #sqlhelp tag on Twitter and I mentioned using object ID 99 as a value, because SQL Server will never set a table to be object ID 99. And so I thought it would make a good topic for a quick blog post.
So what is object ID 99? It’s a reserved object ID that the Storage Engine uses for any data file pages that are part of the allocation system and not owned by a real table. The list of such pages includes things like file header pages, PFS pages, GAM pages, the boot page, and so on – the pages used by the allocation system to track what’s happening at the database level. It doesn’t include IAM pages, as those are part of a table.
You can read more about the various allocation pages in my post Inside The Storage Engine: GAM, SGAM, PFS and other allocation maps.
Let’s look at the page header of a GAM page:
DBCC TRACEON (3604); DBCC PAGE (N'master', 1, 2, 0); -- dbname, file ID, page ID, dump style -- Page (1,2) is the first GAM page in a data file -- Dump style 0 = just the page header GO
Page @0x00000002F83A6000 m_pageId = (1:2) m_headerVersion = 1 m_type = 8 m_typeFlagBits = 0x0 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 = 90 m_slotCnt = 2 m_freeCnt = 6 m_freeData = 8182 m_reservedCnt = 0 m_lsn = (5081:482:5) m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = 39122617 DB Frag ID = 1
You can see that the m_objId is 99. Since SQL Server 2005, allocation pages are the only ones where the m_objId is the actual object ID. In all other cases, the m_objId and m_indexId are derived from the allocation unit ID that the page is allocated to, and the allocation unit ID reported by DBCC PAGE is calculated as:
(m_objId << 16) | (m_indexId << 48)
(where << means left-shift and | means logical-OR)
You can read more about this in my post Inside the Storage Engine: How are allocation unit IDs calculated?
The only other ‘not real’ object ID you might see is a value of 0 being reported by DBCC CHECKDB, when it can’t figure out which object a page really belongs to – see Disaster recovery 101: Object ID 0, index ID -1, partition ID 0 for more details.