{"id":2287,"date":"2014-03-26T11:00:37","date_gmt":"2014-03-26T15:00:37","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2287"},"modified":"2014-11-13T13:40:58","modified_gmt":"2014-11-13T18:40:58","slug":"allocation-unit","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2287\/allocation-unit\/","title":{"rendered":"The Allocation Unit"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nSQL Server organizes all the data in pages. That includes the actual table data, index data, large object data and row overflow data. The pages that make up a table are however not all assigned to the table itself. Instead, they are grouped in logical units called allocation units.\n<\/p>\n

The Allocation Unit<\/h3>\n

\nA table can have many indexes. Each index or heap, including the base table can in turn have many partitions. In fact, in SQL Server every table or index is partitioned. However, if you do not specify an explicit partition scheme, all the data of the index or heap goes into a single partition.\n<\/p>\n

\nEach partition stores the table or index rows that belong to that partition. All the pages required to store that information are grouped into an allocation unit. If the partition also contains row overflow data, another allocation unit is created to contain all pages with row overflow data. If large binary objects are present, all the pages for that type of data make up yet another allocation unit. That means, in SQL Server versions up to 2012, a single partition of an index or heap can contain up to three separate allocation units.\n<\/p>\n

sys.allocation_units<\/h3>\n

\nAll allocation units of the current database can be found in the catalog view sys.allocation_units<\/span>. Because the allocation units are organized under the partitions, sys.allocation_units<\/span> does not contain an object_id<\/span> or index_id<\/span> column but rather a partition_id<\/span>. So, to get from an allocation unit in sys.allocation_units<\/span> to the table it belongs to, we need to first join to sys.partitions<\/span>. That catalog view contains the object_id<\/span> as well as the index_id<\/span> that can be used to identify the index and table that the allocation unit belongs to.\n<\/p>\n

\n[sql]\nSELECT OBJECT_NAME(P.object_id) AS object_name,
\n I.name AS index_name,
\n i.type_desc AS index_type,
\n P.partition_number,
\n AU.type_desc AS allocation_unit_type
\nFROM sys.allocation_units AS AU
\nJOIN sys.partitions AS P
\n ON P.partition_id = AU.container_id
\nJOIN sys.indexes AS I
\n ON P.object_id = I.object_id
\n AND P.index_id = I.index_id;
\n[\/sql]\n<\/div>\n

\nThis query returns information about all allocation units in the current database, including those of system tables. You will get an output that looks like this:\n<\/p>\n

\n\"sys.allocation_units\"<\/a>\n<\/p>\n

\nThe image shows a system table that has all three types of allocation unit present for the clustered index.\n<\/p>\n

Summary<\/h3>\n

\nSQL Server groups all pages that belong to a single partition of a single index or heap into logical units. These units are called allocation units. You can see all allocation units of the current database using the sys.allocation_units<\/span> catalog view.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

SQL Server groups all pages that belong to a single partition of a single index or heap into logical units. These units are called allocation units. Read on to learn about the different types of allocation units and discover how to identify the allocation units belonging to a table.<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[118,5,27,14,105],"tags":[116,120,122,106,15,108,109],"class_list":["post-2287","post","type-post","status-publish","format-standard","hentry","category-dmvs-cvs","category-general","category-series","category-sql-server-internals","category-storage-wednesday","tag-allocation-unit","tag-catalog-view","tag-cv","tag-internals","tag-sql-server","tag-storage","tag-storage-engine"],"acf":[],"yoast_head":"\nThe Allocation Unit - sqlity.net<\/title>\n<meta name=\"description\" content=\"SQL Server organizes the pages belonging to a single partition of any index in logical units called allocation units. Read on to get all the details.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sqlity.net\/en\/2287\/allocation-unit\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The Allocation Unit - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"SQL Server organizes the pages belonging to a single partition of any index in logical units called allocation units. Read on to get all the details.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2287\/allocation-unit\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2014-03-26T15:00:37+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:40:58+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/sys.allocation_units.jpg\" \/>\n<meta name=\"author\" content=\"Sebastian Meine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sebastian Meine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2287\\\/allocation-unit\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2287\\\/allocation-unit\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"The Allocation Unit\",\"datePublished\":\"2014-03-26T15:00:37+00:00\",\"dateModified\":\"2014-11-13T18:40:58+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2287\\\/allocation-unit\\\/\"},\"wordCount\":481,\"commentCount\":4,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2287\\\/allocation-unit\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/03\\\/sys.allocation_units.jpg\",\"keywords\":[\"Allocation Unit\",\"Catalog View\",\"CV\",\"internals\",\"SQL Server\",\"storage\",\"storage engine\"],\"articleSection\":[\"DMVs & CVs\",\"General\",\"Series\",\"SQL Server Internals\",\"Storage Wednesday\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2287\\\/allocation-unit\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2287\\\/allocation-unit\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2287\\\/allocation-unit\\\/\",\"name\":\"The Allocation Unit - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2287\\\/allocation-unit\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2287\\\/allocation-unit\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/03\\\/sys.allocation_units.jpg\",\"datePublished\":\"2014-03-26T15:00:37+00:00\",\"dateModified\":\"2014-11-13T18:40:58+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"SQL Server organizes the pages belonging to a single partition of any index in logical units called allocation units. Read on to get all the details.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2287\\\/allocation-unit\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2287\\\/allocation-unit\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2287\\\/allocation-unit\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/03\\\/sys.allocation_units.jpg\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/03\\\/sys.allocation_units.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2287\\\/allocation-unit\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"The Allocation Unit\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\",\"name\":\"Sebastian Meine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"caption\":\"Sebastian Meine\"},\"sameAs\":[\"http:\\\/\\\/sqlity.net\",\"https:\\\/\\\/x.com\\\/sqlity\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"The Allocation Unit - sqlity.net","description":"SQL Server organizes the pages belonging to a single partition of any index in logical units called allocation units. Read on to get all the details.","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:\/\/sqlity.net\/en\/2287\/allocation-unit\/","og_locale":"en_US","og_type":"article","og_title":"The Allocation Unit - sqlity.net","og_description":"SQL Server organizes the pages belonging to a single partition of any index in logical units called allocation units. Read on to get all the details.","og_url":"https:\/\/sqlity.net\/en\/2287\/allocation-unit\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-03-26T15:00:37+00:00","article_modified_time":"2014-11-13T18:40:58+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/sys.allocation_units.jpg","type":"","width":"","height":""}],"author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/2287\/allocation-unit\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2287\/allocation-unit\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"The Allocation Unit","datePublished":"2014-03-26T15:00:37+00:00","dateModified":"2014-11-13T18:40:58+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2287\/allocation-unit\/"},"wordCount":481,"commentCount":4,"image":{"@id":"https:\/\/sqlity.net\/en\/2287\/allocation-unit\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/sys.allocation_units.jpg","keywords":["Allocation Unit","Catalog View","CV","internals","SQL Server","storage","storage engine"],"articleSection":["DMVs & CVs","General","Series","SQL Server Internals","Storage Wednesday"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2287\/allocation-unit\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2287\/allocation-unit\/","url":"https:\/\/sqlity.net\/en\/2287\/allocation-unit\/","name":"The Allocation Unit - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2287\/allocation-unit\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2287\/allocation-unit\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/sys.allocation_units.jpg","datePublished":"2014-03-26T15:00:37+00:00","dateModified":"2014-11-13T18:40:58+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"SQL Server organizes the pages belonging to a single partition of any index in logical units called allocation units. Read on to get all the details.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2287\/allocation-unit\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2287\/allocation-unit\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2287\/allocation-unit\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/sys.allocation_units.jpg","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/03\/sys.allocation_units.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2287\/allocation-unit\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"The Allocation Unit"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c","name":"Sebastian Meine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","caption":"Sebastian Meine"},"sameAs":["http:\/\/sqlity.net","https:\/\/x.com\/sqlity"]}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-AT","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2287","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=2287"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2287\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2287"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2287"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2287"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}