# 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: 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. Frank Gill says:

Paul,

Is there a left shift bitwise operator in SQL Server?

Thanks for the post,
Frank

1. Paul Randal says:

I updated the post to include the T-SQL using the POWER function – cheers

2. Frank Gill says:

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

3. tobi says:

Note, that

1 / POWER (2.0, 16)

equals

POWER (2.0, -16)

which is a little simpler.

4. Bob Pusateri says:

Another wonderful tidbit for my toolbox. Thanks so much!

Bob

5. Mark S. Rasmussen says:

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

6. Debdutta says:

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.

1. Paul Randal says:

For that AllocUnitId, the m_indexId should be 1. Are you sure the page is allocated?

7. Chetan Pacharne says:

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.

1. Paul Randal says:

The post shows you exactly how to do what you’re asking…

## Other articles

### Back-to-school health check discount through September

It’s getting towards the end of summer so it’s a perfect time to ensure your SQL Server instances are configured correctly and performing well. For

Explore

### Small bug with sample_ms in sys.dm_io_virtual_file_stats

A prior student emailed me yesterday about some strange behavior of the sample_ms column in sys.dm_io_virtual_file_stats. It’s supposed to be the number of milliseconds since the SQL

Explore

### The Curious Case of… the un-killable thread

(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

Explore

### The Curious Case of… object ID 99

(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

Explore

### The Curious Case of… log generated during a DROP TABLE

(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

Explore

### Jonathan’s ten year anniversary!

Ten years ago today we announced that we were hiring our first employee: Jonathan Kehayias. It was a big risk for Kimberly and I, as

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.
{"ft_lang_label":"__label__en","ft_lang_prob":0.66711795,"math_prob":0.7140134,"size":4725,"snap":"2021-43-2021-49","text_gpt3_token_len":1339,"char_repetition_ratio":0.12518534,"word_repetition_ratio":0.024358975,"special_character_ratio":0.33925927,"punctuation_ratio":0.1367816,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.9614731,"pos_list":[0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20],"im_url_duplicate_count":[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2021-10-28T17:00:36Z\",\"WARC-Record-ID\":\"<urn:uuid:265f9fb7-d4f8-47ac-9e14-652dd5e5e2fe>\",\"Content-Length\":\"94444\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:f4bd8a1d-9f17-4f22-87c9-998418101adb>\",\"WARC-Concurrent-To\":\"<urn:uuid:d96ee4c7-d771-47ba-85a1-67bf514f0006>\",\"WARC-IP-Address\":\"3.209.169.194\",\"WARC-Target-URI\":\"https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-how-are-allocation-unit-ids-calculated/\",\"WARC-Payload-Digest\":\"sha1:VPGZPU4YMD6BSMCQNF2XOO6FU3YC3CFZ\",\"WARC-Block-Digest\":\"sha1:D7A7KIQDTYQ3Z2KZXXVOO3MIKT6O3BXN\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2021/CC-MAIN-2021-43/CC-MAIN-2021-43_segments_1634323588398.42_warc_CC-MAIN-20211028162638-20211028192638-00280.warc.gz\"}"}