Are mixed pages removed by an index rebuild?

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 mixed pages from mixed extents, unless trace flag 1118 is enabled.

See the following blog posts for more info:

Assuming that mixed pages are not disabled with trace flag 1118, does an index rebuild remove all mixed pages or not?

Let’s investigate. First I’ll create a clustered index with 1,000 data pages:

CREATE TABLE [MixedTest] ([c1] BIGINT IDENTITY, [c2] CHAR (8000) DEFAULT 'a');
CREATE CLUSTERED INDEX [MixedTest_CL] ON [MixedTest] ([c1]);
SET NOCOUNT ON;
GO
INSERT INTO [MixedTest] DEFAULT VALUES;
GO 1000

And then make sure that we have mixed pages be examining the first IAM page in the clustered index’s IAM chain. You can get the sp_AllocationMetadata proc here.

EXEC [sp_AllocationMetadata] N'MixedTest';
GO
Object Name   Index ID   Alloc Unit ID       Alloc Unit Type   First Page   Root Page   First IAM Page
------------  ---------  ------------------  ----------------  -----------  ----------  ---------------
MixedTest     1          72057594046185472   IN_ROW_DATA       (1:987)      (1:1732)    (1:988)
DBCC TRACEON (3604);
DBCC PAGE (N'master', 1, 988, 3);
GO

(I’m just including the relevant portion of the DBCC PAGE output here…)

<snip>
IAM: Single Page Allocations @0x00000000227EA08E

Slot 0 = (1:987)                    Slot 1 = (1:989)                    Slot 2 = (1:990)
Slot 3 = (1:991)                    Slot 4 = (1:1816)                   Slot 5 = (1:1817)
Slot 6 = (1:1818)                   Slot 7 = (1:1819)
<snip>

Now I’ll do an offline index rebuild of the clustered index, and look again at the IAM page contents (assume I’m running the proc and DBCC PAGE after the rebuild):

ALTER INDEX [MixedTest_CL] ON [MixedTest] REBUILD;
GO
<snip>
IAM: Single Page Allocations @0x0000000023B0A08E

Slot 0 = (1:1820)                   Slot 1 = (1:446)                    Slot 2 = (1:1032)
Slot 3 = (0:0)                      Slot 4 = (1:1035)                   Slot 5 = (1:1034)
Slot 6 = (1:1037)                   Slot 7 = (1:1036)
<snip>

So the answer is no, an index rebuild does not remove mixed page allocations. Only trace flag 1118 does that.

But this is interesting – 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 index, and then these are stitched together. The ‘stitching together’ 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.

Let’s try an offline index rebuild that forces a serial plan.

ALTER INDEX [MixedTest_CL] ON [MixedTest] REBUILD WITH (MAXDOP = 1);
GO
<snip>
IAM: Single Page Allocations @0x0000000023B0A08E

Slot 0 = (1:1822)                   Slot 1 = (1:1823)                   Slot 2 = (1:291)
Slot 3 = (1:292)                    Slot 4 = (0:0)                      Slot 5 = (0:0)
Slot 6 = (0:0)                      Slot 7 = (0:0)
<snip>

In this case there is only one index (i.e. no parallel mini indexes) being built so there are no pages being deallocated in the new index as there is no stitching operation. But why aren’t there 8 mixed pages? This is because during the build phase of the new index, 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 DBCC PAGE).

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.

Enjoy!

12 thoughts on “Are mixed pages removed by an index rebuild?

    1. No – it’s not easier, you need to interpret the output of DBCC EXTENTINFO and figure out which ones are the mixed pages vs. just one page allocated from a dedicated extent, as it lists the extents in allocation order, not by extent type. Looking at the single-page slot array in the first IAM page is the absolute easiest and non-ambiguous way.

  1. Hi Paul,

    i think bulk-allocated dedicated extents means 1 full extent(s) allocated to 1 single object but in Maxdop=1 case these are single pages==means mixed pages.

    1. No they’re not. A bulk-allocated extent is always a dedicated extent no matter what. MAXDOP does not change the allocation code used as it is DOP-agnostic.

  2. Would be more cheerful if you also explained below :)

    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.

    Regards,

  3. This raised a couple of questions in my mind. You may have answered one of them which is how to move existing objects from mixed extents to dedicated extents. If the TF is enabled will an index rebuild then move all pages to dedicated extents? If not is there a feasible way to accomplish that goal?
    The second question is, is it worth worrying about for existing tables/indexes. I have several large (>1G rows) and high activity tables (30M inserts/deletes per day).

    1. 1) Yes it will
      2) No, not worth it. The only time mixed pages make a perf difference is in tempdb with the specific workload type involving tiny temp tables and lots of concurrent connections.

  4. Hi Paul,

    I was wondering if my kind of issue has already been replied by you some where in Blog.. Still unable to find answer.. Could you please advise on below..

    1 of my Prod DB Instance (2008 R2) is facing Large Index Fragmentation and more than 1 tables have fragment_count > 1000. Index Rebuilt is scheduled for daily BUT still avg_fragmentation_in_percent is reaching 98%-100% for many indexes..

    How to find the reason for this.. Is it due to Page Split which can be resolved using Fill Factor of 90 OR it is due to Uniform/Mixed Page existence..

    Please advise and it may reach to P2 for me very soon..

      1. Hi Paul,

        Thanks a lot for your prompt advise and apologies for Late Confirmation as I was in follow up with Client on the same. It is due to Page Split only as confirmed by you.

        I set up Perfmon and it was confirmed with Ratio of Page Splits/Sec & Batch Requests/Sec being much more than 20%.

        I just wanted to Say.. You are really Famous and admired in our Group for your Easily Understandable and In-Depth Blogs on SQL Server Administration..

        Keep Going with the Great Work Sir..

  5. build phase of the new index, the leaf-level pages are taken from bulk-allocated dedicated extents regardless of the recovery model in use.

    Please sir , in which scenarios these bulk allocated dedicate d extents are allocated besides this scenario.

    SQL community is very much obliged to you for your community services. I don’t have word s to express my gratitude.Thank you

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.