{"id":1167,"date":"2007-10-04T00:42:00","date_gmt":"2007-10-04T00:42:00","guid":{"rendered":"\/blogs\/paul\/post\/Inside-the-Storage-Engine-IAM-pages-IAM-chains-and-allocation-units.aspx"},"modified":"2013-11-05T13:49:40","modified_gmt":"2013-11-05T21:49:40","slug":"inside-the-storage-engine-iam-pages-iam-chains-and-allocation-units","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-iam-pages-iam-chains-and-allocation-units\/","title":{"rendered":"Inside the Storage Engine: IAM pages, IAM chains, and allocation units"},"content":{"rendered":"<p>This is a combo from some previously posted material, with some more <span style=\"font-family: 'Courier New';\">DBCC PAGE<\/span> output thrown in.<\/p>\n<p><strong>IAM pages<\/strong><\/p>\n<p>An IAM (<span style=\"text-decoration: underline;\">I<\/span>ndex <span style=\"text-decoration: underline;\">A<\/span>llocation <span style=\"text-decoration: underline;\">M<\/span>ap) page tracks approximately 4GB worth of space in a single file, aligned on a 4GB boundary. These 4GB chunks are called &#8216;GAM intervals&#8217;. An IAM page tracks which extents within that specific GAM interval belongs to a single entity (I&#8217;m chosing my words carefully here and not using any word that has SQL Server connotations like &#8216;object&#8217;).<\/p>\n<p>An IAM page can only track the space for a single GAM interval in a single file so\u00a0If the database has multiple files, or some files are more then 4GB, and the entity has space allocated from multiple files or multiple GAM intervals within a file, then you can see how multiple IAM pages are needed for each entity to track all the space that its using. If an entity requires multiple IAM pages to track all its extents, then they IAM page have to be linked together. That&#8217;s where an IAM chain comes in. More on these below.<\/p>\n<p>Each IAM page has two records, an IAM page header and a bitmap. Let&#8217;s look at one with DBCC PAGE. I&#8217;m using the database from the <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-using-dbcc-page-and-dbcc-ind-to-find-out-if-page-splits-ever-roll-back\/\"><strong><span style=\"color: #696969;\">page split post<\/span><\/strong><\/a>. Doing a <span style=\"font-family: 'Courier New';\">DBCC IND<\/span> on the table we created gives us:<\/p>\n<p><img fetchpriority=\"high\" decoding=\"async\" alt=\"SEQA3.jpg\" src=\"https:\/\/www.sqlskills.com\/blogs\/paul\/content\/binary\/seqa3.jpg\" width=\"1093\" height=\"59\" border=\"0\" \/><\/p>\n<p>By looking at the <span style=\"font-family: 'Courier New';\">PageType<\/span> column, we can see that there&#8217;s an IAM page (a page with type 10 &#8211; see the post on <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-anatomy-of-a-page\/\">Anatomy of a page<\/a> for more details) with page ID <span style=\"font-family: 'Courier New';\">(1:152)<\/span>:<\/p>\n<blockquote dir=\"ltr\" style=\"margin-right: 0px;\"><p><span style=\"font-family: 'Courier New';\">DBCC<\/span><span style=\"font-family: 'Courier New';\"><span style=\"color: #000000;\"> TRACEON <\/span><span style=\"color: #808080;\">(<\/span><span style=\"color: #000000;\">3604<\/span><span style=\"color: #808080;\">);<\/span><\/span><\/p>\n<p>GO<\/p>\n<p><span style=\"font-family: 'Courier New';\">DBCC<\/span><span style=\"font-family: 'Courier New';\"><span style=\"color: #000000;\"> PAGE <\/span><span style=\"color: #808080;\">(<\/span><span style=\"color: #ff0000;\">&#8216;pagesplittest&#8217;<\/span><span style=\"color: #808080;\">,<\/span><span style=\"color: #000000;\"> 1<\/span><span style=\"color: #808080;\">,<\/span><span style=\"color: #000000;\"> 152<\/span><span style=\"color: #808080;\">,<\/span><span style=\"color: #000000;\"> 3<\/span><span style=\"color: #808080;\">);<\/span><\/span><\/p>\n<p>GO<\/p>\n<p>m_pageId = (1:152)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 m_headerVersion = 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 m_type = 10<br \/>\nm_typeFlagBits = 0x0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 m_level = 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 m_flagBits = 0x200<br \/>\nm_objId (AllocUnitId.idObj) = 68\u00a0\u00a0\u00a0\u00a0 m_indexId (AllocUnitId.idInd) = 256<br \/>\nMetadata: AllocUnitId = 72057594042384384<br \/>\nMetadata: PartitionId = 72057594038386688\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Metadata: IndexId = 1<br \/>\nMetadata: ObjectId = 2073058421\u00a0\u00a0\u00a0\u00a0\u00a0 m_prevPage = (0:0)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 m_nextPage = (0:0)<br \/>\npminlen = 90\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 m_slotCnt = 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 m_freeCnt = 6<br \/>\nm_freeData = 8182\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 m_reservedCnt = 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 m_lsn = (18:116:13)<br \/>\nm_xactReserved = 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 m_xdesId = (0:0)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 m_ghostRecCnt = 0<br \/>\nm_tornBits = -1947725876<\/p>\n<p>Allocation Status<\/p>\n<p>GAM (1:2) = ALLOCATED\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SGAM (1:3) = ALLOCATED<br \/>\nPFS (1:1) = 0x70 IAM_PG MIXED_EXT ALLOCATED\u00a0\u00a0 0_PCT_FULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DIFF (1:6) = CHANGED<br \/>\nML (1:7) = NOT MIN_LOGGED<\/p>\n<p>IAM: Header @0x620CC064 Slot 0, Offset 96<\/p>\n<p>sequenceNumber = 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 status = 0x0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 objectId = 0<br \/>\nindexId = 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 page_count = 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 start_pg = (1:0)<br \/>\nIAM: Single Page Allocations @0x620CC08E<\/p>\n<p>Slot 0 = (1:143)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Slot 1 = (1:153)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Slot 2 = (1:154)<br \/>\nSlot 3 = (0:0)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Slot 4 = (0:0)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Slot 5 = (0:0)<br \/>\nSlot 6 = (0:0)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Slot 7 = (0:0)<br \/>\nIAM: Extent Alloc Status Slot 1 @0x620CC0C2<\/p>\n<p>(1:0)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8211; (1:272)\u00a0\u00a0\u00a0\u00a0\u00a0 = NOT ALLOCATED<\/p><\/blockquote>\n<p>Some things to note about the page header itself:<\/p>\n<ul>\n<li>The page has type 10, as we&#8217;d expect<\/li>\n<li>The previous and next page pointers are NULL, because there aren&#8217;t any other IAM pages in this IAM chain<\/li>\n<li>The slot count is 2 &#8211; one for the IAM header record and one for the bitmap itself<\/li>\n<li>The page is almost entirely full<\/li>\n<\/ul>\n<p>The IAM page header has the following fields:<\/p>\n<ul>\n<li><strong><span style=\"font-family: 'Courier New';\">sequenceNumber<\/span><\/strong>\n<ul>\n<li>This is the position of the IAM page in the IAM chain. This increases by one for each page added to the IAM chain.<\/li>\n<\/ul>\n<\/li>\n<li><strong><span style=\"font-family: 'Courier New';\">status<\/span><\/strong>\n<ul>\n<li>This is unused.<\/li>\n<\/ul>\n<\/li>\n<li><strong><span style=\"font-family: 'Courier New';\">objectId<\/span><\/strong><\/li>\n<li><strong><span style=\"font-family: 'Courier New';\">indexId<\/span><\/strong>\n<ul>\n<li>On SQL Server 2000 and before, these are the object\u00a0 and index IDS that the IAM page is part of. On SQL Server 2005 and later they are unused.<\/li>\n<\/ul>\n<\/li>\n<li><strong><span style=\"font-family: 'Courier New';\">page_count<\/span><\/strong>\n<ul>\n<li>This is unused\u00a0 &#8211; it used to be the number of\u00a0page IDs\u00a0that are being tracked in the single page allocation array.<\/li>\n<\/ul>\n<\/li>\n<li><strong><span style=\"font-family: 'Courier New';\">start_pg<\/span><\/strong>\n<ul>\n<li>This is the GAM interval that the page maps. It stores the first page ID\u00a0in the mapped interval.<\/li>\n<\/ul>\n<\/li>\n<li><strong><span style=\"font-family: 'Courier New';\">Single Page Allocations array<\/span><\/strong>\n<ul>\n<li>These are the pages that have been allocated from <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-anatomy-of-an-extent\/\">mixed extents<\/a>. This array is only used in the first IAM page in the chain (as the whole IAM chain only need to track at most 8 single-page allocations).<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>The bitmap occupies the rest of the IAM page and has a bit for each extent in the GAM interval. The bit is set if the extent is allocated to the entity, and clear if it is not. Obviously two IAM pages that map the same GAM interval for different entities cannot both have the same bit set &#8211; this is checked by <span style=\"font-family: 'Courier New';\">DBCC CHECKDB<\/span>. In the output from <span style=\"font-family: 'Courier New';\">DBCC PAGE<\/span> above, you can see that there are no extents allocated to the table. You&#8217;ll notice that the output only goes up to the extent starting at\u00a0page 272 in the file &#8211; this is because the data file is only that big. I inserted a bunch more rows into the table and did another <span style=\"font-family: 'Courier New';\">DBCC PAGE<\/span> of the IAM page. This time the DBCC PAGE output contains:<\/p>\n<blockquote dir=\"ltr\" style=\"margin-right: 0px;\"><p><span style=\"font-family: 'Courier New';\">IAM: Single Page Allocations @0x620CC08E<\/span><\/p>\n<p><span style=\"font-family: 'Courier New';\">Slot 0 = (1:143)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Slot 1 = (1:153)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Slot 2 = (1:154)<br \/>\nSlot 3 = (1:155)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Slot 4 = (1:156)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Slot 5 = (1:157)<br \/>\nSlot 6 = (1:158)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Slot 7 = (1:159)<\/span><br \/>\n<span style=\"font-family: 'Courier New';\">IAM: Extent Alloc Status Slot 1 @0x620CC0C2<\/span><\/p>\n<p><span style=\"font-family: 'Courier New';\">(1:0)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &#8211; (1:152)\u00a0\u00a0\u00a0\u00a0\u00a0 = NOT ALLOCATED<br \/>\n(1:160)\u00a0\u00a0\u00a0\u00a0\u00a0 &#8211; (1:296)\u00a0\u00a0\u00a0\u00a0\u00a0 =\u00a0\u00a0\u00a0\u00a0 ALLOCATED<br \/>\n(1:304)\u00a0\u00a0\u00a0\u00a0\u00a0 &#8211; (1:400)\u00a0\u00a0\u00a0\u00a0\u00a0 = NOT ALLOCATED<\/span><\/p><\/blockquote>\n<p dir=\"ltr\">You can see that the entire single-page allocation array is full and then allocations switched to dedicated extents. The first available extent must have been the one starting at page 160 and all extents up to an including the one starting at page 296 are now allocated. Note also that the file must have grown because the output now goes up to page 400 in the file.<\/p>\n<p dir=\"ltr\">A couple more things to note about IAM pages:<\/p>\n<ul>\n<li>\n<div>There are themselves single-page allocations from mixed extents and are not tracked anywhere<\/div>\n<\/li>\n<li>\n<div>They can be allocated from any file to track extents in any other file<\/div>\n<\/li>\n<\/ul>\n<p><strong>IAM chains<\/strong><\/p>\n<p>If we continued to grow the file and fill up the table then eventually we&#8217;d need another IAM page to map the next GAM interval. This is where an IAM chain comes in. It&#8217;s a linked-list of IAM pages that track the space allocated to a single entity. The linked-list is not sorted at all &#8211; IAM pages are appended to it in the order that they&#8217;re needed. The IAM pages within the list are numbered, again, in the order that they were appended to the list.<\/p>\n<p>Definition of &#8216;entity&#8217; &#8211; what uses an IAM chain? This is vastly different in SQL Server 2000 and 2005.<\/p>\n<p>In SQL Server 2000, a single IAM chain is used for each:<\/p>\n<ul>\n<li>Heap or clustered index\n<ul>\n<li>A\u00a0table can only have one or the other, not both. These have index IDs of 0 and 1 respectively.<\/li>\n<\/ul>\n<\/li>\n<li>Non-clustered index\n<ul>\n<li>These have index IDs from 2 to 250 (i.e. you can only have 249 of them)<\/li>\n<\/ul>\n<\/li>\n<li>Table&#8217;s complete LOB storage\n<ul>\n<li>For LOB columns (<span style=\"font-family: 'Courier New';\">text<\/span>, <span style=\"font-family: 'Courier New';\">ntext<\/span>, <span style=\"font-family: 'Courier New';\">image<\/span>) in the heap or clustered index. This is sometimes called the &#8216;text index&#8217; and has a fixed index ID of 255.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>This gives a maximum of 251 IAM chains per object in SQL Server 2000 and before. I usually generalize and say that in SQL Server 2000, there&#8217;s one IAM chain per index (which fits nicely if you remember that IAM stands for <em>Index<\/em> Allocation Map).<\/p>\n<p><strong>Allocation units (SQL Server 2005 and later)<\/strong><\/p>\n<p>Now from SQL Server 2005 onward, things have changed a lot. IAM chains and IAM pages are exactly the same, but what they correspond to is different. A table can now have up to <strong><strong>45 million<\/strong><\/strong> IAM chains! <span style=\"font-size: 10pt;\">There are now three things that IAM chains map space allocations for:<\/span><\/p>\n<ol>\n<li><span style=\"font-size: 10pt;\">heaps and b-trees (a b-tree is the internal structure used to store an index)<\/span><\/li>\n<li><span style=\"font-size: 10pt;\"><span><span style=\"font-family: Verdana; font-size: small;\"><span style=\"font: 7pt 'Times New Roman';\">\u00a0<\/span><\/span><\/span><\/span><span style=\"font-size: 10pt;\">LOB data<\/span><\/li>\n<li><span style=\"font-size: 10pt;\">row-overflow data<\/span><\/li>\n<\/ol>\n<p><span style=\"font-size: 10pt;\">and we now call these units of space allocation tracking <\/span><em><em>allocation units<\/em><\/em><span style=\"font-size: 10pt;\">. The internal names for these three types of allocation unit are (respectively):<\/span><\/p>\n<ol>\n<li>\n<div><span style=\"font-size: 10pt;\">hobt allocation unit (<span style=\"text-decoration: underline;\">H<\/span>eap <span style=\"text-decoration: underline;\">O<\/span>r <span style=\"text-decoration: underline;\">B<\/span>&#8211;<span style=\"text-decoration: underline;\">T<\/span>ree, pronounced &#8216;hobbit&#8217;, yes, as in Lord Of The Rings)<\/span><\/div>\n<\/li>\n<li>\n<div><span style=\"font-size: 10pt;\">LOB allocation unit<\/span><\/div>\n<\/li>\n<li>\n<div><span style=\"font-size: 10pt;\">SLOB allocation unit (<span style=\"text-decoration: underline;\">S<\/span>mall-<span style=\"text-decoration: underline;\">LOB<\/span> or <span style=\"text-decoration: underline;\">S<\/span>hort-<span style=\"text-decoration: underline;\">LOB<\/span>)<\/span><\/div>\n<\/li>\n<\/ol>\n<p><span style=\"font-size: 10pt;\">and the external names are, respectively:<\/span><\/p>\n<ol>\n<li>\n<div><span style=\"font-size: 10pt;\">IN_ROW_DATA allocation unit<\/span><\/div>\n<\/li>\n<li>\n<div><span style=\"font-size: 10pt;\">LOB_DATA allocation unit<\/span><\/div>\n<\/li>\n<li>\n<div><span style=\"font-size: 10pt;\">ROW_OVERFLOW_DATA allocation unit<\/span><\/div>\n<\/li>\n<\/ol>\n<p><span style=\"font-size: 10pt;\">They couldn&#8217;t really continue to be called IAM chains, because they&#8217;re no longer tracking space allocation for an index. However, they&#8217;re chain of IAM pages is still called an IAM chain, and the unit of tracking is now called an allocation unit. Apart from that, there&#8217;s no difference.<\/span><\/p>\n<p><span style=\"font-size: 10pt;\">Let&#8217;s have a quick look at three new features in SQL Server 2005 that made\u00a0these changes necessary and boosted the number of potential IAM chains per table.<\/span><\/p>\n<p><strong><strong><span style=\"font-size: 10pt;\">Included Columns<\/span><\/strong><\/strong><strong><span style=\"font-size: 10pt; font-weight: bold;\"><br \/>\n<\/span><\/strong><span style=\"font-size: 10pt;\">This is the ability for non-clustered indexes to include non-key columns at the leaf-level. This is useful for three reasons:<\/span><\/p>\n<ol>\n<li>\n<div><span style=\"font-size: 10pt;\">Iit allows a non-clustered index to truly cover a query where the query results include more than 16 columns or the combination of column lengths in the query results is greater than 900 bytes (remember that a non-clustered index key is limited to 16 columns and 900 bytes).<\/span><\/div>\n<\/li>\n<li>\n<div><span style=\"font-size: 10pt;\">It <\/span><span style=\"font-size: 10pt;\">allows columns to be include in the non-clustered index that have data types that cannot be part of an index key (e.g.\u00a0<\/span><span style=\"font-family: Verdana;\"><span><span style=\"font-family: 'Courier New'; font-size: 10pt;\">varchar(max)<\/span><\/span><span style=\"font-size: 10pt;\"> or <\/span><span><span style=\"font-family: 'Courier New'; font-size: 10pt;\">XML<\/span><\/span><span style=\"font-size: 10pt;\">).<\/span><\/span><\/div>\n<\/li>\n<li>\n<div><span style=\"font-size: 10pt;\">It allows a non-clustered index to cover a query without having to have all the columns included in the index key. As the index key is included in rows at all levels of the b-tree, this allows the index to be smaller.<\/span><\/div>\n<\/li>\n<\/ol>\n<p><span style=\"font-size: 10pt;\">An example of space saving: imagine a 100 million row index, with a key length of 900 bytes, but only the first two integer keys are really needed as the index key, the other 4 fixed-length columns could be stored in the index as included columns. <\/span><span style=\"font-size: 10pt;\">With the 900 byte key, 8 rows can fit per database page (i.e. the <\/span><em><em>fanout<\/em><\/em><span style=\"font-size: 10pt;\"> is 8). This means there will be 12500000 pages at the leaf level, 1562500 pages at the next level up in the b-tree and so on, giving a total of 12500000 + 1562500 + 195313 + 24415 + 3052 + 382 + 48 + 6 + 1 = 14285717 pages (including 1785717 to store the upper levels of the b-tree).<\/span><\/p>\n<p><span style=\"font-size: 10pt;\">If we go with the included columns method then the key size shrinks to 8 bytes, and with the row overhead we can get the row length in the upper levels of the b-tree down to 15 bytes (giving a fanout of approx. 537). Note that the fanout at the leaf-level is still going to be 8,\u00a0 because the amount of data stored in each row at the leaf-level is the same. So, this means there will be 12500000 pages at the leaf level, 23278 pages at the next level up and so on, giving a total of 12500000 + 23278 + 44 + 1 = 12523323 pages (including 23323 to store the upper levels of the b-tree). Compared to the full-size 900-byte key, this is a 12% saving of 1762394 pages, or 13.6GB!\u00a0Obviously this is a contrived case but you can see how the savings can occur.<\/span><\/p>\n<p><span style=\"font-size: 10pt;\">The main reason for adding this feature it to enable true covering queries. A covering query is one where the query optimizer knows it can get all the query results from the non-clustered index and so the query can be satisfied without having to incur the extra IOs of looking up data in the base table &#8211; a significant performance saving.<\/span><\/p>\n<p><span style=\"font-size: 10pt;\">Now that non-clustered indexes can have included columns, and those columns can be LOB data types (but only the new ones in SQL Server 2005 &#8211; <span style=\"font-family: 'Courier New';\">varchar(max)<\/span>, <span style=\"font-family: 'Courier New';\">nvarchar(max)<\/span>, <span style=\"font-family: 'Courier New';\">varbinary(max)<\/span>, and <span style=\"font-family: 'Courier New';\">XML<\/span>). This means that having a single LOB allocation unit (as in the case of the single text index in SQL Server 2000) isn&#8217;t possible any more because each index may have its own set of LOBs. Now, you may ask why there isn&#8217;t just a single set of LOBs with multiple references from various indexes plus the base table. We considered that during SQL Server 2005 development but it would have made things a lot more complicated.<\/span><\/p>\n<p><span style=\"font-size: 10pt;\">So, with this new feature, each index needs two allocation units &#8211; one for the data or index rows (the hobt allocation unit) and one for any LOB data.<\/span><\/p>\n<p><strong><strong><span style=\"font-size: 10pt;\">Large Rows<\/span><\/strong><\/strong><\/p>\n<p><strong><strong><\/strong><\/strong><strong><strong><\/strong><\/strong><span style=\"font-size: 10pt;\">One of the things that has plagued schema designers for a long time is the 8060 byte limit on table row sizes so this restriction was removed in SQL Server 2005. The way this is done is to allow variable-length columns (e.g. <\/span><span style=\"font-family: Verdana;\"><span><span style=\"font-family: 'Courier New'; font-size: 10pt;\">varchar<\/span><\/span><span style=\"font-size: 10pt;\">, <\/span><span><span style=\"font-family: 'Courier New'; font-size: 10pt;\">sqlvariant<\/span><\/span><span style=\"font-size: 10pt;\">) to get pushed off-row when the row size gets too big to fit on a single page.<\/span><\/span><\/p>\n<p><span style=\"font-size: 10pt;\">But where do these column values get pushed to? They&#8217;re effectively turned into mini LOB columns. The column value in the row is replaced with a 24-byte (and possibly 36, 48, 60, or 72-byte) pointer to the off-row column value, which is stored as if its a LOB value in a seperate allocation unit &#8211;\u00a0the row-overflow (or SLOB) allocation unit. These values are stored in text pages in exactly the same way as regular LOB values are, just\u00a0using a separate allocation unit.\u00a0The SLOB allocation unit is only created when the first column value is pushed off-row.<\/span><\/p>\n<p><span style=\"font-size: 10pt;\">This feature works for non-clustered indexes too &#8211; if you consider the ability to have included columns in non-clustered indexes then you could easily have non-clustered index rows that won&#8217;t fit on a page. It would have been short-sighted of to get rid of the 900-byte limit and replace it with an 8060-byte limit by not extending the row-overflow feature to non-clustered indexes too.<\/span><\/p>\n<p><span style=\"font-size: 10pt;\">Now with the addition of this new feature, each index can have up to three allocation units &#8211; hobt, LOB, and SLOB. Even with this, that only makes a maximum of 750 IAM chains per table for SQL Server 2005 (remember an IAM chain now maps the storage allocations for an allocation unit, so 250 indexes * 3 allocation units = 750 IAM chains) and 3000 IAM chains per table for SQL Server 2008 onward (where the number of indexes per table can be 1000). But I mentioned 45 million<\/span><span style=\"font-size: 10pt;\">\u00a0IAM chains per table earlier &#8211; where do all the rest come from?<\/span><\/p>\n<p><strong><strong><span style=\"font-size: 10pt;\">Partitioning<\/span><\/strong><\/strong><\/p>\n<p><strong><strong><\/strong><\/strong><strong><strong><\/strong><\/strong><span style=\"font-size: 10pt;\">Partitioning gives us the 15,000x multiplier. Partitioning \u00a0allows tables and indexes to be split into a series of ranges, with each range stored separately (most commonly in seperate filegroups). Partitioning is a topic for a separate post.<\/span><\/p>\n<p><span style=\"font-size: 10pt;\">If each range or <\/span><em><em>partition<\/em><\/em><span style=\"font-size: 10pt;\"> of the table or index is stored seperately, then each is going to need its own hobt allocation unit. Of course, the LOB values associated with each partition need to be stored with it, and so each partition also needs a LOB allocation unit. Also, the row-overflow feature is per-row, and so rows in each partition will overflow into SLOB allocation units just as for un-partitioned tables and indexes. Thus each partition of a table or index can have up to three allocation units (and hence three IAM chains).<\/span><\/p>\n<p><span style=\"font-size: 10pt;\">Still, where does that 15,000 come in? Each table or index can have up to 15,000 partitions from SQL Server 2008 SP2 onward (1,000 partitions before that). This gives us 1000 indexes x 15,000 partitions x 3 allocation units = 45 millionIAM chains. Realistically this probably won&#8217;t happen, but it is possible.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>This is a combo from some previously posted material, with some more DBCC PAGE output thrown in. IAM pages An IAM (Index Allocation Map) page tracks approximately 4GB worth of space in a single file, aligned on a 4GB boundary. These 4GB chunks are called &#8216;GAM intervals&#8217;. An IAM page tracks which extents within that [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[48,62,65],"tags":[],"class_list":["post-1167","post","type-post","status-publish","format-standard","hentry","category-inside-the-storage-engine","category-on-disk-structures","category-partitioning"],"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: IAM pages, IAM chains, and allocation units - 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-iam-pages-iam-chains-and-allocation-units\/\" \/>\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: IAM pages, IAM chains, and allocation units - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"This is a combo from some previously posted material, with some more DBCC PAGE output thrown in. IAM pages An IAM (Index Allocation Map) page tracks approximately 4GB worth of space in a single file, aligned on a 4GB boundary. These 4GB chunks are called &#8216;GAM intervals&#8217;. An IAM page tracks which extents within that [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-iam-pages-iam-chains-and-allocation-units\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2007-10-04T00:42:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-11-05T21:49:40+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/content\/binary\/seqa3.jpg\" \/>\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=\"12 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-iam-pages-iam-chains-and-allocation-units\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-iam-pages-iam-chains-and-allocation-units\/\",\"name\":\"Inside the Storage Engine: IAM pages, IAM chains, and allocation units - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-iam-pages-iam-chains-and-allocation-units\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-iam-pages-iam-chains-and-allocation-units\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/content\/binary\/seqa3.jpg\",\"datePublished\":\"2007-10-04T00:42:00+00:00\",\"dateModified\":\"2013-11-05T21:49:40+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-iam-pages-iam-chains-and-allocation-units\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-iam-pages-iam-chains-and-allocation-units\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-iam-pages-iam-chains-and-allocation-units\/#primaryimage\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/content\/binary\/seqa3.jpg\",\"contentUrl\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/content\/binary\/seqa3.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-iam-pages-iam-chains-and-allocation-units\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Inside the Storage Engine: IAM pages, IAM chains, and allocation units\"}]},{\"@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: IAM pages, IAM chains, and allocation units - 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-iam-pages-iam-chains-and-allocation-units\/","og_locale":"en_US","og_type":"article","og_title":"Inside the Storage Engine: IAM pages, IAM chains, and allocation units - Paul S. Randal","og_description":"This is a combo from some previously posted material, with some more DBCC PAGE output thrown in. IAM pages An IAM (Index Allocation Map) page tracks approximately 4GB worth of space in a single file, aligned on a 4GB boundary. These 4GB chunks are called &#8216;GAM intervals&#8217;. An IAM page tracks which extents within that [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-iam-pages-iam-chains-and-allocation-units\/","og_site_name":"Paul S. Randal","article_published_time":"2007-10-04T00:42:00+00:00","article_modified_time":"2013-11-05T21:49:40+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/content\/binary\/seqa3.jpg","type":"","width":"","height":""}],"author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"12 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-iam-pages-iam-chains-and-allocation-units\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-iam-pages-iam-chains-and-allocation-units\/","name":"Inside the Storage Engine: IAM pages, IAM chains, and allocation units - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-iam-pages-iam-chains-and-allocation-units\/#primaryimage"},"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-iam-pages-iam-chains-and-allocation-units\/#primaryimage"},"thumbnailUrl":"https:\/\/www.sqlskills.com\/blogs\/paul\/content\/binary\/seqa3.jpg","datePublished":"2007-10-04T00:42:00+00:00","dateModified":"2013-11-05T21:49:40+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-iam-pages-iam-chains-and-allocation-units\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-iam-pages-iam-chains-and-allocation-units\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-iam-pages-iam-chains-and-allocation-units\/#primaryimage","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/content\/binary\/seqa3.jpg","contentUrl":"https:\/\/www.sqlskills.com\/blogs\/paul\/content\/binary\/seqa3.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-iam-pages-iam-chains-and-allocation-units\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Inside the Storage Engine: IAM pages, IAM chains, and allocation units"}]},{"@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\/1167","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=1167"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1167\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=1167"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=1167"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=1167"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}