CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

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

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

Rate This
  • Comments 8

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. 

 

image

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.

image

 

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.

image

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.

 

image

 

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

Leave a Comment
  • Please add 1 and 2 and type the answer here:
  • Post
  • This connect item should solve the problem 100% in all situations: connect.microsoft.com/.../per-table-allocation-delta

  • 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?

  • 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.

  • 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>

  • 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?

  • 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.

  • Great article, helped me solve an issue with a long running query!

  • how to do indexing of heavy data around 15 GB. Data is on Sqlserver 2008 r2

Page 1 of 1 (8 items)