It's been a long few days of building slide decks and other content and I just had to stop for a bit and take care of my internals-hacking withdrawal symptoms!

While I was at Microsoft, I wrote some code in the Storage Engine to very easily return all the IAM chains/allocation units (see this post for more details of the internals of those), what type they are, and the relevant page IDs (first, root, first-IAM) so I could go spelunking with DBCC PAGE. Since I left six months ago, it's one of the things I've been missing using when poking around on customer sites, so this afternoon I sat down and wrote the equivalent in T-SQL, using the undocumented sys.system_internals_allocation_units DMV. The output is easy to match up to sys.partitions but the page IDs are formatted in byte-reversed hex so a little tweaking was needed to extract the fields and make them human readable – I've put them into the same format that all SQL Server error messages use when giving a page number.

So – I present to you sp_AllocationMetadata. I was having all kinds of trouble using it in other databases (trying to figure out a way to change database contexts in the SP) until I remembered that you can create an SP in master and mark it as a system object using the undocumented sys.sp_MS_marksystemobject SP. This makes any SP execute in the context of the database from where it is called – extremely useful when you're querying against a database's system catalog views.

[Edit: Kalen pointed out that the DMV *is* documented, but just not in the BOL index. Even better - that means the SP below isn't doing anything dodgy :-) - thanks Kalen!]

The SP can be called with an optional object name parameter, in which case it will only give you back the allocation metadata for that object. If you don't specify a parameter, it gives you back the allocation metadata for all objects in the database. Here's an example of the output:

USE AdventureWorks;
GO

EXEC sp_AllocationMetadata 'HumanResources.Employee';
GO

Object Name   Index ID   Alloc Unit ID       Alloc Unit Type   First Page   Root Page   First IAM Page
————- ———- ——————- —————– ———— ———– —————-
Employee      1          72057594050379776   IN_ROW_DATA       (1:588)      (1:594)     (1:593)
Employee      2          72057594055491584   IN_ROW_DATA       (1:2141)     (1:2144)    (1:2142)
Employee      3          72057594055557120   IN_ROW_DATA       (1:2146)     (1:2149)    (1:2147)
Employee      4          72057594055622656   IN_ROW_DATA       (1:2150)     (1:2150)    (1:2151)
Employee      5          72057594055688192   IN_ROW_DATA       (1:2153)     (1:2153)    (1:2154)

You'll notice there are only IN_ROW_DATA allocation units – that's because this table doesn't have any LOB data or an variable-length columns that have been pushed off-row (producing LOB_DATA and ROW_OVERFLOW_DATA allocation units, respectively). So – it only shows what actually exists (rather than creating NULL values, for instance).

Below is the script that creates the SP, and I've included it as an attachment too.

Ah – that feel's better :-) Happy spelunking!

USE master;
GO

IF OBJECT_ID ('sp_AllocationMetadata') IS NOT NULL
   DROP PROCEDURE sp_AllocationMetadata;
GO

CREATE PROCEDURE sp_AllocationMetadata
(
   
@object VARCHAR (128) = NULL
)
AS
SELECT
   
OBJECT_NAME (sp.object_id) AS [Object Name],
   
sp.index_id AS [Index ID],
   
sa.allocation_unit_id AS [Alloc Unit ID],
   
sa.type_desc AS [Alloc Unit Type],
   
'(' CONVERT (VARCHAR (6),
      
CONVERT (INT, SUBSTRING (sa.first_page, 6, 1) +
      
   SUBSTRING (sa.first_page, 5, 1))) +
   
':' + CONVERT (VARCHAR (20),
      
CONVERT (INT, SUBSTRING (sa.first_page, 4, 1) +
      
   SUBSTRING (sa.first_page, 3, 1) +
         
SUBSTRING (sa.first_page, 2, 1) +
         
SUBSTRING (sa.first_page, 1, 1))) +
   
')' AS [First Page],
   '(' + CONVERT (VARCHAR (6),
      
CONVERT (INT,
         
SUBSTRING (sa.root_page, 6, 1) +
         
SUBSTRING (sa.root_page, 5, 1))) +
   
':' + CONVERT (VARCHAR (20),
      
CONVERT (INT,
         
SUBSTRING (sa.root_page, 4, 1) +
         
SUBSTRING (sa.root_page, 3, 1) +
         
SUBSTRING (sa.root_page, 2, 1) +
         
SUBSTRING (sa.root_page, 1, 1))) +
   
')' AS [Root Page],
   
'(' + CONVERT (VARCHAR (6),
      
CONVERT (INT,
         
SUBSTRING (sa.first_iam_page, 6, 1) +
         
SUBSTRING (sa.first_iam_page, 5, 1))) +
   
':' + CONVERT (VARCHAR (20),
      
CONVERT (INT,
         
SUBSTRING (sa.first_iam_page, 4, 1) +
         
SUBSTRING (sa.first_iam_page, 3, 1) +
         
SUBSTRING (sa.first_iam_page, 2, 1) +
         
SUBSTRING (sa.first_iam_page, 1, 1))) +
   
')' AS [First IAM Page]
FROM
   
sys.system_internals_allocation_units AS sa,
   
sys.partitions AS sp
WHERE
   
sa.container_id = sp.partition_id
   AND sp.object_id =
      
(CASE WHEN (@object IS NULL)
         THEN sp.object_id
         
ELSE OBJECT_ID (@object)
      
END);
GO

EXEC sys.sp_MS_marksystemobject sp_AllocationMetadata;
GO

sp_AllocationMetadata.zip (.69 KB)