IAM pages, IAM chains, and allocation units<\/a><\/li>\n<\/ul>\nThe final pieces in the allocation puzzle are the other allocation-tracking map pages – GAM, SGAM, PFS, ML map, and DIFF map pages. All of the following explanation holds for SQL Server 2000 and all subsequent releases so far. For any of these pages you can do a dump-style 3 DBCC PAGE dump and it will interpret the page and give you a human readable form of the allocation tracking data.<\/p>\n
GAM pages<\/strong><\/p>\nGAM stands for G<\/strong><\/strong>lobal A<\/strong><\/strong>llocation M<\/strong><\/strong>ap. If you remember from before, database data files are split up into GAM intervals<\/em><\/em> (don’t get confused – they’re not split physically, just conceptually). A GAM interval is equivalent to the amount of space that the bitmaps in GAM, SGAM, ML map, DIFF map, and IAM pages track – 64000 extents or almost 4GB. These bitmaps are the same size in each of these five page types and have one bit per extent, but they mean different things in each of the different allocation pages.<\/p>\nThe bits in the GAM bitmap have the following semantics:<\/p>\n
\n- bit = 1<\/strong><\/strong>: the extent is available for allocation (you could think of it as currently allocated to the GAM page)<\/li>\n
- bit = 0<\/strong><\/strong>: the extent is already allocated for use<\/li>\n<\/ul>\n
These semantics are the same for mixed and dedicated\/uniform extents.<\/p>\n
One thing to note, at the start of every GAM interval is a GAM extent<\/em><\/em> which contains the global allocation pages that track that GAM interval. This GAM extent cannot be used for any regular page allocations. The first GAM extent starts at page 0 in the file and has the following layout:<\/p>\n\n- Page 0: the file header page (another post!)<\/li>\n
- Page 1: the first PFS page<\/li>\n
- Page 2: the first GAM page<\/li>\n
- Page 3: the first SGAM page<\/li>\n
- Page 4: Unused in 2005+<\/li>\n
- Page 5: Unused in 2005+<\/li>\n
- Page 6: the first DIFF map page<\/li>\n
- Page 7: the first ML map page<\/li>\n<\/ul>\n
SGAM pages<\/strong><\/p>\nI remember last year having an email discussion about what the ‘S’ stands for in SGAM. Various names have been used over the years inside and outside Microsoft but the official name that Books Online uses is S<\/strong><\/strong>hared G<\/strong><\/strong>lobal A<\/strong><\/strong>llocation M<\/strong><\/strong>ap. To be honest, we always just call them ‘es-gams’<\/em><\/em> and never spell it out.<\/p>\nAs I said above, the SGAM bitmap is exactly the same as the GAM bitmap in structure and the interval it covers, but the semantics of the bits are different:<\/p>\n
\n- bit = 1<\/strong><\/strong>: the extent is a mixed extent and may have at least one unallocated page available for use\u00a0(it’s an optimistic update algorithm)<\/li>\n
- bit = 0<\/strong><\/strong>: the extent is either dedicated or is a mixed extent with no unallocated pages (essentially the same situation given that the SGAM is used to find mixed extents with unallocated pages)<\/li>\n<\/ul>\n
Combining GAM, SGAM, and IAM pages<\/strong><\/p>\nSo, taking the GAM, SGAM and IAM pages together (remember that in the IAM bitmap, the bit is set if the extent is allocated to the IAM chain\/allocation unit), the various combinations of bits are:<\/p>\n
\n\n\nGAM<\/span><\/strong><\/td>\nSGAM<\/span><\/strong><\/td>\nAny IAM<\/span><\/strong><\/td>\nComments<\/span><\/strong><\/td>\n<\/tr>\n\n| 0<\/span><\/td>\n | 0<\/span><\/td>\n | 0<\/span><\/td>\n | Mixed extent with all pages allocated<\/span><\/td>\n<\/tr>\n\n| 0<\/span><\/td>\n | 0<\/span><\/td>\n | 1<\/span><\/td>\n | Dedicated extent (must be allocated to only a single IAM page)<\/span><\/td>\n<\/tr>\n\n| 0<\/span><\/td>\n | 1<\/span><\/td>\n | 0<\/span><\/td>\n | Mixed extent with >= 1 unallocated page<\/span><\/td>\n<\/tr>\n\n| 0<\/span><\/td>\n | 1<\/span><\/td>\n | 1<\/span><\/td>\n | Invalid state<\/span><\/td>\n<\/tr>\n\n| 1<\/span><\/td>\n | 0<\/span><\/td>\n | 0<\/span><\/td>\n | Unallocated extent<\/span><\/td>\n<\/tr>\n\n| 1<\/span><\/td>\n | 0<\/span><\/td>\n | 1<\/span><\/td>\n | Invalid state<\/span><\/td>\n<\/tr>\n\n| 1<\/span><\/td>\n | 1<\/span><\/td>\n | 0<\/span><\/td>\n | Invalid state<\/span><\/td>\n<\/tr>\n\n| 1<\/span><\/td>\n | 1<\/span><\/td>\n | 1<\/span><\/td>\n | Invalid state<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n You can see that only 4 of the 8 possible bit combinations for any particular extent are valid. Anything else constitutes a corruption of some sort and can lead to all kinds of horrible situations.<\/p>\n ML map pages<\/strong><\/p>\nML stands for M<\/strong>inimally L<\/strong>ogged. These pages track which extents have been modified by minimally-logged operations since the last transaction log backup when\u00a0using the BULK_LOGGED recovery model. The idea is that the next transaction log backup will backup the log as usual, and then also include all the extents marked as changed in these bitmaps. The combination of these extents, plus the transaction log in the backup gives the differences that have occured in the database since the previous transaction log backup. The ML page bitmaps are cleared once they’ve been read. If you don’t ever use the BULK_LOGGED recovery model, these pages are never used.<\/p>\nThe ML page bitmap is exactly the same as the GAM bitmap in structure and the interval it covers, but the semantics of the bits are different:<\/p>\n \n- bit = 1<\/strong><\/strong>: the extent has been changed by a minimally logged operation since the last transaction log backup<\/li>\n
- bit = 0<\/strong><\/strong>: the extent was not changed<\/li>\n<\/ul>\n
DIFF map pages<\/strong><\/p>\nDIFF stands for diff<\/strong>erential. These pages track which extents have been modified since the last full backup was taken. It is a common misconception that the bitmaps track the changes since the last differential backup. The idea is that a differential backup will contain all the extents that have changed since the last full backup. Restore time can be cut down significantly by using differential backups to avoid having to restore all the log backups in the period between the full and last differential backup – more on this in a later post. The bitmaps are not cleared until the next full backup. Note that I don’t say full database<\/em> backup in the explanation above. The full and differential backups can be database, filegroup, or file level backups.<\/p>\nThe DIFF page bitmap is exactly the same as the GAM bitmap in structure and the interval it covers, but the semantics of the bits are different:<\/p>\n \n- bit = 1<\/strong><\/strong>: the extent has been changed since the last full backup<\/li>\n
- bit = 0<\/strong><\/strong>: the extent was not changed<\/li>\n<\/ul>\n
PFS pages<\/strong><\/p>\nPFS stands for P<\/strong><\/strong>age F<\/strong><\/strong>ree S<\/strong><\/strong>pace, but the PFS page tracks much more than that. As well as GAM intervals, every database file is also split (conceptually) into PFS intervals<\/em><\/em>. A PFS interval is 8088 pages, or about 64MB. A PFS page doesn’t have a bitmap – it has a byte<\/em><\/em>-map, with one byte for each page in the PFS interval (not including itself).<\/p>\nThe bits in each byte are encoded to mean the following:<\/span><\/span><\/p>\n\n- bits 0-2: how much free space is on the page\n
\n- 0x00 is empty<\/li>\n
- 0x01 is 1 to 50% full<\/li>\n
- 0x02 is 51 to 80% full<\/li>\n
- 0x03 is 81 to 95% full<\/li>\n
- 0x04 is 96 to 100% full<\/li>\n<\/ul>\n<\/li>\n
- bit 3 (0x08): is there one or more ghost records on the page?<\/li>\n
- bit\u00a04 (0x10): is the page an IAM page?<\/li>\n
- bit\u00a05 (0x20): is the page a mixed-page?<\/li>\n
- bit 6 (0x40): is the page allocated?<\/li>\n
- Bit 7 is unused<\/li>\n<\/ul>\n
For instance, an IAM page will have a PFS byte value of 0x70 (allocated + IAM page + mixed page).<\/p>\n Free space is only tracked for pages storing LOB values (i.e. text<\/span>\/image<\/span> in SQL Server 2000, plus (n)varchar(max)<\/span>, varbinary(max)<\/span>, XML<\/span>, and row-overflow data in SQL Server 2005) and heap data pages. This is because these are the only pages that store unordered data and so insertions can occur anywhere there’s space. For indexes, there’s an explicit ordering so there’s no choice in the insertion point.<\/p>\nThe point at which a PFS byte is reset is not intuitive. PFS bytes are not fully reset until the page is reallocated. On deallocation, the only bit in the PFS byte that’s changed is the allocation status bit – this makes it very easy to rollback a deallocation.<\/p>\n Here’s an example. Using a database with a simple table with one row. A DBCC PAGE of the IAM page includes:<\/p>\n PFS (1:1) = 0x70 IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL<\/span><\/p><\/blockquote>\nIf I drop the table in an explicit transaction and then do the DBCC PAGE again, the output no includes:<\/p>\n PFS (1:1) = 0x30 IAM_PG MIXED_EXT 0_PCT_FULL<\/span><\/p><\/blockquote>\nAnd if I rollback then transaction, the DBCC PAGE output reverts to:<\/p>\n PFS (1:1) = 0x70 IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL<\/span><\/p><\/blockquote>\nOk – four blog posts in one day is quite enough! :-)<\/p>\n <\/p>\n","protected":false},"excerpt":{"rendered":" Well this one is well overdue and I’m in the middle of writing a class where I want to reference this blog post – so I suppose I’d better write it!! This is an updated post from my old Storage Engine blog that now covers DIFF and ML map pages. In some previous posts […]<\/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],"tags":[],"class_list":["post-1068","post","type-post","status-publish","format-standard","hentry","category-inside-the-storage-engine","category-on-disk-structures"],"yoast_head":"\n Inside The Storage Engine: GAM, SGAM, PFS and other allocation maps - 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-gam-sgam-pfs-and-other-allocation-maps\/\" \/>\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: GAM, SGAM, PFS and other allocation maps - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\" Well this one is well overdue and I’m in the middle of writing a class where I want to reference this blog post – so I suppose I’d better write it!! This is an updated post from my old Storage Engine blog that now covers DIFF and ML map pages. In some previous posts […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-gam-sgam-pfs-and-other-allocation-maps\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2008-03-14T03:31:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-09-26T20:32:32+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=\"7 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-gam-sgam-pfs-and-other-allocation-maps\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-gam-sgam-pfs-and-other-allocation-maps\/\",\"name\":\"Inside The Storage Engine: GAM, SGAM, PFS and other allocation maps - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2008-03-14T03:31:00+00:00\",\"dateModified\":\"2020-09-26T20:32:32+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-gam-sgam-pfs-and-other-allocation-maps\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-gam-sgam-pfs-and-other-allocation-maps\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-gam-sgam-pfs-and-other-allocation-maps\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Inside The Storage Engine: GAM, SGAM, PFS and other allocation maps\"}]},{\"@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: GAM, SGAM, PFS and other allocation maps - 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-gam-sgam-pfs-and-other-allocation-maps\/","og_locale":"en_US","og_type":"article","og_title":"Inside The Storage Engine: GAM, SGAM, PFS and other allocation maps - Paul S. Randal","og_description":" Well this one is well overdue and I’m in the middle of writing a class where I want to reference this blog post – so I suppose I’d better write it!! This is an updated post from my old Storage Engine blog that now covers DIFF and ML map pages. In some previous posts […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-gam-sgam-pfs-and-other-allocation-maps\/","og_site_name":"Paul S. Randal","article_published_time":"2008-03-14T03:31:00+00:00","article_modified_time":"2020-09-26T20:32:32+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-gam-sgam-pfs-and-other-allocation-maps\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-gam-sgam-pfs-and-other-allocation-maps\/","name":"Inside The Storage Engine: GAM, SGAM, PFS and other allocation maps - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2008-03-14T03:31:00+00:00","dateModified":"2020-09-26T20:32:32+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-gam-sgam-pfs-and-other-allocation-maps\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-gam-sgam-pfs-and-other-allocation-maps\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-gam-sgam-pfs-and-other-allocation-maps\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Inside The Storage Engine: GAM, SGAM, PFS and other allocation maps"}]},{"@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\/1068","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=1068"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1068\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=1068"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=1068"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=1068"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
| | | | | | | | | | | |