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.
10 thoughts on “Inside the Storage Engine: How are allocation unit IDs calculated?”
Is there a left shift bitwise operator in SQL Server?
Thanks for the post,
I updated the post to include the T-SQL using the POWER function – cheers
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?
1 / POWER (2.0, 16)
POWER (2.0, -16)
which is a little simpler.
Another wonderful tidbit for my toolbox. Thanks so much!
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 :)
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.
For that AllocUnitId, the m_indexId should be 1. Are you sure the page is allocated?
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.
The post shows you exactly how to do what you’re asking…