Inside The Storage Engine: sp_AllocationMetadata

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 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 proc) until I remembered that you can create a proc in master and mark it as a system object using the undocumented sys.sp_MS_marksystemobject SP. This makes any proc 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.

The proc 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] N'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 proc, and I’ve included it as an attachment too. Happy spelunking! sp_AllocationMetadata.zip (1.2 KB)

/*============================================================================
  File:     sp_AllocationMetadata.sql

  Summary:  This script cracks the system tables to provide top-level
            metadata about a table or index

  SQL Server Versions: 2005 onwards
------------------------------------------------------------------------------
  Written by Paul S. Randal, SQLskills.com

  (c) 2014, SQLskills.com. All rights reserved.

  For more scripts and sample code, check out
    http://www.SQLskills.com

  You may alter this code for your own *non-commercial* purposes. You may
  republish altered code as long as you include this copyright and give due
  credit, but you must obtain prior permission before blogging this code.

  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

USE [master];
GO

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

CREATE PROCEDURE [sp_AllocationMetadata]
(
	@object SYSNAME = NULL
)
AS
SELECT
	OBJECT_NAME ([sp].[object_id]) AS [Object Name],
	[sp].[index_id] AS [Index ID],
	[sp].[partition_id] AS [Partition 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

--USE [AdventureWorks];
--GO
--EXEC [sp_AllocationMetadata] N'HumanResources.Employee';
--GO