Sql-server – Allocation Unit ID calculation is incorrect

database-internalssql server

I am reading the article http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-how-are-allocation-unit-ids-calculated/ on how to calculate the allocation unit ID based on m_indexId and m_objID. Then I try that on one of my database, by executing the command below:

    DBCC TRACEON(3604);
    DBCC PAGE (UFDATA_008_2013, 1, 73057, 3);
    GO

And the output result is:

    PAGE HEADER:


    Page @0x10008000

    m_pageId = (1:73057)                 m_headerVersion = 1                  m_type = 1
    m_typeFlagBits = 0x0                 m_level = 0                          m_flagBits = 0x8000
    m_objId (AllocUnitId.idObj) = 1913890731                                  m_indexId (AllocUnitId.idInd) = 0
    Metadata: AllocUnitId = 406903719657472                                   Metadata: PartitionId = 406903719657472
    Metadata: IndexId = 1                Metadata: ObjectId = 1913890731      m_prevPage = (1:92774)
    m_nextPage = (0:0)                   pminlen = 11                         m_slotCnt = 61
    m_freeCnt = 3241                     m_freeData = 4829                    m_reservedCnt = 0
    m_lsn = (4028:6215:30)               m_xactReserved = 0                   m_xdesId = (0:0)
    m_ghostRecCnt = 0                    m_tornBits = 0

Then I use the formula in the above article to calculate the allocation Unit ID as (0 << 48) + (1913890731 << 16) = 125428742946816, which does not equal to the output Metadata: AllocUnitId(406903719657472). Why?

I can confirm the page is allocated, since I use the following command to list all the pages allocated to table ‘AADomDataColSet’:

       DBCC TRACEON(3604);
       Dbcc ind(UFDATA_008_2013, ‘AADomDataColSet’, -1);

And the result is below:

Output result for AADomDataColSet

You can find the page (1, 73057) is allocated to this object.

Thanks

Best Answer

This seems identical to a case discussed in the comments to Paul's post. He indicated the cause is that the page is no longer allocated (notice metadata IndexId = 1, but m_indexid = 0).

Decoding the allocation unit ID using Paul's script:

DECLARE @alloc BIGINT = 406903719657472;
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];

Result:

Result screenshot

This matches the m_objid in your DBCC PAGE output.

Updated question

The calculation is correct if an index ID of 1 is used, again using the formula in Paul's post:

SELECT allocation_id =
    1 * CONVERT (BIGINT, POWER (2.0, 48)) | -- index id
    1913890731 * CONVERT (BIGINT, POWER (2.0, 16)); -- object id

Allocation id result

So your actual question seems not to be about the allocation unit calculation at all; rather you are asking why DBCC PAGE shows m_indexId (AllocUnitId.idInd) = 0 when the page is allocated and associated with index 1 - as shown in the DBCC IND output.

That, I don't know offhand. What I do know is that the calculation works both ways for me, testing on SQL Server 2012 build 6518. Perhaps there is something about your SQL Server version or table that means AllocUnitId.idInd is not being reported correctly. Without specific, reproducible details, you might have to ask Paul himself (though he's currently on holiday).

It's probably some edge case. For example, this post says that there may be differences in m_indexId if the database was upgraded from SQL Server 2000. Try rebuilding the table, that oftens sorts these sorts of things out.

Related Question