How It Works: Online Index Rebuild - Can Cause Increased Fragmentation

SQL Server Books Online alludes to the fragmentation possibility but does not fully explain that the Online Index rebuilding may increase fragmentation when it is allowed to run with MAX DOP > 1 and ALLOW_PAGE_LOCKS = OFF directives .

The process of building an online index involves maintaining the active connection activity with that of the online building operation(s).   This is done by updating data modification plans to maintain both indexes during the online index building.

There are a few objectives an index rebuild can accomplish:

• Generate new statistics
• Change the number of pages used by the index (fill factor)
• Reorganize the data on database pages in relative proximity to each other

ALTER INDEX MAXDOP Option

The MAXDOP option caps the number of workers which can participate in the alter action.  For example the following tells SQL Server to use no more than 8 workers during the alter index operation.  You can't force the maxdop it is only a suggested cap directive.

Alter Index all On test2 Rebuild With (Online = On, maxdop = 8)

MAXDOP = 1 (Serial)

The following FIGURE depicts a serialized alter index operation.   The new rowset maintains an allocation cache that is used when allocating the new pages to move data onto.   When the cache is empty 1 or more extents are allocated, as close to the last extent allocated as possible. 

This process allows the data to be packed onto pages near each other.  Reducing the number of pages, if the fill factor so indicates, and placing the rows in sorted order near one another.

MAXDOP > 1 (Parallel) using ALLOW_PAGE_LOCKS = OFF

When running in parallel a decision is made as to how the allocation cache will be utilized.  In the case of ALLOW_PAGE_LOCKS = OFF the logic is to share a single allocation cache. 

Take special note: The logic can use statistical operations to divide the workload among the workers.

This can lead to a leap frog style of allocation and increase fragmentation.   The pages of the index may be very contiguous allocations … 100, 101, 102, 103, … but the data on the pages is 100 (from 1st partition), 101 (from 2nd partition), 102 (1st partition) so when scanning the IAM in page order the page fragmentation level can climb.

Actions such as the fill factor adjustments and statistics gathering process as expected.

MAXDOP > 1 (Parallel) using ALLOW_PAGE_LOCKS = ON (Default is ON for ALTER INDEX COMMAND)

When ALTER INDEX is able to use page or table (rowset level) locking the allocation patterns are optimized for bulk operations.  Without attempting to write a novel about how this works I have drawn a very high level picture in the figure shown below.

When bulk operations are enabled, an additional caching layer is instituted for each of the workers to use.   The Bulk Allocation Cache is sized based on the work load expected for the given partition, etc...   This allows each partition to allocate 1 or more extents at a time and then use those pages to store the data they are processing.   This provides a critical level of separation necessary to reduce the leap frogging effect and reduces fragmentation by at least a factor of 8 pages per extent.

Note: The fragmentation level will not be reduced as much as a MAXDOP=1 alteration, but it can reduce the fragmentation within percentage points of MAXDOP=1 in many instances.

Recap

• MAXDOP is a key factor for determining the amount of work each worker is targeted to perform.
• The type of allocation caching used determines the possible fragmentation impact
• None of these options controls the fill factor maintenance
• None of these options controls the statistics gathering

Bob Dorr - Principal SQL Server Escalation Engineer

Comments

• Anonymous
  September 05, 2012
  This connect item should solve the problem 100% in all situations: connect.microsoft.com/.../per-table-allocation-delta

• Anonymous
  September 10, 2012
  Hi, I am definately missing something. Just before your note you say "This provides a critical level of separation necessary to reduce the leap frogging effect and reduces fragmentation by at least a factor of 8 pages per extent." But there are only 8 pages in an extent so i was wondering what i am missing?

• Anonymous
  September 11, 2012
  Andrew, when I stated it provides the separation I am pointing to the fact that each partition gets at least 8 or more pages (its own extent(s)) to add data to instead of sharing all allocations.   This means the difference is at least 8x because you only switch from one sorted stream to the next every extent instead of every page.

• Anonymous
  September 13, 2012
  Thanks for sharing this information, it is really relevant. Keep the same. Also see :- <b><a href=" www.parttime-jobs-online.com/" target=_new> part time jobs </a></b>

• Anonymous
  October 18, 2012
  Does this also apply to creating indexes from scratch with MAXDOP > 1 and ALLOW_PAGE_LOCKS = OFF, or does it just apply to REBUILD operations?

• Anonymous
  November 25, 2012
  Hello, i would like to ask that what is the benefits of sql training, what all topics should be covered and it is kinda bothering me … and has anyone studies from this course www.wiziq.com/.../125-comprehensive-introduction-to-sql of SQL tutorial online?? or tell me any other guidance... would really appreciate help… and Also i would like to thank for all the information you are providing on sql training.

• Anonymous
  March 26, 2013
  Great article, helped me solve an issue with a long running query!

• Anonymous
  July 04, 2013
  how to do indexing of heavy data around 15 GB. Data is on Sqlserver 2008 r2

• Anonymous
  September 19, 2016
  Hi guy's my name is Akshita Chopra. I am very beautiful and cute girl in Delhi. I am a independent model.http://akshitachopra.com/

• Anonymous
  September 19, 2016
  Hi my name is priyanka singhania. I am an independent girl in Delhi. I like to offer you good services.http://priyankasinghania.com/

• Anonymous
  September 21, 2016
  I like this post. I have got such a good information from here. I am going to bookmark this blog this is very informative. Thankshttp://aaditisharma.com/

• Anonymous
  October 24, 2016
  While this helpfully describes how the product works it's still bad! The product should be fixed to allocate in more efficient patterns.

• Anonymous
  October 26, 2016
  The Blog Design does not allow printing :-(

• Anonymous
  December 02, 2016
  MAXDOP > 1 (Parallel) using ALLOW_PAGE_LOCKS = OFFDoes it create 2 partition temporary or is it done when there is a partition in the table or does it use default i m confused here.

• Anonymous
  December 02, 2016
  MAXDOP > 1 (Parallel) using ALLOW_PAGE_LOCKS = OFFDoes it create 2 partition temporary or is it done when there is a partition in the table or does it use default i m confused here. please explain.