{"id":1072,"date":"2008-03-12T22:38:00","date_gmt":"2008-03-12T22:38:00","guid":{"rendered":"\/blogs\/paul\/post\/Inside-The-Storage-Engine-sp_AllocationMetadata-putting-undocumented-system-catalog-views-to-work.aspx"},"modified":"2017-04-26T10:15:49","modified_gmt":"2017-04-26T17:15:49","slug":"inside-the-storage-engine-sp_allocationmetadata-putting-undocumented-system-catalog-views-to-work","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-sp_allocationmetadata-putting-undocumented-system-catalog-views-to-work\/","title":{"rendered":"Inside The Storage Engine: sp_AllocationMetadata"},"content":{"rendered":"<p>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 <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-iam-pages-iam-chains-and-allocation-units\/\">post<\/a> 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 <em>DBCC PAGE<\/em>. Since I left six months ago, it&#8217;s one of the things I&#8217;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 <em>sys.system_internals_allocation_units<\/em> DMV. The output is easy to match up to <em>sys.partitions<\/em> 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 &#8211; I&#8217;ve put them into the same format that all SQL Server error messages use when giving a page number.\\<\/p>\n<p>So &#8211; I present to you <strong>sp_AllocationMetadata<\/strong>. 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 <em>sys.sp_MS_marksystemobject<\/em> SP. This makes any proc\u00a0execute in the context of the database from where it is called &#8211; extremely useful when you&#8217;re querying against a database&#8217;s system catalog views.<\/p>\n<p>The proc\u00a0can 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&#8217;t specify a parameter, it gives you back the allocation metadata for all objects in the database. Here&#8217;s an example of the output:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nUSE &#x5B;AdventureWorks];\r\nGO\r\n\r\nEXEC &#x5B;sp_AllocationMetadata] N'HumanResources.Employee';\r\nGO\r\n<\/pre>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nObject Name   Index ID   Alloc Unit ID       Alloc Unit Type   First Page   Root Page   First IAM Page\r\n------------  ---------  ------------------  ----------------  -----------  ----------  ---------------\r\nEmployee      1          72057594050379776   IN_ROW_DATA       (1:588)      (1:594)     (1:593)\r\nEmployee      2          72057594055491584   IN_ROW_DATA       (1:2141)     (1:2144)    (1:2142)\r\nEmployee      3          72057594055557120   IN_ROW_DATA       (1:2146)     (1:2149)    (1:2147)\r\nEmployee      4          72057594055622656   IN_ROW_DATA       (1:2150)     (1:2150)    (1:2151)\r\nEmployee      5          72057594055688192   IN_ROW_DATA       (1:2153)     (1:2153)    (1:2154)\r\n<\/pre>\n<p>You&#8217;ll notice there are only <em>IN_ROW_DATA<\/em> allocation units &#8211; that&#8217;s because this table doesn&#8217;t have any LOB data or an variable-length columns that have been pushed off-row (producing <em>LOB_DATA<\/em> and <em>ROW_OVERFLOW_DATA<\/em> allocation units, respectively). So &#8211; it only shows what actually exists (rather than creating NULL values, for instance). Below is the script that creates the proc, and I&#8217;ve included it as an attachment too. Happy spelunking! <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/content\/binary\/sp_allocationmetadata.zip\">sp_AllocationMetadata.zip (1.2 KB)<\/a><\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\n\/*============================================================================\r\n  File:     sp_AllocationMetadata.sql\r\n\r\n  Summary:  This script cracks the system tables to provide top-level\r\n            metadata about a table or index\r\n\r\n  SQL Server Versions: 2005 onwards\r\n------------------------------------------------------------------------------\r\n  Written by Paul S. Randal, SQLskills.com\r\n\r\n  (c) 2014, SQLskills.com. All rights reserved.\r\n\r\n  For more scripts and sample code, check out\r\n    http:\/\/www.SQLskills.com\r\n\r\n  You may alter this code for your own *non-commercial* purposes. You may\r\n  republish altered code as long as you include this copyright and give due\r\n  credit, but you must obtain prior permission before blogging this code.\r\n\r\n  THIS CODE AND INFORMATION ARE PROVIDED &quot;AS IS&quot; WITHOUT WARRANTY OF\r\n  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED\r\n  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND\/OR FITNESS FOR A\r\n  PARTICULAR PURPOSE.\r\n============================================================================*\/\r\n\r\nUSE &#x5B;master];\r\nGO\r\n\r\nIF OBJECT_ID (N'sp_AllocationMetadata') IS NOT NULL\r\n\tDROP PROCEDURE &#x5B;sp_AllocationMetadata];\r\nGO\r\n\r\nCREATE PROCEDURE &#x5B;sp_AllocationMetadata]\r\n(\r\n\t@object SYSNAME = NULL\r\n)\r\nAS\r\nSELECT\r\n\tOBJECT_NAME (&#x5B;sp].&#x5B;object_id]) AS &#x5B;Object Name],\r\n\t&#x5B;sp].&#x5B;index_id] AS &#x5B;Index ID],\r\n\t&#x5B;sp].&#x5B;partition_id] AS &#x5B;Partition ID],\r\n        &#x5B;sa].&#x5B;allocation_unit_id] AS &#x5B;Alloc Unit ID],\r\n\t&#x5B;sa].&#x5B;type_desc] AS &#x5B;Alloc Unit Type],\r\n\t'(' + CONVERT (VARCHAR (6),\r\n\t\tCONVERT (INT,\r\n\t\t\tSUBSTRING (&#x5B;sa].&#x5B;first_page], 6, 1) +\r\n\t\t\tSUBSTRING (&#x5B;sa].&#x5B;first_page], 5, 1))) +\r\n\t':' + CONVERT (VARCHAR (20),\r\n\t\tCONVERT (INT,\r\n\t\t\tSUBSTRING (&#x5B;sa].&#x5B;first_page], 4, 1) +\r\n\t\t\tSUBSTRING (&#x5B;sa].&#x5B;first_page], 3, 1) +\r\n\t\t\tSUBSTRING (&#x5B;sa].&#x5B;first_page], 2, 1) +\r\n\t\t\tSUBSTRING (&#x5B;sa].&#x5B;first_page], 1, 1))) +\r\n\t')' AS &#x5B;First Page],\r\n\t'(' + CONVERT (VARCHAR (6),\r\n\t\tCONVERT (INT,\r\n\t\t\tSUBSTRING (&#x5B;sa].&#x5B;root_page], 6, 1) +\r\n\t\t\tSUBSTRING (&#x5B;sa].&#x5B;root_page], 5, 1))) +\r\n\t':' + CONVERT (VARCHAR (20),\r\n\t\tCONVERT (INT,\r\n\t\t\tSUBSTRING (&#x5B;sa].&#x5B;root_page], 4, 1) +\r\n\t\t\tSUBSTRING (&#x5B;sa].&#x5B;root_page], 3, 1) +\r\n\t\t\tSUBSTRING (&#x5B;sa].&#x5B;root_page], 2, 1) +\r\n\t\t\tSUBSTRING (&#x5B;sa].&#x5B;root_page], 1, 1))) +\r\n\t')' AS &#x5B;Root Page],\r\n\t'(' + CONVERT (VARCHAR (6),\r\n\t\tCONVERT (INT,\r\n\t\t\tSUBSTRING (&#x5B;sa].&#x5B;first_iam_page], 6, 1) +\r\n\t\t\tSUBSTRING (&#x5B;sa].&#x5B;first_iam_page], 5, 1))) +\r\n\t':' + CONVERT (VARCHAR (20),\r\n\t\tCONVERT (INT,\r\n\t\t\tSUBSTRING (&#x5B;sa].&#x5B;first_iam_page], 4, 1) +\r\n\t\t\tSUBSTRING (&#x5B;sa].&#x5B;first_iam_page], 3, 1) +\r\n\t\t\tSUBSTRING (&#x5B;sa].&#x5B;first_iam_page], 2, 1) +\r\n\t\t\tSUBSTRING (&#x5B;sa].&#x5B;first_iam_page], 1, 1))) +\r\n\t')' AS &#x5B;First IAM Page]\r\nFROM\r\n\tsys.system_internals_allocation_units AS &#x5B;sa],\r\n\tsys.partitions AS &#x5B;sp]\r\nWHERE\r\n\t&#x5B;sa].&#x5B;container_id] = &#x5B;sp].&#x5B;partition_id]\r\nAND &#x5B;sp].&#x5B;object_id] =\r\n\t(CASE WHEN (@object IS NULL)\r\n\t\tTHEN &#x5B;sp].&#x5B;object_id]\r\n\t\tELSE OBJECT_ID (@object)\r\n\tEND);\r\nGO\r\n\r\nEXEC sys.sp_MS_marksystemobject &#x5B;sp_AllocationMetadata];\r\nGO\r\n\r\n--USE &#x5B;AdventureWorks];\r\n--GO\r\n--EXEC &#x5B;sp_AllocationMetadata] N'HumanResources.Employee';\r\n--GO\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[38,48,62,100],"tags":[],"class_list":["post-1072","post","type-post","status-publish","format-standard","hentry","category-example-scripts","category-inside-the-storage-engine","category-on-disk-structures","category-undocumented-commands"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Inside The Storage Engine: sp_AllocationMetadata - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-sp_allocationmetadata-putting-undocumented-system-catalog-views-to-work\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Inside The Storage Engine: sp_AllocationMetadata - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"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 [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-sp_allocationmetadata-putting-undocumented-system-catalog-views-to-work\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2008-03-12T22:38:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-26T17:15:49+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-sp_allocationmetadata-putting-undocumented-system-catalog-views-to-work\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-sp_allocationmetadata-putting-undocumented-system-catalog-views-to-work\/\",\"name\":\"Inside The Storage Engine: sp_AllocationMetadata - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2008-03-12T22:38:00+00:00\",\"dateModified\":\"2017-04-26T17:15:49+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-sp_allocationmetadata-putting-undocumented-system-catalog-views-to-work\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-sp_allocationmetadata-putting-undocumented-system-catalog-views-to-work\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-sp_allocationmetadata-putting-undocumented-system-catalog-views-to-work\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Inside The Storage Engine: sp_AllocationMetadata\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Inside The Storage Engine: sp_AllocationMetadata - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-sp_allocationmetadata-putting-undocumented-system-catalog-views-to-work\/","og_locale":"en_US","og_type":"article","og_title":"Inside The Storage Engine: sp_AllocationMetadata - Paul S. Randal","og_description":"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 [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-sp_allocationmetadata-putting-undocumented-system-catalog-views-to-work\/","og_site_name":"Paul S. Randal","article_published_time":"2008-03-12T22:38:00+00:00","article_modified_time":"2017-04-26T17:15:49+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-sp_allocationmetadata-putting-undocumented-system-catalog-views-to-work\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-sp_allocationmetadata-putting-undocumented-system-catalog-views-to-work\/","name":"Inside The Storage Engine: sp_AllocationMetadata - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2008-03-12T22:38:00+00:00","dateModified":"2017-04-26T17:15:49+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-sp_allocationmetadata-putting-undocumented-system-catalog-views-to-work\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-sp_allocationmetadata-putting-undocumented-system-catalog-views-to-work\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-sp_allocationmetadata-putting-undocumented-system-catalog-views-to-work\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Inside The Storage Engine: sp_AllocationMetadata"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1072","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=1072"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1072\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=1072"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=1072"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=1072"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}