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!