How To Query Allocation Units

Following my post explaining Allocation Units see here I though that I would do a quick follow-up post, demonstrating how you can interrogate these structures. There are two ways of doing this.

The first (and documented way) is to use the catalogue view called sys.allocation units. This view will display one row per allocation unit, for every table and index. This can be joined back to sys.tables, via sys.partition, in order to retrieve the table name, as demonstrated below...

SELECT t.name, au.* FROM sys.allocation_units au
INNER JOIN sys.partitions p
     ON au.container_id = p.partition_id
     INNER JOIN sys.tables t
          ON p.object_id = t.object_id
ORDER BY t.name

...A portion of the output (run against AdaventureWorks2008) is detailed below...

 ...This query is interesting, and shows you how large each of the allocation units is, in terms of pages. What is even more interesting, however, is the undocumented way to interrogate allocation units. This is with the use of sys.system_internals_allocation_units. Let re-run the last query, using this catalogue view...

SELECT t.name, au.* FROM sys.system_internals_allocation_units au
INNER JOIN sys.partitions p
     ON au.container_id = p.partition_id
     INNER JOIN sys.tables t
          ON p.object_id = t.object_id
ORDER BY t.name

...Lets have a look at the results of this query...

...The results of this query are even more interesting. Here, alongside the page counts, we can also see the location of the first IAM Page in the IAM chain, the indexe's root page, and the first page of the index. We can crack these results by using the sys.fn_physloc_formatter() function, as below...

SELECT
     t.name,
     sys.fn_PhysLocFormatter(au.first_page) AS 'First Page',
     sys.fn_PhysLocFormatter(au.root_page) AS 'Root Page',
     sys.fn_PhysLocFormatter(au.first_iam_page) AS 'First IAM Page'
FROM sys.system_internals_allocation_units au
INNER JOIN sys.partitions p
     ON au.container_id = p.partition_id
     INNER JOIN sys.tables t
          ON p.object_id = t.object_id
ORDER BY t.name

...The results are shown below...

...Pretty cool hey? Be warned, however, that the sys.system_internals_allocation_units catalogue view is both undocumented and unsupported! This means that I can not vouch that the page locations it gives you will always be accurate. It is, however, a useful and interesting starting point.

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom