Inside the Storage Engine: How are allocation unit IDs calculated?

It’s been a long time since I’ve written a post about pure internals, but every so often I get asked how an allocation unit ID is calculated from the m_objId and m_indexId fields that are stored in the header of every page.

When DBCC PAGE dumps a page header’s contents, it does the necessary calculations and metadata look-ups to be able to tell you the allocation unit ID, partition ID, relational object ID, and relational index ID. Basically everything prefixed with ‘Metadata:’ in the DBCC PAGE output below is NOT stored on the page itself:

Page @0x00000004ED8A2000

m_pageId = (1:445)                  m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0xa000
m_objId (AllocUnitId.idObj) = 97    m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594044284928
Metadata: PartitionId = 72057594039304192                                Metadata: IndexId = 0
Metadata: ObjectId = 599673184      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 8                         m_slotCnt = 1                       m_freeCnt = 8069
m_freeData = 121                    m_reservedCnt = 0                   m_lsn = (225:443:22)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 0                      DB Frag ID = 1

The formula is as follows:

  • Take the m_indexId and left-shift by 48, giving value A
  • Take the m_objId and left-shift by 16, giving value B
  • AllocUnitId = A | B (where | is a logical OR operation)

Using the page above:

  • A = 256 << 48 = 72057594037927936
  • B = 97 << 16 = 6356992
  • AllocUnitId = 72057594044284928

You can do this using SQL Server using the POWER function as a left shift of X bits is the same as multiplying by 2-to-the-power-X:

SELECT 256 * CONVERT (BIGINT, POWER (2.0, 48)) | 97 * CONVERT (BIGINT, POWER (2.0, 16));
GO

And then you can perform the various look-ups using sys.system_internals_allocation_units and sys.partitions like so:

SELECT
	[a].[container_id] AS [Partition ID],
	[p].[object_id] AS [Object ID],
	[p].[index_id] AS [Index ID]
FROM sys.system_internals_allocation_units [a]
JOIN sys.partitions [p]
	ON [p].[partition_id] = [a].[container_id]
WHERE
	[a].[allocation_unit_id] = 72057594044284928;
GO
Partition ID         Object ID   Index ID
-------------------- ----------- -----------
72057594039304192    599673184   0

And you can see that the values match the DBCC PAGE output.

To convert from an allocation unit ID to what you should see in the DBCC PAGE output:

  • m_indexId = AllocUnitId >> 48
  • m_objId = (AllocUnitId – (m_indexId << 48)) >> 16

The T-SQL for this involves floating point math as we need to use the reciprocal of POWER:

DECLARE @alloc BIGINT = 72057594044284928;
DECLARE @index BIGINT;

SELECT @index =
	CONVERT (BIGINT,
		CONVERT (FLOAT, @alloc)
			* (1 / POWER (2.0, 48)) -- right shift, reciprocal of left shift
	);
SELECT
	CONVERT (BIGINT,
		CONVERT (FLOAT, @alloc - (@index * CONVERT (BIGINT, POWER (2.0, 48))))
			* (1 / POWER (2.0, 16)) -- right shift, reciprocal of left shift
	) AS [m_objId],
	@index AS [m_indexId];
GO

 

m_objId              m_indexId
-------------------- --------------------
97                   256

An example of when you might use this information/code is during programmatic analysis of a corrupt database that DBCC CHECKDB cannot process to allow you to extract data as a last resort.

Enjoy!

10 thoughts on “Inside the Storage Engine: How are allocation unit IDs calculated?

  1. Paul,

    I got this to work.

    SELECT CAST(256 AS BIGINT) * POWER(CAST(2 AS BIGINT), CAST(48 AS BIGINT))

    Is there a less verbose way to do it in T-SQL?

    Thanks,
    Frank

  2. Once again, can’t thank you enough for spilling the details on this one. Using this I’ve now finally got my parsing & recovery functionality working perfectly, with less magic than before :)

    – Mark

  3. I am trying to apply this formula to get the AllocationUnitId for a datapage, from the Page Dump; m_objId (AllocUnitId.idObj) = 75576499, m_indexId (AllocUnitId.idInd) = 0, Metadata: AllocUnitId = 286427958149120. When I apply the above formula to get the AllocationUnitId, I got 4952981438464.
    SELECT 0 * CONVERT (BIGINT, POWER (2.0, 48)) | 75576499 * CONVERT (BIGINT, POWER (2.0, 16));
    The return value is not matching. Am I doing anything wrong here? Why m_indexid = 0 here. This table has 1 Clustered and 4 NonClustered Indexes.

  4. In your example, the object id is getting from Page is 97 but in system table is showing different id is 599673184. Is there any way to calculate object id which found in system table.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Some thoughts on courage

(This is also the Ponderings – editorial – in today’s SQLskills newsletter.) I want to start out this post by sincerely thanking everyone who emailed

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.