Xrhrft

I am reading this article: Inside the Storage Engine: Anatomy of a page.

I have a database MyDB, and there is a table MyTable in the database.

I have the following questions:

1. 
   

   If I do as follows:

   
   
   

   (1) use the following query to find the object ID for a table:

   
   
   

   Use MyDB;
   
   select sys.objects.name, sys.objects.object_id from sys.objects where (name = 'MyTable');
   
   

   
   
   

   (2) then use the following command to find all pages allocated to MyTable:

   
   
   

   dbcc ind(MyDB, 'MyTable', -1);
   
   

   
   
   

   (3) then in the result table, I choose one of the data pages (page type = 1), and use the following command:

   
   
   

   DBCC TRACEON(3604);
   
   DBCC PAGE (MyDB, 1, 17386, 3);
   
   

   
   
   

   Then in the dumped content (page header) of step (3), the m_objId (AllocUnitId.idObj) field should equal to the object ID obtained in step (1). Is that correct?

   
   
   

   And whether that works for both user tables and system base tables, such as sys.syscolpars base table?

   
   
   

   Based on my test, the above two conclusion are both correct.

   
   



2. What is the meaning of Metadata: ObjectId? In article, the ‘metadata: objectId’ <> m_objId. But from my own tests, the ‘metadata: objectId’ always equals to m_objId. Why? The original article does not explain the metadata clearly.

I am using SQL Server 2005, 2008, 2008 R2, 2012 and 2014

First of all, Paul mentions this about those "Metadata" fields in the blog post:

Note that I didn’t include the fields starting with Metadata:. That’s because they’re not part of a page header. During SQL Server 2005 development I did some major work rewriting the guts of DBCC PAGE and to save everyone using it from having to do all the system table look-ups to determine what the actual object and index IDs are, I changed DBCC PAGE to do them internally and output the results.

So the "Metadata: ObjectId" field should always match the actual object_id from sys.objects.

The other part of the question was eventually answered in the linked blog post as follows:

The m_objId is only the actual object ID for system tables with IDs less than 100, and for tables in databases upgraded from SQL Server 2000. For all other tables, the m_objId is part of the allocation unit ID.

So the m_objId will match for system tables (and upgrades from SQL Server 2000). But for others, he says it's "part of the allocation unit ID." That's a bit vague.

Running SQL Server 2017 here, I can create a user table with one index page and get the following for those two values from DBCC PAGE:

m_objId (AllocUnitId.idObj) = 178

Metadata: ObjectId = 901578250

I can confirm that 901578250 matches what's in sys.objects object_id for this table.

So where does 178 come from?

Upon further research, Paul has another post: Inside the Storage Engine: How are allocation unit IDs calculated?

In that, he discusses how the allocation_unit_id (from sys.allocation_units) is calculated from m_objId and another internal id field from the page headers (m_indexId).

So, essentially, after SQL Server 2000, the m_objId value is just a building block used to calculate the ID for the allocation_unit. It's internal to the page and doesn't really map to something else (not in the same way that "Metadata: ObjectId" does anyway).