{"id":4372,"date":"2014-06-10T10:15:55","date_gmt":"2014-06-10T17:15:55","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4372"},"modified":"2014-06-10T10:15:55","modified_gmt":"2014-06-10T17:15:55","slug":"mixed-pages-removed-index-rebuild","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/mixed-pages-removed-index-rebuild\/","title":{"rendered":"Are mixed pages removed by an index rebuild?"},"content":{"rendered":"<p>This is a question that came up this morning during our IE1 class that I thought would make an interesting blog post as there are some twists to the answer.<\/p>\n<p>The first 8 pages that are allocated to an allocation unit are\u00a0<em>mixed pages<\/em> from\u00a0<em>mixed extents<\/em>, unless trace flag 1118 is enabled.<\/p>\n<p>See the following blog posts for more info:<\/p>\n<ul>\n<li><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-iam-pages-iam-chains-and-allocation-units\/\" target=\"_blank\">Inside the Storage Engine: IAM pages, IAM chains, and allocation units<\/a><\/li>\n<li><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-anatomy-of-an-extent\/\" target=\"_blank\">Inside the Storage Engine: Anatomy of an extent<\/a><\/li>\n<li><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-tf-1118\/\" target=\"_blank\">Misconceptions around TF 1118<\/a><\/li>\n<\/ul>\n<p>Assuming that mixed pages are not disabled with trace flag 1118, does an index rebuild remove all mixed pages or not?<\/p>\n<p>Let&#8217;s investigate. First I&#8217;ll create a clustered index with 1,000 data pages:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nCREATE TABLE &#x5B;MixedTest] (&#x5B;c1] BIGINT IDENTITY, &#x5B;c2] CHAR (8000) DEFAULT 'a');\r\nCREATE CLUSTERED INDEX &#x5B;MixedTest_CL] ON &#x5B;MixedTest] (&#x5B;c1]);\r\nSET NOCOUNT ON;\r\nGO\r\nINSERT INTO &#x5B;MixedTest] DEFAULT VALUES;\r\nGO 1000\r\n<\/pre>\n<p>And then make sure that we have mixed pages be examining the first IAM page in the clustered index&#8217;s IAM chain. You can get the <em>sp_AllocationMetadata<\/em> proc <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-sp_allocationmetadata-putting-undocumented-system-catalog-views-to-work\/\" target=\"_blank\">here<\/a>.<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nEXEC &#x5B;sp_AllocationMetadata] N'MixedTest';\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\nMixedTest     1          72057594046185472   IN_ROW_DATA       (1:987)      (1:1732)    (1:988)\r\n<\/pre>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nDBCC TRACEON (3604);\r\nDBCC PAGE (N'master', 1, 988, 3);\r\nGO\r\n<\/pre>\n<p>(I&#8217;m just including the relevant portion of the <em>DBCC PAGE<\/em> output here&#8230;)<\/p>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\n&lt;snip&gt;\r\nIAM: Single Page Allocations @0x00000000227EA08E\r\n\r\nSlot 0 = (1:987)                    Slot 1 = (1:989)                    Slot 2 = (1:990)\r\nSlot 3 = (1:991)                    Slot 4 = (1:1816)                   Slot 5 = (1:1817)\r\nSlot 6 = (1:1818)                   Slot 7 = (1:1819)\r\n&lt;snip&gt;\r\n<\/pre>\n<p>Now I&#8217;ll do an offline index rebuild of the clustered index, and look again at the IAM page contents (assume I&#8217;m running the proc and <em>DBCC PAGE<\/em> after the rebuild):<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nALTER INDEX &#x5B;MixedTest_CL] ON &#x5B;MixedTest] REBUILD;\r\nGO\r\n<\/pre>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\n&lt;snip&gt;\r\nIAM: Single Page Allocations @0x0000000023B0A08E\r\n\r\nSlot 0 = (1:1820)                   Slot 1 = (1:446)                    Slot 2 = (1:1032)\r\nSlot 3 = (0:0)                      Slot 4 = (1:1035)                   Slot 5 = (1:1034)\r\nSlot 6 = (1:1037)                   Slot 7 = (1:1036)\r\n&lt;snip&gt;\r\n<\/pre>\n<p>So the answer is no, an index rebuild does not remove mixed page allocations. Only trace flag 1118 does that.<\/p>\n<p>But this is interesting &#8211; there are only 7 mixed pages in the singe-page slot array above. What happened? The answer is that the offline index rebuild ran in parallel, with each thread building a partial\u00a0index, and then these are stitched together. The &#8216;stitching together&#8217; operation will cause some of the non-leaf index pages to be deallocated as their contents are merged together. This explains the deallocated page that was originally tracked by entry 3 in the slot array.<\/p>\n<p>Let&#8217;s try an offline index rebuild that forces a serial plan.<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nALTER INDEX &#x5B;MixedTest_CL] ON &#x5B;MixedTest] REBUILD WITH (MAXDOP = 1);\r\nGO\r\n<\/pre>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\n&lt;snip&gt;\r\nIAM: Single Page Allocations @0x0000000023B0A08E\r\n\r\nSlot 0 = (1:1822)                   Slot 1 = (1:1823)                   Slot 2 = (1:291)\r\nSlot 3 = (1:292)                    Slot 4 = (0:0)                      Slot 5 = (0:0)\r\nSlot 6 = (0:0)                      Slot 7 = (0:0)\r\n&lt;snip&gt;\r\n<\/pre>\n<p>In this case there is only one index (i.e. no parallel mini indexes)\u00a0being built so there are no pages being deallocated in the new index as there is no stitching operation. But why aren&#8217;t there 8 mixed pages? This is because during the build phase of the new\u00a0index, the leaf-level pages are taken from bulk-allocated dedicated extents, regardless of the recovery model in use. The mixed pages are non-leaf index pages (which you can prove to yourself using <em>DBCC PAGE<\/em>).<\/p>\n<p>For parallel and single-threaded online index operations, the same two patterns occur as for offline index rebuilds, even though the algorithm is slightly different.<\/p>\n<p>Enjoy!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This is a question that came up this morning during our IE1 class that I thought would make an interesting blog post as there are some twists to the answer. The first 8 pages that are allocated to an allocation unit are\u00a0mixed pages from\u00a0mixed extents, unless trace flag 1118 is enabled. See the following blog [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[47,48,62],"tags":[],"class_list":["post-4372","post","type-post","status-publish","format-standard","hentry","category-indexes-from-every-angle","category-inside-the-storage-engine","category-on-disk-structures"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Are mixed pages removed by an index rebuild? - 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\/mixed-pages-removed-index-rebuild\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Are mixed pages removed by an index rebuild? - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"This is a question that came up this morning during our IE1 class that I thought would make an interesting blog post as there are some twists to the answer. The first 8 pages that are allocated to an allocation unit are\u00a0mixed pages from\u00a0mixed extents, unless trace flag 1118 is enabled. See the following blog [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/mixed-pages-removed-index-rebuild\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2014-06-10T17:15:55+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=\"3 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\/mixed-pages-removed-index-rebuild\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/mixed-pages-removed-index-rebuild\/\",\"name\":\"Are mixed pages removed by an index rebuild? - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2014-06-10T17:15:55+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/mixed-pages-removed-index-rebuild\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/mixed-pages-removed-index-rebuild\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/mixed-pages-removed-index-rebuild\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Are mixed pages removed by an index rebuild?\"}]},{\"@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":"Are mixed pages removed by an index rebuild? - 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\/mixed-pages-removed-index-rebuild\/","og_locale":"en_US","og_type":"article","og_title":"Are mixed pages removed by an index rebuild? - Paul S. Randal","og_description":"This is a question that came up this morning during our IE1 class that I thought would make an interesting blog post as there are some twists to the answer. The first 8 pages that are allocated to an allocation unit are\u00a0mixed pages from\u00a0mixed extents, unless trace flag 1118 is enabled. See the following blog [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/mixed-pages-removed-index-rebuild\/","og_site_name":"Paul S. Randal","article_published_time":"2014-06-10T17:15:55+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/mixed-pages-removed-index-rebuild\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/mixed-pages-removed-index-rebuild\/","name":"Are mixed pages removed by an index rebuild? - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2014-06-10T17:15:55+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/mixed-pages-removed-index-rebuild\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/mixed-pages-removed-index-rebuild\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/mixed-pages-removed-index-rebuild\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Are mixed pages removed by an index rebuild?"}]},{"@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\/4372","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=4372"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4372\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=4372"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=4372"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=4372"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}